Eine Einführung in

SQL

Can't INSERT because of reference.

MySQL
If a foreign key is set up between two tables it may be that you cannot insert unless a related record exists. In this example we cannot add ('Tom', 'ma') to t_staff table unless we first create a maths department ('ma', 'Mathematics') in the t_dept table.
CREATE TABLE t_dept(
    id CHAR(2)
   ,fname VARCHAR(20)
   ,PRIMARY KEY(id));

CREATE TABLE t_staff(
    name VARCHAR(20)
   ,dept CHAR(2)
   ,PRIMARY KEY(name)
   ,FOREIGN KEY(dept) REFERENCES t_dept(id))
Execute tidy up code silently


Specific to MySQL
An error is only generated if the table was created as type INNODB. This may be set up as the default in my.cnf or on the create line:
CREATE TABLE t_dept(
    id CHAR(2)
   ,fname VARCHAR(20)
   ,PRIMARY KEY(id)) type=innodb;

CREATE TABLE t_staff(
    name VARCHAR(20)
   ,dept CHAR(2)
   ,PRIMARY KEY(name)
   ,FOREIGN KEY(dept) REFERENCES t_dept(id)) type=innodb
error 1216
Cannot add or update a child row: a foreign key constraint fails