目录
SQL FOREIGN KEY 约束一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)。
一、数据表准备
“persons” 表:
id | name | age |
---|---|---|
1 | tom | 23 |
2 | john | 21 |
3 | jerry | 18 |
“orders” 表:
id | order_no | p_id |
---|---|---|
1 | 77895 | 3 |
2 | 44678 | 3 |
3 | 22456 | 2 |
4 | 12431 | 1 |
“orders” 表中的 “p_id” 列指向 “persons” 表中的 “id” 列。
“persons” 表中的 “id” 列是 “persons” 表中的 PRIMARY KEY(主键)。
“orders” 表中的 “p_id” 列是 “orders” 表中的 FOREIGN KEY(外键)。
FOREIGN KEY 约束用于预防破坏表之间连接的行为。
FOREIGN KEY 约束也能防止非法数据插入外键列,因为它必须是它指向的那个表中的值之一。
二、创建外键约束的方式
2.1、在创建表的时候指定外键约束
CREATE TABLE 表名
(
column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT 外键约束名 FOREIGN KEY (column1,column2,... column_n)
REFERENCES 外键依赖的表 (column1,column2,...column_n)
ON DELETE CASCADE--级联删除
);
下面的 SQL 在 “orders” 表创建时在 “p_id” 列上创建 FOREIGN KEY 约束:
MySQL:
CREATE TABLE orders
(
id int NOT NULL,
order_no int NOT NULL,
p_id int,
PRIMARY KEY (id ),
FOREIGN KEY (p_id) REFERENCES persons( p_id )
)
2.2、在创建表后增加外键约束
ALTER TABLE 表名
ADD CONSTRAINT 外键约束名
FOREIGN KEY (column1, column2,...column_n)
REFERENCES 外键所依赖的表 (column1,column2,...column_n)
ON DELETE CASCADE;--级联删除
ALTER TABLE orders
ADD FOREIGN KEY (p_id )
REFERENCES persons(p_id )
注意,在创建外键约束时,必须先创建外键约束所依赖的表,并且该列为该表的主键
三、对指定外键的表进行增删改的情况总结
子表:谁创建外键谁就是子表
父表:这个外键所依赖的表
3.1、删除时,未指定cascade时
1)删除父表数据
a.因为子表与父表一一对应,删除父表数据时,需要先把子表对应数据删除否则无法删除
b. 同理,删除表的时候,也需要先删除子表再删除父表
也就是删除person表的id=1的数据现在是删除不了的,或者直接删除person表也是删不了的。
解决方案:
a.指定cascade,删除父表、数据
CASCADE指当删除主表中被引用列的数据时,级联删除子表中相应的数据行。
b.禁用约束(子表的外键约束)
ALTER TABLE 表名 disable constraint 约束名;
2)删除子表:可以删除子表或者数据不报错
3.2、更新时
注意:(这里的更新指的是更新外键那个字段)
a.更新父表会违反约束(更新person表id=1的改成4会报错)
b.可以更新子表(可以更新子表的外键,也就是可以将order的p_id为1的改成2)
c.没有针对约束的级联更新
3.3、插入时
a.父表可以插入(person表可以插入id=4的一行数据)
b.子表插入会违反约束(order表的p_id插入person表没有的id数值会报错,也就是order表插入4,4,4会报错)