ACID-compliant database engines like InnoDB provide foreign keys to automate RI maintenance. Simple example: customers have orders, which have items; by defining a customers foreign key in the orders table and an orders foreign key in the items table, you ensure that no orders or items row gets orphaned.
SQL implements FKs as key objects defined by CREATE TABLE and ALTER TABLE. MySQL syntax is:
[CONSTRAINT symbol] FOREIGN KEY [constraintID] (keycolumnname, ...)
REFERENCES tbl_name (keycolumnname, ...)
[ ON DELETE { RESTRICT | CASCADE | SET NULL | NO ACTION } ]
[ ON UPDATE { RESTRICT | CASCADE | SET NULL | NO ACTION } ]
To drop a FK in MySQL you need to know the CONSTRAINT symbol, so it's good practice to adopt a standard naming convention and use it here.
A foreign key should reference its table's immediate parent. In a chain like customers->orders->items, the items table does not carry a direct customer reference.
Example:
drop table if exists c,b,a; create table a (ai int primary key); insert into a values(1),(2); create table b ( bi int primary key, ai int, foreign key(ai) references a(ai) on delete cascade on update cascade ); insert into b values(1,1),(2,2); create table c ( ci int primary key, bi int, foreign key(bi) references b(bi) on delete cascade on update cascade ); insert into c values(1,1),(2,2); -- join to see all three levels: select a.ai, b.bi, c.ci from a join b using(ai) join c using(bi); +----+----+----+ | ai | bi | ci | +----+----+----+ | 1 | 1 | 1 | | 2 | 2 | 2 | +----+----+----+ delete from a where ai=1; select a.ai, b.bi, c.ci from a join b using(ai) join c using(bi); +----+----+----+ | ai | bi | ci | +----+----+----+ | 2 | 2 | 2 | +----+----+----+
Last updated 9 Oct 2024
