CREATE TABLE parent (<br /> id INT NOT NULL,<br /> PRIMARY KEY (id)<br /> ) ENGINE=INNODB;</p> <p>CREATE TABLE child (<br /> id INT,<br /> parent_id INT,<br /> INDEX par_ind (parent_id),<br /> FOREIGN KEY (parent_id)<br /> REFERENCES parent(id)<br /> ON DELETE CASCADE<br /> ) ENGINE=INNODB;

CREATE TABLE products(<br /> productId INT AUTO_INCREMENT PRIMARY KEY,<br /> productName varchar(100) not null,<br /> categoryId INT NOT NULL,<br /> CONSTRAINT fk_category<br /> FOREIGN KEY (categoryId)<br /> REFERENCES categories(categoryId)<br /> ON UPDATE CASCADE<br /> ON DELETE CASCADE<br /> ) ENGINE=INNODB;<br />

</p> <p> CREATE TABLE products(<br /> productId INT AUTO_INCREMENT PRIMARY KEY,<br /> productName varchar(100) not null,<br /> categoryId INT NOT NULL,<br /> CONSTRAINT fk_category<br /> FOREIGN KEY (categoryId)<br /> REFERENCES categories(categoryId)<br /> ON UPDATE CASCADE<br /> ON DELETE CASCADE<br /> ) ENGINE=INNODB;

ALTER TABLE `tabl`<br /> ADD CONSTRAINT `constraint_name` FOREIGN KEY (`column`) REFERENCES `table2` (`id`) ON DELETE CASCADE;<br />

— ADD foreign key<br /> ALTER TABLE TableName ADD FOREIGN KEY (Table_ID) REFERENCES OtherTable (OtherTable_ID);

Here is the basic syntax of defining a foreign key constraint in the CREATE TABLE or ALTER TABLE statement:</p> <p>[CONSTRAINT constraint_name]<br /> FOREIGN KEY [foreign_key_name] (column_name, …)<br /> REFERENCES parent_table(colunm_name,…)<br /> [ON DELETE reference_option]<br /> [ON UPDATE reference_option]<br /> In this syntax:</p> <p>First, specify the name of foreign key constraint that you want to create after the CONSTRAINT keyword. If you omit the constraint name, MySQL automatically generates a name for the foreign key constraint.</p> <p>Second, specify a list of comma-separated foreign key columns after the FOREIGN KEY keywords. The foreign key name is also optional and is generated automatically if you skip it.</p> <p>Third, specify the parent table followed by a list of comma-separated columns to which the foreign key columns reference.</p> <p>Finally, specify how foreign key maintains the referential integrity between the child and parent tables by using the ON DELETE and ON UPDATE clauses. The reference_option determines action which MySQL will take when values in the parent key columns are deleted (ON DELETE) or updated (ON UPDATE).</p> <p>MySQL has five reference options: CASCADE, SET NULL, NO ACTION, RESTRICT, and SET DEFAULT.</p> <p>CASCADE: if a row from the parent table is deleted or updated, the values of the matching rows in the child table automatically deleted or updated.<br /> SET NULL: if a row from the parent table is deleted or updated, the values of the foreign key column (or columns) in the child table are set to NULL.<br /> RESTRICT: if a row from the parent table has a matching row in the child table, MySQL rejects deleting or updating rows in the parent table.<br /> NO ACTION: is the same as RESTRICT.<br /> SET DEFAULT: is recognized by the MySQL parser. However, this action is rejected by both InnoDB and NDB tables.<br /> In fact, MySQL fully supports three actions: RESTRICT, CASCADE and SET NULL.</p> <p>If you don’t specify the ON DELETE and ON UPDATE clause, the default action is RESTRICT.</p> <p>MySQL FOREIGN KEY examples<br /> Let’s create a new database called fkdemo for the demonstration.</p> <p>CREATE DATABASE fkdemo;</p> <p>USE fkdemo;<br /> RESTRICT & NO ACTION actions<br /> Inside the fkdemo database, create two tables categories and products:</p> <p>CREATE TABLE categories(<br /> categoryId INT AUTO_INCREMENT PRIMARY KEY,<br /> categoryName VARCHAR(100) NOT NULL<br /> ) ENGINE=INNODB;</p> <p>CREATE TABLE products(<br /> productId INT AUTO_INCREMENT PRIMARY KEY,<br /> productName varchar(100) not null,<br /> categoryId INT,<br /> CONSTRAINT fk_category<br /> FOREIGN KEY (categoryId)<br /> REFERENCES categories(categoryId)<br /> ) ENGINE=INNODB;<br /> The categoryId in the products table is the foreign key column that refers to the categoryId column in the categories table.</p> <p>Because we don’t specify any ON UPDATE and ON DELETE clauses, the default action is RESTRICT for both update and delete operation.