INSERT problems: reference

Can't INSERT because of reference.

schema:scott

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))

DROP TABLE t_staff;
DROP TABLE t_dept;

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));

INSERT INTO t_dept VALUES ('co','computing');
INSERT INTO t_staff VALUES ('andrew','co');
INSERT INTO t_staff VALUES ('tom', 'ma');
SELECT * FROM t_staff

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

We can examine this constraint to find the table that has it...

Foreign key references are not respected.

INSERT and DELETE Operations

INSERT new records

UPDATE existing records

DELETE records

INSERT ... SELECT

INSERT: Not all fields need to be specified

INSERT a date

Explicitly enter a NULL

INSERT problems: reference

DELETE problems: reference

INSERT problems: incompatible formats

String contains a quote '

Data normalization