How can we add FOREIGN KEY constraint to a field of an existing MySQL table?

We can add FOREIGN KEY constraints to columns of existing MySQL tables with the help of ALTER TABLE statement.

grammar

ALTER TABLE table_name ADD FOREIGN KEY (colum_name) REFERENCES table with Primary Key(column_name);

Example

Suppose we want to add a foreign key constraint on the table "Orders1", referencing the table "Customer", the primary key of the table is the "Cust_Id" column. This 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)

The above is the detailed content of How can we add FOREIGN KEY constraint to a field of an existing MySQL table?. For more information, please follow other related articles on the PHP Chinese website!