数据库02_约束

数据库的约束之完整性约束&主键约束&外键约束

约束就是我们在建表过程中，给表中字段添加一些条件，在这些条件的配合下可以保证数据的唯一性，完整性，有的约束针对于整行有效，有的约束只针对某一列有效。

一、完整性约束：

• 列值要求（约束）
  • 输入类型是否正确？年龄必须是数字
  • 输入的格式是否正确？身份证号码必须是18位
• 整行要求（约束）
  • 是否在允许的范围内？性别只能是“男”或“女”
  • 是否存在重复输入？学员信息输入了两次
  • 是否符合其他特定要求？信誉值大于5的用户才能够加入会员列表
• 域完整性：限制数据类型，外键约束，默认值，非空约束。
• 实体完整性约束：唯一约束，主键约束，自增列。
• 参照完整性：主外键关联。
• 自定义完整性：规则，存储过程，触发器。

二、主键约束

表中有一列或几列组合的值能用来唯一标识表中的每一行，这样的一列或多列的组合叫做表的主键。如：学号可以作为学生表的主键，课程号可以作为课程表的主键，（学号、课程号）作为成绩表的主键（组合键）。

主键标识的列不能为空不能重复，唯一标识。
一个表只能有一个主键，主键约束确保了表中的行是唯一的。
表中可以没有主键，但是通常情况下应当为表设置一个主键。

• 主键选择原则：
  • 1、最少性：尽量选择一个键作为主键
  • 2、稳定性：尽量选择数值更新少的值作为主键
  • 3、去业务性：尽量不选择具备业务含义的列作为主键，例如：身份证，电环号码……
• 主键的3种方式：
  • 1、直接在字段定义后面声明主键

    create table student_info (
                id bigint primary key,
                student_name varchar(20),
                age tinyint,
                gender tinyint,    
                telphone varchar(11),
                study_direction varchar(20)) 
                engine=innodb character set = utf8mb4;
                

  • 2、用constraint声明主键

    #规范pk_表名
    create table student_info( 
                id bigint, 
                studneg_name varchar(20), 
                age tinyint, 
                gender tinyint, 
                telphone varchar(11), 
                study_direction varchar(20),
                CONSTRAINT pk_student_info primary key (id)) 
                engine=innodb character set = utf8mb4;
    
    #设置student_name和telphone为符合键
    create table student_infoo(
                id bigint, 
                student_name varchar(10), 
                age tinyint,
                gender tinyint, 
                telphone varchar(11), 
                study_direction varchar(20), 
                PRIMARY KEY(student_name,telphone)) 
                engine=innodb character r set = utf8mb4;

  • 3、用ALTER语句补充声明主键

    create table student_info (
                id bigint,
                student_name varchar(20),
                age tinyint,
                gender tinyint,    
                telphone varchar(11),
                study_direction varchar(20)) 
                engine=innodb character set = utf8mb4;
    #ALTER语句补充声明主键           
    ALTER TABLE student_info add CONSTRAINT pk_studnet_info PRIMARY KEY (id);
    
    
    #删除主键的方式：
    ALTER TABLE student_info DROP PRIMARY KEY;

三、外键约束

1、为什么要添加外键约束？

假设学生表中学生的学号从3000到3110的，如果在成绩表中添加学号为3111的学生的成绩，那么这条数据在成绩表里面就是脏数据。同时如果学生表中某个学生退学，那么删除学生表中的学生的话，会在成绩表中产生脏数据。

外键约束可以使得两张表关联，保证数据的一致性和实现一些级联操作。

表结构：

学生表：学号(主键)、姓名、电话、性别、年龄、学历、学校、时间
课程表：课程编号(主键)、课程名称、授课教师、时间
成绩表：学号、课程编号、成绩、时间

学生表主键学号? 课程表主键课程编号? 成绩表表没有主键

学号不能做主键，一个学生可以有多门课程的成绩
课程编号也不能做主键，一门课程会有多个学生的成绩

在成绩表中新增一列成绩ID，用于唯一标识成绩?

有些表没有可以用来作为主键的字段时，可以设一列专门的ID，通过一个整型来进行自增长。

?学生表：学号(主键)、姓名、电话、性别、年龄、学历、学校、时间
课程表：课程编号(主键)、课程名称、授课教师、时间
成绩表：成绩ID(添加自增长列作为主键)、学号(外键关联学生表）、课程编号(外键关联课程表)、成绩、时间

2、外键约束的作用

• 外键：主表里面某一列引用了外表。

成绩表的sid外键，当在grade表中添加student表中不存在的sid学生的成绩时就会报错。
因为主表student中不存在088这个sid，所以就不能随便插入，必须是主表中存在的才能插入

• 在主表中删除这条删除学生成绩也不能删

外键删除的时候和更新的时候都是受限的，通常建议使用RESTRICT。可选的包括RESTRICT、NOACTION、CASCADE（级联删除）、SET NULL。
在成绩表中成绩ID为主键，课程编号作为外键关联到课程表，学号作为外键关联到学生表。

3、外键的定义语法

[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, …)

REFERENCES tbl_name (index_col_name, …)

[ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

[ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用，如果不指定CONSTRAINT symbol，MYSQL会自动生成一个名字。

ON DELETE、ON UPDATE表示事件触发限制，可设参数：

① RESTRICT（限制外表中的外键改动，默认值）

② CASCADE（跟随外键改动）

③ SET NULL（设空值）

④ SET DEFAULT（设默认值）

⑤ NO ACTION（无动作，默认的）

create table student_course_info(
            id bigint primary key,
            fk_student_id bigint,
            fk_course_id bigint,
            score int,
            choice_date date,
            foreign key (fk_student_id) references student_info(id) on delete cascade on update cascade,
            foreign key (fk_course_id) references course_info(id) on delete cascade on update cascade)
            engine=innodb character set = utf8mb4;

4、外键约束（图形化连接）

当在成绩表中添加学生表中不存在的学生的成绩时就会报错

?因为主表学生表中不存在088这个sid，所以就不能随便插入，必须时主表中存在的才能插入如果在主表中删除某条学生成绩也不能删。

?外键删除的时候和更新的时候都是受限的，通常建议的也是RESTRICT。
?

?关于外键的学习可参考：https://blog.csdn.net/lianafany/article/details/104773784