MySQL数据库的主键和外键详解

MySQL数据库的主键和外键详解
（SQL：外键字段不能为空字符串（''）只能是null或有效值）

1）只有InnoDB类型的表才可以使用外键，mysql默认是MyISAM，这种类型不支持外键约束
2）外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作；
3）外键的作用：
保持数据一致性，完整性，主要目的是控制存储在外键表中的数据。 使两张表形成关联，外键只能引用外表中的列的值！
4）建立外键的前提：
两个表必须是InnoDB表类型。
使用在外键关系的域必须为索引型(Index)。
使用在外键关系的域必须与数据类型相似

主键

主键的定义

主键：表中经常有一个列或多列的组合，其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键，通过它可强制表的实体完整性。当创建或更改表时可通过定义 PRIMARY KEY 约束来创建主键。一个表只能有一个 PRIMARY KEY 约束，而且 PRIMARY KEY 约束中的列不能接受空值。由于 PRIMARY KEY 约束确保唯一数据，所以经常用来定义标识列。

作用:
1）保证实体的完整性;
2）加快数据库的操作速度
3） 在表中添加新记录时，DBMS会自动检查新记录的主键值，不允许该值与其他记录的主键值重复。

4. DBMS自动按主键值的顺序显示表中的记录。如果没有定义主键，则按输入记录的顺序显示表中的记录。

联合主键

关系数据库实际上还允许通过多个字段唯一标识记录，即两个或更多的字段都设置为主键，这种主键被称为联合主键。
对于联合主键，允许一列有重复，只要不是所有主键列都重复即可。

主键的特性

主键的必要性

在有些数据库中，虽然主键不是必需的，但最好为每个表都设置一个主键，不管是单主键还是复合主键。它存在代表着表结构的完整性，表的记录必须得有唯一区分的字段，主键主要是用于其他表的外键关联，以及本记录的修改与删除。

主键的无意义性

由于主键的作用十分重要，如何选取主键会对业务开发产生重要影响。如果我们以学生的身份证号作为主键，似乎能唯一定位记录。然而，身份证号也是一种业务场景，如果身份证号升位了，或者需要变更，作为主键，不得不修改的时候，就会对业务产生严重影响。

选取主键的一个基本原则是：不使用任何业务相关的字段作为主键。

因此，身份证号、手机号、邮箱地址这些看上去可以唯一的字段，均不可用作主键。

主键的选择

• 自增整数类型：数据库会在插入数据时自动为每一条记录分配一个自增整数，这样我们就完全不用担心主键重复，也不用自己预先生成主键；
• 全局唯一GUID类型：使用一种全局唯一的字符串作为主键，类似8f55d96b-8acc-4636-8cb8-76bf8abc2f57。GUID算法通过网卡MAC地址、时间戳和随机数保证任意计算机在任意时间生成的字符串都是不同的，大部分编程语言都内置了GUID算法，可以自己预算出主键。

对于大部分应用来说，通常自增类型的主键就能满足需求
定义的自增性整数类型的主键一般使用是BIGINT NOT NULL AUTO_INCREMENT类型。
如果使用INT自增类型，那么当一张表的记录数超过2147483647（约21亿）时，会达到上限而出错。使用BIGINT自增类型则可以最多约922亿亿条记录。

外键

外键的定义

如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。外键又称作外关键字。

举例：

student表：

image

class表：

image

由于一个班级可以有多个学生，在关系模型中，这两个表的关系可以称为“一对多”，即一个class的记录可以对应多个student表的记录。

为了表达这种一对多的关系，我们需要在student表中加入一列class_id，让它的值与class表的某条记录相对应。

这样，我们就可以根据class_id这个列直接定位出一个student表的记录应该对应到class的哪条记录。

在student表中，通过class_id的字段，可以把数据与另一张表关联起来，这种列称为外键。

外键并不是通过列名实现的，而是通过定义外键约束实现的：

//定义外键约束
ALTER TABLE student
ADD CONSTRAINT fk_class_id  //外键约束的名称fk_class_id可以任意
FOREIGN KEY (class_id)     //指定了class_id作为外键
REFERENCES class (id);    //指定了这个外键将关联到class表的id列（即class表的主键）

class_id为学生表的外键。（此时student表中的class_id与class表中的id表示的含义相同）
此时外键表是student表，主键表是class表。

要删除一个外键约束，也是通过ALTER TABLE实现的：

ALTER TABLE student
DROP FOREIGN KEY fk_class_id;

由于外键约束会降低数据库的性能，大部分互联网应用程序为了追求速度，并不设置外键约束，而是仅靠应用程序自身来保证逻辑的正确性。这种情况下，class_id仅仅是一个普通的列，只是它起到了外键的作用而已。

选取设置 MySQL 外键的字段

定义一个外键时，需要遵守下列规则：

• 父表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则父表与子表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
• 必须为父表定义主键。
• 主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。
• 在父表的表名后面指定列名或列名的组合。这个列或列的组合必须是父表的主键或候选键。
• 外键中列的数目必须和父表的主键中列的数目相同。
• 外键中列的数据类型必须和父表主键中对应列的数据类型相同。

外键的作用

保持数据一致性，完整性。主要目的是控制存储在外键表中的数据。 使两张表形成关联，外键只能引用外表中的列的值或使用空值

外键约束

MySQL外键约束（FOREIGN KEY）用来在两个表的数据之间建立链接，它可以是一列或者多列。一个表可以有一个或多个外键。
外键是表的一个字段，不是本表的主键，但对应另一个表的主键。定义外键后，不允许删除另一个表中具有关联关系的行。

• 主表（父表）
  对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表。
  被引用的表叫主表（父表）。
• 从表（子表）
  对于两个具有关联关系的表而言，相关联字段中外键所在的表就是从表。
  定义了外键的表叫从表（子表）。

外键约束的作用：

阻止执行

• 从表插入新行，其外键值不是主表的主键值便阻止插入；
• 从表修改外键值，新值不是主表的主键值便阻止修改；
• 主表删除行，其主键值在从表里存在便阻止删除(要想删除，必须先删除从表的相关行)；
• 主表修改主键值，旧值在从表里存在便阻止修改(要想修改，必须先删除从表的相关行)。

级联执行

• 主表删除行，连带从表的相关行一起删除；
• 主表修改主键值，连带从表相关行的外键值一起修改。两种方法提供给用户选择。无论选取哪种方法，从表里都不会有多余行。从另一个角度理解，用拒绝同一事物在从表中的标志与主表不一致来实现与主表中的标志一致。

两种实现方法，通过下面方式选择：

• 界面：设级联更新、级联删除两个选择方框，选取则级联执行、不选取则阻止执行；
• 命令：设E)kSCM)E、RESTRICT两个可选项，CASCADE为级联执行、RESTRICT为阻止执行。

CASCADE：级联删除

在外键表的最后，加上on delete cascade 就是级联删除

再删除主表数据时和主表关联的子表数据也会删除

CREATE TABLE `student` (
  `id` bigint(64) NOT NULL AUTO_INCREMENT,
  `class_id` bigint(64) DEFAULT NULL,
  `name` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `age` int(32) DEFAULT NULL,
  `sex` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_class_id` (`class_id`),
  CONSTRAINT `fk_class_id` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`)  
    on delete cascade,
) 

所谓的级联删除，就是删除主键表的同时，外键表同时删除。
以上面的例子将就是，假如班级表中的某个班级被删除了，那么在学生表中要想查询这个被删除的班级号所对应的班级信息就会报错，因为已经不存在这个班级了，所以，删除班级表（主键表）时必须删除其他与之关联的表，这里就说明了外键的作用，保持数据的一致性、完整性。当然反过来讲，你删除学生表中的记录，并不影响班级表中的数据，你查询班级号也能正确查询。所以删除外键表中的数据并不影响主键表。

RESTRICT：约束/限制、NO ACTION(非活动，默认)

当取值为No Action或者Restrict时，则当在主键表中删除对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除。（即外键表约束主键表）

SET NULL：级联置空

在在外键表的最后，加上on delete set null就是级联置空

再删除主表数据时，会把和主表关联的外键设置成NULL

CREATE TABLE `student` (
  `id` bigint(64) NOT NULL AUTO_INCREMENT,
  `class_id` bigint(64) DEFAULT NULL,
  `name` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `age` int(32) DEFAULT NULL,
  `sex` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_class_id` (`class_id`),
  CONSTRAINT `fk_class_id` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`)  
   on  delete  set null,
) 

当取值为Set Null时，则当在主键表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null，（一样是外键表约束主键表，不过这就要求该外键允许取null）。

NO ACTION和RESTRICT的区别：只有在及个别的情况下会导致区别，前者是在其他约束的动作之后执行，后者具有最高的优先权执行。

注意

删除表时，应该先删子表，后删父表，除非使用casecade constraints 解除关联。

先删除父表时会报错：有一个被foreign keys关联的 key的字段在子表中

<e>查询：drop table class

错误代码： 3730
Cannot drop table 'class' referenced by a foreign key constraint 'fk_class_id' on table 'student'.

如果一定要先删除父表

使用casecade constraints解除关联就可以删掉父表。