约束：用于限制表中的数据，为了保证表中数据的准确和可靠性 ( 一致性 )

六大约束

  • NOT NULL：非空约束，用于保证对应字段的值不为 NULL
  • DEFAULT：默认约束，用于保证该字段的值有指定默认值
  • PRIMARY KEY：主键约束，用于保证该字段的值具有唯一性，并且非空
  • UNIQUE：唯一约束，用于保证该字段的值具有唯一性，但是可以为空
  • CHECK：检查约束 ( mysql 不支持 )，用于限制字段的值
  • FOREIGN KEY：外键约束，用于限制两张表的关系，用于保证从表中的字段值来自主表中的关联列的值 ( 所以该约束在从表中使用，引用主表中某列的值 )

约束可以在创建表时添加，也可以在修改表时添加 ( 前提是数据没有添加 )

根据约束添加位置的不同，其分类有列级约束和表级约束

  1. create table 表名(
  2.     字段名 字段类型 列级约束.
  3.     字段名 字段类型
  4.     ...
  5.     表级约束
  6. );

列级约束：六大约束在语法上都支持，但外键约束没有效果

表级约束：除了 NOT NULL 和 DEFAULT， 其他的都支持

创建表时添加列级约束

创建库 stu

CREATE DATABASE stu;

创建表时添加列级约束

CREATE TABLE major(
    id INT PRIMARY KEY,
    major_name VARCHAR(20) NOT NULL
);

create table stuinfo(
    id int primary key,
    stu_name varchar(20) not null,
    gender bit(1) check(gender in("男", "女")), #检查约束 (mysql不支持，没效果)
    seat int unique, #唯一约束
    age int default 18, #默认约束
    major_id int references major(id) #外键关联 (其实没有效果)
);

PS：外键约束在列级约束上不产生效果

使用命令

show index from 表名;

约束 - 图1

发现外键约束并没有显示出来

该命令能够查看对应表中的所有索引，包括主键约束，外键约束，唯一约束和后面会学到的自定义索引

PS：可以添加多个列级约束，约束之间使用空格隔开，没有顺序要求

gender bit(1) not null default "男";

创建表时添加表级约束

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
    id INT,
    stu_name VARCHAR(20),
    gender BIT(1),
    seat INT,
    age INT,
    major_id INT,

    CONSTRAINT s_m_key PRIMARY KEY(id), #设置 id 为主键
    CONSTRAINT o_seat UNIQUE(seat), #设置 seat 为唯一键
    CONSTRAINT c_gender CHECK(gender IN ("男", "女")), #检查约束，依然没用
    CONSTRAINT fk_major_stuinfo FOREIGN KEY(major_id) REFERENCES major(id) #设置主表某一字段为外键关联从表中的指定字段
);

约束 - 图2

表级约束的语法

create table 表名(

    在所有字段的最下面
    [CONSTRAINT 约束名] 约束类型(指定列),
    ...
);

[] 内表示可以省略，约束名会有默认的

写约束的话，一般能放在列级的就放在列级

主键和唯一的区别(面试)

保证唯一性 是否可以为空 表中可以有多个 是否允许多个字段组合
主键 √ x x √
唯一 √ √ √ √

PS：唯一约束的字段可以插入 null，但是只能有一个 null，多个 null 就认为不唯一了

PS：主键和唯一都可以使用多个字段来进行约束，将多个字段的组合看成是一个主键 / 唯一，该组合具有主键 / 唯一的特性 （但是不推荐组合）

primary key(字段1, 字段2)
unique(字段1, 字段2)

外键的特点

  1. 要求在从表上设置外键关系

  2. 从表的外键字段的类型和主表的关联字段的类型要求一致或兼容
    约束 - 图3

  3. 主表中的关联字段必须是一个 key (一般是主键或唯一)

  4. 插入数据时，先插入主表数据，再插入从表数据；删除数据时，先删除从表，再删除主表

但是对于每次删除都需要按照顺序删而言是很麻烦的，因此引入了级联删除的概念；而如果不想删除主表关联的从表数据，只想把从表的外键字段的值置空的话，则又引入了级联置空的概念

级联删除和级联置空

将创建了的两张表 stuinfo 和 major，前者所有的 key 都删除，后者只保留主键，然后插入以下数据

insert into major
values(1, "jvav"), (2, "redis"), (3, "kafka");

INSERT INTO stuinfo
VALUES
(1, "j1", 1, NULL, NULL, 1),
(2, "j1", 1, NULL, NULL, 2),
(3, "j1", 1, NULL, NULL, 3),
(4, "j1", 1, NULL, NULL, 1),
(5, "j1", 1, NULL, NULL, 3),
(6, "j1", 1, NULL, NULL, 1);

级联删除

PS！！！ MyISAM 存储引擎不支持级联删除，测试前先保证表的存储引擎为 InnoDB

对于需要级联删除的外键字段，在添加外键时，需要追加关键字 on delete cascade

因此我们设置 major_id 为外键时应该这么写

alter table stuinfo add constraint fk_stu_major foreign key(major_id) references major(id) on delete cascade;

然后，这时我们再去删除主表中的数据

delete from major where id = 1;

可以发现没有报错

再次查看 stuinfo 表

约束 - 图4

可以发现级联删除成功

级联置空

级联置空的追加关键字为 on delete set null

#删除 stuinfo 上原来的外键
alter table stuinfo drop foreign key fk_stu_major;

#清空数据再添加
delete from major;
delete from stuinfo;

#添加级联置空外键
alter table stuinfo add constraint fk_stu_major foreign key(major_id)
references major(id) on delete set null;

#删除 id = 1 的 major 后查看 stuinfo 表
delete from major where id = 1;
select * from stuinfo;

约束 - 图5

可以发现， 引用了 id = 1 的 major 的 stuinfo 记录并没有被删除，而是外键字段置为了 null

修改表时添加约束

添加列级约束语法

alter table 表名 modify column 列名 类型 约束 [约束2 约束3 ...];

相对应的，想要删除约束，只需要在修改表的字段时不添加约束即可

添加表级约束语法

alter table 表名 add [constraint 约束名] 约束类型(字段名) [references 主表(字段名)];

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
    id INT,
    stu_name VARCHAR(20),
    gender BIT(1),
    seat INT,
    age INT,
    major_id INT
);

alter table stuinfo modify column stu_name varchar(20) not null;
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#所有支持列级约束和表级约束写法的约束，都有两种写法
#添加主键约束 (因为主键约束支持表级和列级)
ALTER TABLE stuinfo MODIFY COLUMN id int PRIMARY KEY;
ALTER TABLE stuinfo ADD PRIMARY KEY(id);
#添加唯一约束
alter table stuinfo modify column seat int unique;
alter table stuinfo add unique(seat);
#添加外键约束
ALTER TABLE stuinfo ADD FOREIGN KEY(major_id) REFERENCES major(id);

修改表时删除约束

反过来想，再改一次，改的时候不写约束不就相当于删了吗？

还有一种删除方法 (用于删除表级约束)

alter table stuinfo drop primary key; #主键可以不指定删除的约束名，因为主键在一张表中只有一个
#删除外键约束
alter table stuinfo drop foreign key 外键约束名; 
#注意是约束名，而不是约束类型
alter table stuinfo drop index 约束名;

列级约束和表级约束的区别

位置 支持的约束类型 是否可以起约束名
列级约束 列的后面 语法都支持，但外键没有效果 不可以
表级约束 所有列的下面 默认和非空不支持，其它都支持 可以

标识列

又称为自增长列，可以不用在插入时指定值，在插入数据时系统会提供默认值

自增长列的关键字：auto_increment

创建表时设置自增长列

DROP TABLE IF EXISTS test;
CREATE TABLE test(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20)
);

#多次插入并不会报错，id 列自增长。但是如果前面不指定插入的列的话就必须匹配前面的列
INSERT INTO test VALUES(NULL, "zzz0");
#或者不指定自增长列，自增长列同样会插入值
INSERT INTO test SET NAME="2222";

自增长列的步长和起始值可以更改( MySQL 中虽然可以设置起始值，但是没有效果。因此基本上只支持设置步长)

使用命令查看与自增长关键字有关的设置

show variables like "%auto_increment%";

约束 - 图6

第一行为步长，第二行为起始值

使用命令设置步长

SET auto_increment_increment = 3;

再插入几个数据

约束 - 图7

关于不允许设置起始值，其实可以在表为空的时候，插入第一个数据时就指定插入时的自增长列的值，这样就变相的指定了自增长列的起始值

Q：自增长列必须和主键搭配吗？

A：不一定，但是自增长列必须和 key 搭配 ( 主键、唯一、外键和自定义 )

Q：一个表中可以有多个自增长列吗？

A：不行，只能有一个自增长列

Q：自增长列的数据类型有没有限制？

A：只支持数值型