Mysql05——外键约束

MySQL外键约束

一、MySQL外键约束作用

外键约束（Foreign Key）即数据库中两个数据表之间的某个列建立的一种联系。这种联系通常是以实际场景中含义完全相同的字段所造成的。MySQL通过外键约束的引入，可以使得数据表中的数据完整性更强，也更符合显示情况。

在 MySQL 中，外键约束是一种用于维护表之间关系的约束。外键约束可以用来指定一个或多个列作为外键，并将其连接到另一个表中的主键或唯一键上。这可以确保数据的完整性，并支持关系型数据库的引用完整性。

下面，我举一个例子来说明MySQL外键约束的作用。

假如我们对大学学生成绩管理系统建立数据库，有两张表，一张表是学生表，存储了学生的学号、姓名、性别、院系等信息，还有一张表是成绩表，存储了学生学号、课程编号、考试成绩等信息。这样，这两张表之间就会通过学生学号建立外键约束。很自然的我们想到，成绩表的学生学号依赖于学生表的学生学号存在，如果一个学生毕业、或者退学，从学生表中删除时，那么他的相关成绩也就没有必要在成绩表中存在了。在没有创建外键关系之前，这两张表完全是独立存在的，我们可以强行在成绩表中插入一个不存在学生的相关成绩，也可以强行删除学生表中的一个学生，并且不管其成绩信息是否在成绩表中存在。但是，在建立外键关系后，MySQL数据库会约束上述两种行为，每次对数据进行插入或者删除时，都会检查数据完整性，使得我们的操作必须符合实际情况。

二、外键约束创建及应用

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

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

在 CREATE TABLE 语句中，通过 FOREIGN KEY

在创建数据表时创建外键约束，只需要在创建数据表的create语句后面，使用foreign key关键字指定本表的外键字段，使用reference关键字指定关联表的关联字段，并且明确约束行为即可。

[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…]
REFERENCES <主表名> 主键列1 [，主键列2，…]

示例

1、创建一个班级表

create table class (  
    cno int(4) auto_increment, 
    cname varchar(12) not null,  
    room  varchar(4),   
    primary key(cno)  
);

2、插入数据

insertinto class values(null,'bigdata001',101);insertinto class values(null,'bigdata002',102);insertinto class values(null,'bigdata003',101);

3、查询数据

select*from class;

4、创建一个学生表

create table student(  
    sno int(6) primary key auto_increment,  
    name varchar(12),  
    sex char(1),  
    age int(2),  
    classno int(4),  
    constraint fk_stu_classno foreign key (classno) references class(cno)  
);

当外键约束被添加后，以下行为会发生：

• 如果插入数据时，引用列的值在被引用表中不存在，将会触发错误，阻止插入操作。
• 如果更新数据时，引用列的值在被引用表中不存在，将会触发错误，阻止更新操作。
• 如果想要删除被引用表中的数据，而有其他表中的数据引用了被删除的数据，将会触发错误，阻止删除操作（除非使用了级联操作进行处理）。

5 插入数据

insertinto student values(null,'张三','男',23,1);insertinto student values(null,'李四','男',24,1);insertinto student values(null,'王五','男',16,2);

6、查询student表数据

select*from student

7、尝试删除、更改1班

deletefrom class where cno=1;update class set cno=5where cno=2;

因为存在外键，class表中cno=1中有学生存在，所以删除不了。

如果想删除1班，请手动先对1班学生进行处理（删除或者清空外键）

update student set classno=nullwhere classno=1;

8、再次删除cno=1的班级

deletefrom class where cno=1;

9、修改外键设置：外键要修改只能先删除再添加

如果希望在更新班级号的时候，可以直接更新学生的班级编号；希望在删除某个班级的时候，清空学生的班级编号

（1）删除外键

altertable student dropforeignkey fk_stu_classno;

2）修改外键设置

altertable student addconstraint fk_stu_classno foreignkey(classno)references class(cno)ondeletesetnullonupdatecascade;

通过创建外键约束，当进行插入或更新操作时，数据库会自动检查外键的值是否存在于参照表中的主键或唯一键中。如果外键引用不存在，操作将被拒绝，并引发错误。

需要注意以下几点：

• 外键约束在创建时需要满足引用完整性的条件，即被引用的键必须是唯一的。
• 外键约束也可以定义为级联更新或删除。这意味着在更新或删除参照表中的键时，相关联的外键将自动更新或删除。
• 创建外键约束时，确保相关的列具有相似的数据类型和长度，以避免不匹配的错误。

上述是在 MySQL 中使用外键约束的基本步骤。外键约束可以帮助维护表之间的关系，并确保数据的一致性和完整性。