How can we add a FOREIGN KEY constraint to the field of an existing MySQL table?
We can add a FOREIGN KEY constraint to a column of an existing MySQL table with the help of ALTER TABLE statement.
Syntax
ALTER TABLE table_name ADD FOREIGN KEY (colum_name) REFERENCES table having Primary Key(column_name);
Example
Suppose we want to add a FOREIGN KEY constraint on the table ‘Orders1’ referencing to the table ‘Customer’ which have column ‘Cust_Id’ as the Primary Key. It can be done with the help of the following query −
mysql> Alter table orders1 add FOREIGN KEY(Cust_id) REFERENCES Customer(Cust_id);
Query OK, 0 rows affected (0.21 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> Describe ORDERS1;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| order_id | int(11) | NO | PRI | NULL | |
| Product_name | varchar(25) | YES | | NULL | |
| orderdate | date | YES | | NULL | |
| Cust_id | int(11) | YES | MUL | NULL | |
+--------------+-------------+------+-----+---------+-------+
4 rows in set (0.05 sec)
Published on 18-Feb-2018 09:20:57
