SQL FOREIGN KEY 约束

SQL FOREIGN KEY 约束一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)。

一、数据表准备

“persons” 表：

“orders” 表：

“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--级联删除
    );
1
2
3
4
5
6
7
8
9

下面的 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 )
)
1
2
3
4
5
6
7
8

2.2、在创建表后增加外键约束

ALTER TABLE 表名
    ADD CONSTRAINT 外键约束名
    FOREIGN KEY (column1, column2,...column_n) 
    REFERENCES 外键所依赖的表 (column1,column2,...column_n)
    ON DELETE CASCADE;--级联删除
1
2
3
4
5

ALTER TABLE orders
ADD FOREIGN KEY (p_id )
REFERENCES persons(p_id )
1
2
3

注意，在创建外键约束时，必须先创建外键约束所依赖的表，并且该列为该表的主键

三、对指定外键的表进行增删改的情况总结

子表：谁创建外键谁就是子表

父表：这个外键所依赖的表

3.1、删除时，未指定cascade时

1）删除父表数据

  a.因为子表与父表一一对应，删除父表数据时，需要先把子表对应数据删除否则无法删除

  b. 同理，删除表的时候，也需要先删除子表再删除父表
  
  也就是删除person表的id=1的数据现在是删除不了的，或者直接删除person表也是删不了的。
1
2
3
4
5

解决方案：

 a.指定cascade，删除父表、数据

    CASCADE指当删除主表中被引用列的数据时，级联删除子表中相应的数据行。

 b.禁用约束（子表的外键约束）

     ALTER TABLE 表名 disable constraint 约束名;
1
2
3
4
5
6
7

2）删除子表：可以删除子表或者数据不报错

3.2、更新时

  注意：（这里的更新指的是更新外键那个字段）
  
    a.更新父表会违反约束（更新person表id=1的改成4会报错）

    b.可以更新子表（可以更新子表的外键，也就是可以将order的p_id为1的改成2）

    c.没有针对约束的级联更新
1
2
3
4
5
6
7

3.3、插入时

     a.父表可以插入（person表可以插入id=4的一行数据）

     b.子表插入会违反约束（order表的p_id插入person表没有的id数值会报错，也就是order表插入4,4,4会报错）
1
2
3