| SQLite |
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)) |
|
|
| Specific to SQLite |
|
Foreign key references are not respected.
|
| DB2 |
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)) |
|
|
| Specific to DB2 |
| none |
| MS Access |
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)) |
|
|
| Specific to MS Access |
| none |
| PostgreSQL |
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)) |
|
|
| Specific to PostgreSQL |
| none |
| Oracle |
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)) |
|
|
| Specific to Oracle |
|
We can examine this constraint to find the table that has it... |
| MS SQL Server |
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)) |
|
|
| Specific to MS SQL Server |
| none |
| 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)) |
|
|
| 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
|
| Sybase |
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)) |
|
|
| Specific to Sybase |
| none |
| Mimer SQL |
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)) |
|
|
| Specific to Mimer SQL |
| none |