SQL FOREIGN KEY

SQL FOREIGN KEYin one table points to the primary key of another table .

Table – users

+—-+——+
| uid | name |
+—-+——+
|  1 | AAAA |
|  2 | BBBB |
|  3 | CCCC |
|  4 | DDDD |
|  5 | EEEE |
+—-+——+

Table  – orders

+—–+—–+——–+
| oid | uid | items  |
+—–+—–+——–+
|   1 |   1 | Pen    |
|   2 |   2 | Watch  |
|   3 |   3 | shoe   |
|   4 |   4 | mobile |
+—–+—–+——–+

Here the field uid in the table orders points to the column uid in users .

That is  uid in the orders is the foreign key and that in users is primary key

FOREIGN KEY constraint prevent any action that may destroy the relation between the tables.

It also prevent invalid data and make sure that the data to be inserted should be one of the primary key value in another

Example

CREATE TABLE IF NOT EXISTS `orders` (
`oid` int(11) NOT NULL AUTO_INCREMENT,
`uid` int(11) NOT NULL,
`items` varchar(255) NOT NULL,
PRIMARY KEY (`oid`),
FOREIGN KEY (uid) REFERENCES users(uid)
)

Share