MENU


FOREIGN KEY

Foreign keys can be defined for a table to cross-reference related data across tables. A foreign key value can only be created in the child table, if there is a matching candidate key value in the parent table.
The FOREIGN KEY clause is specified in the child table. The parent and child tables must use the same storage engine. They must not be TEMPORARY tables. Corresponding columns must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same. The character set and collation must be the same.
If the CONSTRAINT symbol clause is given, the symbol value must be unique in a database.


ON UPDATE        and        ON DELETE
CASCADE: Delete or update the rows from the parent table and child table automatically.
SET NULL: Delete or update the rows from the parent table, and set the foreign key columns in the child table to NULL.
RESTRICT (default): Rejects the deletion or update operation for the parent table
NO ACTION: Same as RESTRICT in MySQL.
SET DEFAULT: Recognized by the MySQL parser, but rejected by InnoDB. 

DROP TABLE IF EXISTS parent1,parent2,child;
CREATE TABLE parent1(
   a INT,
   b INT,
   PRIMARY KEY (a,b)
);
CREATE TABLE parent2(
   c VARCHAR(10) PRIMARY KEY
);
CREATE TABLE child(
   x INT,
   y INT,
   z VARCHAR(10),
   FOREIGN KEY (x,y) REFERENCES parent1(a,b) 
      ON DELETE CASCADE
      ON UPDATE RESTRICT,
   CONSTRAINT zc
      FOREIGN KEY (z) REFERENCES parent2(c) 
      ON DELETE CASCADE
);
INSERT INTO parent1 VALUES (1,2);
INSERT INTO parent2 VALUES ('hello');
INSERT INTO child VALUES (1,2,'hello');
#INSERT INTO child VALUES (1,2,'world');  # this fails!
ALTER TABLE child DROP FOREIGN KEY zc;
INSERT INTO child VALUES (1,2,'world');  # this works!