MySQL FK
Foreign Key Constraints with MySQL
Foreign key constraints are not enforced with MySQL MyISAM tables.
They do work with InnoDB tables.
The following illustrates the difference.
| MyISAM |
InnoDB |
drop table t2;
drop table t1;
create table t1
(
pk integer,
value integer,
primary key (pk)
) type=myisam;
create table t2
(
pk integer,
fk integer,
value integer,
primary key (pk),
constraint myfk foreign key (fk) references t1 (pk)
) type=myisam;
insert into t2 (pk,fk,value) values (1,1,123);
insert into t1 (pk,value) values (1, 123);
insert into t2 (pk,fk,value) values (1,1,123);
delete from t1;
drop table t1;
|
drop table t2;
drop table t1;
create table t1
(
pk integer,
value integer,
primary key (pk)
) type=innodb;
create table t2
(
pk integer,
fk integer,
value integer,
primary key (pk),
constraint myfk foreign key (fk) references t1 (pk)
) type=innodb;
insert into t2 (pk,fk,value) values (1,1,123);
insert into t1 (pk,value) values (1, 123);
insert into t2 (pk,fk,value) values (1,1,123);
delete from t1;
drop table t1;
|
The MyISAM tables do no register an error about the syntax of the referential integrity constraints.
We do get an error with the data entry in the above example, but this is due to a unique constraint.
The first insert worked when it should not have worked.
Here are the above MyISAM lines executed:
mysql>
mysql> create table t1
-> (
-> pk integer,
-> value integer,
-> primary key (pk)
-> ) type=myisam;
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql>
mysql> create table t2
-> (
-> pk integer,
-> fk integer,
-> value integer,
-> primary key (pk),
-> constraint myfk foreign key (fk) references t1 (pk)
-> ) type=myisam;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> insert into t2 (pk,fk,value) values (1,1,123);
Query OK, 1 row affected (0.00 sec) <--- Ouch, this should not work!
mysql> insert into t1 (pk,value) values (1, 123);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2 (pk,fk,value) values (1,1,123);
ERROR 1062 (23000): Duplicate entry '1' for key 1
mysql> delete from t1;
Query OK, 1 row affected (0.00 sec)
mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)
|
If we change the table type to be innodb, it works like we would expect.
mysql> drop table t2;
Query OK, 0 rows affected (0.04 sec)
mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> create table t1
-> (
-> pk integer,
-> value integer,
-> primary key (pk)
-> ) type=innodb;
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql>
mysql> create table t2
-> (
-> pk integer,
-> fk integer,
-> value integer,
-> primary key (pk),
-> constraint myfk foreign key (fk) references t1 (pk)
-> ) type=innodb;
Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql> insert into t2 (pk,fk,value) values (1,1,123);
ERROR 1216 (23000): Cannot add or update a child row: a foreign key constraint fails
^--- This is how it should work ---^
mysql> insert into t1 (pk,value) values (1, 123);
Query OK, 1 row affected (0.02 sec)
mysql> insert into t2 (pk,fk,value) values (1,1,123);
Query OK, 1 row affected (0.01 sec)
mysql> delete from t1;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql> drop table t1;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql>
|
|
