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)

Ankitha Reddy

Published on 18-Feb-2018 04:50:57

• Related Questions & Answers
• How can we remove FOREIGN KEY constraint from a column of an existing MySQL table?
• How can we apply the PRIMARY KEY constraint to the field of an existing MySQL table?
• How can we apply UNIQUE constraint to the field of an existing MySQL table?
• How can we remove PRIMARY KEY constraint from a column of an existing MySQL table?
• How can we remove composite PRIMARY KEY constraint applied on multiple columns of an existing MySQL table?
• How can we add FOREIGN KEY constraints to more than one fields of a MySQL table?
• How can we apply a NOT NULL constraint to a column of an existing MySQL table?
• How can we assign FOREIGN KEY constraint on multiple columns?
• How can we remove NOT NULL constraint from a column of an existing MySQL table?
• How can we set PRIMARY KEY on multiple columns of an existing MySQL table?
• How can we add columns with default values to an existing MySQL table?
• What is MySQL UNIQUE constraint and how can we apply it to the field of a table?
• Which statement, other than ALTER TABLE statement, can be used to apply UNIQUE constraint to the field of an existing MySQL table?
• How can we add multiple columns, with single command, to an existing MySQL table?
• MySQL error 1452 - Cannot add or a child row: a foreign key constraint fails