This is a more complex example in which a product_order  table has foreign keys for two other tables. One foreign key references a two-column index in the product  table. The other references a single- column index in the customer  table: 

## Adding Foreign Key Constraints 

CREATE TABLE product ( category INT NOT NULL, id INT NOT NULL, price DECIMAL, PRIMARY KEY(category, id) ) ENGINE=INNODB; CREATE TABLE customer ( id INT NOT NULL, PRIMARY KEY (id) ) ENGINE=INNODB; CREATE TABLE product_order ( no INT NOT NULL AUTO_INCREMENT, product_category INT NOT NULL, product_id INT NOT NULL, customer_id INT NOT NULL, PRIMARY KEY(no), INDEX (product_category, product_id), INDEX (customer_id), FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY (customer_id) REFERENCES customer(id) ) ENGINE=INNODB; 

You can add a foreign key constraint to an existing table using the following ALTER TABLE  syntax: 

ALTER TABLE tbl_name ADD [CONSTRAINT [ symbol ]] FOREIGN KEY [ index_name ] ( col_name , ...) REFERENCES tbl_name  ( col_name ,...) [ON DELETE reference_option ] [ON UPDATE reference_option ] 

The foreign key can be self referential (referring to the same table). When you add a foreign key constraint to a table using ALTER TABLE , remember to first create an index on the column(s) referenced by the foreign key. 

## Dropping Foreign Key Constraints 

You can drop a foreign key constraint using the following ALTER TABLE  syntax: 

ALTER TABLE tbl_name  DROP FOREIGN KEY fk_symbol ; 

If the FOREIGN KEY  clause defined a CONSTRAINT  name when you created the constraint, you can refer to that name to drop the foreign key constraint. Otherwise, a constraint name was generated internally, and you must use that value. To determine the foreign key constraint name, use SHOW CREATE TABLE : 

mysql> SHOW CREATE TABLE child\G *************************** 1. row *************************** Table: child Create Table: CREATE TABLE ‘child‘ ( ‘id‘ int DEFAULT NULL, ‘parent_id‘ int DEFAULT NULL, 

