MySQL的表约束与数据完整性：主键、外键、引用完整性、级联操作

原创

董哥的黑板报 2022-04-02 11:49:27 博主文章分类：MySQL ©著作权

文章标签 数据表结构修改数据表引擎修改 desc alter table 文章分类 代码人生

©著作权归作者所有：来自51CTO博客作者董哥的黑板报的原创作品，请联系作者获取转载授权，否则将追究法律责任

一、主键约束（PRIMARY KEY）

概念：主键，又称主码，是表中一列或者多列的组合
特点：

主键约束要求主键列的数据唯一，并且不能为空（not null）
可以结合外键来定义不同数据表之间的关系，也可以加快数据库查询速度
一个表中最多允许有一个主键
主键分为：单字段主键和多字段主键

创建表时在字段名后指定单字段主键：
create  table  <表名>(
    字段名 数据类型 primary key [默认值]
);
创建表时在末尾指定单/多字段主键（表级约束）：约束名自己定义（[constraint 约束名]也可以不使用）
create  table  <表名>(
    字段名 数据类型 [默认值],
    [constraint 约束名] primary key(字段名,字段名2...)
);
在已有表上增加单/多字段主键约束：
alter table 表名 add primary key(字段1，字段2...);
删除主键约束：一个表中最多只有一个主键，所以直接删除，不需要指定字段
alter table 表名 drop primary key;

二、外键约束（FOREIGN KEY）

首先是表中的一个字段，它可以不是本表的主键，但对应另外一个表的主键。外检主要作用是保证数据引用的完整性。定义外键后，不允许删除在另一个表中具有关联关系的行。外键的作用是保持数据的一致性、完整性。
利用外键关系，你可以在一个表里声明与另一个表里的某个索引相关联的索引。你还可以把自己想要施加在表上的约束条件放到外键关系里。数据库会根据这个关系里的规则来维护数据的引用完整性。
例如，sampdb数据库里的score表包含一个student_id列，我们要用它来把score表里的考试成绩与student表里的学生关联在一起。下面是创建这些表的语句，在它们之间建立了一些明确的关系。例如，把score.student_id定义为了student.student_id列的一个外键。这样可以确保：只把那些其student_id值在student 表里存在的行，插到score表里去。也就是说，这个外键可以防止为不存在的学生输入成绩这一情况的出现。

Create Table: CREATE TABLE `score` (
  `student_id` int unsigned NOT NULL,
  `event_id` int unsigned NOT NULL,
  `score` int NOT NULL,
  PRIMARY KEY (`event_id`,`student_id`),
  KEY `student_id` (`student_id`),
  CONSTRAINT `score_ibfk_1` FOREIGN KEY (`event_id`) REFERENCES `grade_event` (`event_id`),
  CONSTRAINT `score_ibfk_2` FOREIGN KEY (`student_id`) REFERENCES `student` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Create Table: CREATE TABLE `student` (
  `name` varchar(20) NOT NULL,
  `sex` enum('F','M') NOT NULL,
  `student_id` int unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`student_id`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

外键不仅在行的插入操作中很有用，在删除和更新操作中也有用处。例如，我们可以建立这样一个约束条件，即从 student表里删除某个学生时，也要同时自动删除score表里与这个学生有关的所有行。这就是所谓的级联删除 (cascaded delete)，删除操作的效果就像瀑布那样从一个表“流淌”到另外一个表。另外还可以进行级联更新。例如，有了级联更新，那么当在student表里更改某个学生的student_id时，score表里与这个学生相对应的所有行的这个值也会发生相应的变化。
外键可以帮我们维护数据的一致性，并且它们用起来也很方便。如果不使用外键，那么你必须自己负责保证表之间的依赖关系和维护它们的一致性，而这意味着你的应用程序必须增加一些必要的代码。在某些情况下，这样做也不需要花费太多的工夫，你只需额外调用几条DELETE语句，确保在你删除某个表里的行时，其他表里与之相对应的行也会随之一起被删除。但毕竟这是多出来的工作，而且既然数据库引擎能够替你完成数据的一致性检査，那么为何不使用它呢？如果你的表关系非常复杂，那么自动检査功将会特别有用。你完全不用在自己的应用程序里去负责实现这些依赖关系。

父表(主表)、子表(从表)
父表(主表)：对于两个具有关联关系的表而言，相关联字段中主键所在的那个表即是主表。
子表(从表)：对于两个具有关联关系的表而言，相关联字段中外键所在的那个表即是从表。
例如：有一个学生表(student)和一个班级表(class)，学生表中的班号为外键，参考班级表中的主键
父表(主表)：class
子表(从表)：student
父表中的键值可以用来关联两个表。具体来讲，子表里的索引会引用父表里的索引。子表的索引值必须与父表中的索引值相匹配，或者被设置为NULL，以表明在父表里不存在与之对应的行。子表里的这个索引就是所谓的“外键”。也就是说，这个键处在父表的外部，但它又包含了指向父表的值。一个外键关系可以被设置为不接受NULL值，此时所有的外键值都必须与父表里的某个值相匹配。

语法格式
在子表里定义外键的语法如下，这个语法自身很完备，但InnoDB存储引擎目前还没有实现所有的子句语义：
它目前还不支持MATCH子句，即使指定了MATCH子句，它也会被忽略掉。
另外，还有几种action值可以被识别出来，但不会有任何效果。(对于除InnoDB以外的其他存储引擎，它们可以解析整个FOREIGN KEY定义，但会把它完全忽略掉) 。
[CONSTRAINT constraint_name]
FOREIGN KEY [fk_name] (index_columns)
  REFERENCES tbl_name (index_columns)
  [ON DELETE action]
  [ON UPDATE action]
  [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
CONSTRAINT子句。如果提供它，那么它会为外键约束提供一个名字。如果省略它，那么InnoDB存储引擎会创建一个名字。
FOREIGN KEY子句。它会列出子表里的索引列，这些列必须与父表里的索引值相匹配。是外键ID。即使提供了这种ID，它也会被忽略掉。只有InnoDB为这个外键自动创建了索引时，它才会起作用；在这种情况下，该索引名即为fk_name。
REFERENCES子句。它会列出父表及其索引列的名字，让子表里的外键可以引用它们。 REFERENCES子句的部分必须与FOREIGNKEY关键字的部分拥有相同的列数。
OP DELETE和ON UPDATE在下面"级联操作"中介绍。

建立外键关系的准则
外键用来在两个表之间建立连接，可以是一列或多列。一个表可以有多个外键。
外键可以为空值。如果为非空值，则该外键值必须等于另一个表中的主键值。
一致性、完整性：一个外键关联另一个表后，另一个表不可以删除具有关联关系的行。当外键在插入一行新数据时，外键的值必须是主键中存在的，否则不能添加。
子表必须建立索引，且外键列需要放在首位。父表也必须建立索引，且REFERENCES子句里的列需要放在首位。 (也就是说，外键关系所涉及的两个表里的外键列必须被索引)。在定义外键关系之前，必须显式地创建父表的索引。如果在创建子表的CREATE TABLE语句里没有包括这样的索引，那么InnoDB存储引擎将自动在子表里为外键 (即引用列)创建索引。有时，不包括索引，可以让CREATE TABLE语句写起来更容易。不过，由InnoDB自动创建的索引是一个非唯一的索引，并且只包含外键列。如果想让它成为一个PRIMARY KEY或UNIQUE索引，或者想让它能够包括在外键列之外的其他列，那么你就应该在子表里显式地定义它。
父表和子表索引里的对应列必须类型兼容。例如：
数据类型必须一致，不能用一个INT列去匹配一个CHAR列；
对应的字符列必须拥有相同的长度；
对应的整型列必须拥有相同的大小，并且要么都是有符号数，要么都是UNSIGNED的。
不能对外键关系里的字符串列的前缀进行索引。 (也就是说，对于字符串列，必须对整个列进行索引，而不能只对其前面的几个字符进行索引)。
父表与子表的存储引擎必须相同。

创表时指定外键：
外键名为定义的外键约束的名称，一个表中不能有相同名称的外键
字段名表示子表需要添加外键约束的字段列
主表名即被子表外键所依赖的表的名称
主列键表示主表中定义的主键列，或者列组合
CREATE TABLE tudent(
    字段名 数据类型 [默认值],
    [CONSTRAINT 外键名] FOREIGN KEY 字段名1, [,字段名2...] REFERENCES 父表名 主键列1, [,主键列2...]
);
增加外键约束：
ALTER TABLE tbl_name ADD [CONSTRAINT 外键名] FOREIGN KEY(子表字段) REFERENCES 父表名(父表主键);
删除外键约束：通过外键名称删除
ALTER TABLE tbl_name DROP FOREIGN KEY 外键名;

三、级联操作

创建外键时，允许父子表之间有所联系（更新/删除）

格式在上面其实已经介绍过了
on delete {cascade | no  action | set null | restrict}
on update {cascade | no  action | set null | restrict}
cascade
父表记录的删除(delete)或者修改(update)操作会自动删除/修改子表与之对应的记录
no action
父表记录的删除(delete)或者修改(update)操作如果子表存在与之对应的记录，那么删除/修改操作将会失败
set null
父表记录的删除(delete)或者修改(update)操作会将子表中与之对应的记录的外键值自动设置为null值
restrict
与no action相同，是级联操作的默认值
ON DELETE子句：
可以用它来指定在删除父表里的行时，子表应该做些什么。
如果没有指定ON DELETE子句，那么其默认行为是“拒绝从父表里删除仍被子表的行所引用的那些行”。
如果想显式地指定一种action值，那么可以使用下列子句的其中一条：
ON DELETE NO ACTION子句和ON DELETE RESTRICT子句。它们的含义与省略ON DELETE子句一样。有些数据库系统提供了延迟检査功能，而NO ACTION就是一种延迟检查。对于InnoDB，由于外键约束条件会被立即检查，所以NO ACTION和RESTRICT的含义完全一样。
ON DELETE CASCADE子句。它表示的是这样一种行为，即在删除父表里的行时，子表里与之相关联的行也会被删除掉。在本质上，删除产生的效果将从父表蔓延到子表。这样一来，你只需删除父表里的行，然后让InnoDB存储引擎负责从子表里删除相关的行，即可实现一个涉及多表的删除操作。
ON DELETE SET NULL子句。它表示的是这样一种行为，即在删除父表里的行时，子表里与之相关联的索引列会被设置为NULL。如果想使用这个选项，那么必须把外键定义里指定的所有子表索引列，定义为允许有NULL值。(使用这种行为，即暗示着你不能把外键定义为PRIMARY KEY，因为主键不允许有NULL值)。
ON DELETE SET DEFAULT子句。它可以被MySQL正确识別，但尚未实现；InnoDB会将它报告为一个错误。
ON UPDATE子句：
它用来指定：当更新父表里的行时，子表应该做些什么。
如果没有指定这条子句，那么其默认行为是：在子表里，拒绝执行那些会导致其外键值在父表索引里无任何匹配的插入和更新操作；并且会阻止更新那些仍然被子表所引用的父表索引值。
其他可供选用的值与ON DELETE子句的相同，并且具有相同的效果。

演示案例
首先，创建两个名为parent和child的表。其中，child表包含一个外键，它会引用parent表的par_id列：
CREATE TABLE parent
(
  par_id INT NOT NULL,
  PRIMARY KEY(par_id)
)ENGINE=INNODB;

CREATE TABLE child
(
  par_id INT NOT NULL,
  child_id INT NOT NULL,
  PRIMARY KEY(par_id, child_id),
  FOREIGN KEY (par_id) REFERENCES parent(par_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
)ENGINE=INNODB;
child表中的含义为：
ON DELETE CASCADE子句指定了：当从parent表里删除某个行时，MySQL也应该自动从child表里删除与par_id值相匹配的行。
ON UPDATE CASCADE子句指定了：如果更改了parent表里某个行的par_id值，那么MySQL将自动把child表里所有匹配到的par_id值也更改为这个新值。
现在，在parent表里插入一些行，然后在child表离插入一些与之拥有相关键值的行：
INSERT INTO parent (par_id) VALUES(1),(2),(3);
INSERT INTO child (par_id,child_id) VALUES(1,1),(1,2);
INSERT INTO child (par_id,child_id) VALUES(2,1),(2,2),(2,3);
INSERT INTO child (par_id,child_id) VALUES(3,1);
这些语句将产生下面所示的表内容，其中，child表里的每个par_id值都分别与parent 表里的某个par_id值相匹配：
SELECT * FROM parent;
SELECT * FROM child;
为证明InnoDB在插入新记录时会遵从外键关系的约束，我们现在来往child表插入一个行，而其par_id值在parent表里并无匹配：可以看到插入失败
INSERT INTO child (par_id, child_id) VALUES(4,1);
如果想测试级联删除的效果，可以看看在删除parent表里的一个行时会发生什么情况：
DELETE FROM parent WHERE par_id=1;
执行完上面的语句之后，MySQL会把这条DELETE语句的效果级联到child表，如下所示：
SELECT * FROM child;
如果想测试级联更新的效果，可以看看在更新parent表里的一个行时会发生什么情况：
UPDATE parent SET par_id=100 WHERE par_id=2;
SELECT * FROM parent;
SELECT * FROM child;
可以看到子表也跟着一起变化。

演示案例
上面的示例演示了在删除和更新parent表里的行时，需要如何安排那些操作，才能让它们级联删除或更新child表里的相关行。ON DELETE子句和ON UPDATE子句还支持其他动作。例如，你可以让child表里的行都保持原样，而只把它们的外键列设置为NULL。为实现这一点，需要对child表的定义做一些修改。
使用ON DELETE SET NULL来代替ON DELETE CASCADE。这将使InnoDB把外键列(par_id)设置为NULL，而不会删除这些行。
使用ON UPDATE SET NULL来代替 ON UPDATE CASCADE。这将使InnoDB在更新parent表里的行时，把child表里与之匹配的行的外键列(par_id)设置为 NULL。
child表里的par_id列最初被定义成NOT NULL。这无法与ON DELETE SET NULL和ON UPDATE SET NULL配合使用，因此必须把该列的定义更改为允许 NULL值。
在child表的最初定义中，将par_id列定义成PRIMARY KEY的一部分。不过 PRIMARY KEY不允许包含NULL值。因此，在把par_id改成允许NULL值的同时，还需要把PRIMARY KEY改成UNIQUE索引。UNIQUE索引要求索引值必须是唯一的，但NULL值除外，它可以在索引里多次出现。
想要査看这些修改的效果，则需要使用最初的定义重新创建parent表，并在表中加载相同的初始行。然后，用如下所示的新定义创建一个新的child表：
CREATE TABLE parent
(
  par_id INT NOT NULL,
  PRIMARY KEY(par_id)
)ENGINE=INNODB;

CREATE TABLE child
(
  par_id INT NULL,
  child_id INT NOT NULL,
  UNIQUE(par_id, child_id),
  FOREIGN KEY (par_id) REFERENCES parent(par_id)
    ON DELETE SET NULL
    ON UPDATE SET NULL
)ENGINE=INNODB;
现在，当往child表里插入新行时 ，其行为和最初的定义基本一样。也就是说，只有其par_id值在parent表里能找到的行，才允许被插到child表里；否则，将拒绝插入：
INSERT INTO child (par_id,child_id) VALUES(1,1),(1,2);
插入数据：
INSERT INTO parent (par_id) VALUES(1),(2),(3);
INSERT INTO child (par_id,child_id) VALUES(1,1),(1,2);
INSERT INTO child (par_id,child_id) VALUES(2,1),(2,2),(2,3);
INSERT INTO child (par_id,child_id) VALUES(3,1);
请注意，往child表里插入新的行时，与原来相比还是有一点区别。因为par_id列现在被定义为允许NULL值，所以现在可以把包含NULL值的新行插到child表里，而不会出现错误。此外，当从parent表里删除行时，具体的表现行为也存在一点区别。请试着从parent表里删除一行记录，然后检査一下child表的内容，看看会发生什么事情：
DELETE FROM parent WHERE par_id=1;
SELECT * FROM child;
此时，在child里表，所有par_id列的值为1的行都未被删除。而par_id列的值都被设置为了NULL，这正是ON DELETE SET NULL约束所指定的效果。
更新parent表里的行也会有类似的效果：
UPDATE parent SET par_id = 100 WHERE par_id=2;
SELECT * FROM child;

cascade	父表记录的删除(delete)或者修改(update)操作会自动删除/修改子表与之对应的记录
no action	父表记录的删除(delete)或者修改(update)操作如果子表存在与之对应的记录，那么删除/修改操作将会失败
set null	父表记录的删除(delete)或者修改(update)操作会将子表中与之对应的记录的外键值自动设置为null值
restrict	与no action相同，是级联操作的默认值

如果想要查看某个InnoDB表都有哪些外键关系，可以使用SHOW CREATE TABLE语句。
如果在创建一个带有外键关系的表时遇到了问题，那么可以使用SHOW ENGINE INNODB STATUS语句来查看完整的出错消息。

四、删除父表

概念：数据表之间存在外键关联的情况；如果表1（父表）的主键被表2（子表）的外键所关联，那么直接删除表1数据库会报错
删除情景有2种：

先删除子表，然后再删除父表
如果想要保留子表的数据，可以先将子表的外键约束删除，然后再删除父表

下面演示第2种情况

演示案例
创建父表tb_dept，其中id为主键
CREATE TABLE tb_dept(
    id       INT(11) PRIMARY KEY,
    name     VARCHAR(22),
    lacation VARCHAR(50)
);
接着创建子表tb_temp，其种deptid作为外键关联到tb_dept表的id字段上
CREATE TABLE tb_temp(
    id    INT(11) PRIMARY KEY,
    name  VARCHAR(25),
    deptid INT(11),
    salary FLOAT,
    CONSTRAINT fk_emp_dept FOREIGN KEY(deptid) REFERENCES tb_dept(id)
);
使用下面的命令查看tb_temp的外键约束，如下所示：
SHOW CREATE TABLE tb_temp\G
首先尝试直接删除tb_temp，可以看到MySQL报错
DROP TABLE tb_dept;
接着先将tb_temp表的外键约束删除，然后再删除tb_dept表，显示成功
ALTER TABLE tb_temp DROP FOREIGN KEY fk_emp_dept;
DROP TABLE tb_dept;