七、MySql8常见约束

约束的概念

1、主键PRIMARY KEY

作用：DBMS为了方查找数据库，设计的一个规则
主键约束单列，非空而且唯一。

特点：
一张表里面主键是唯一的；
主键可以用来唯一的标识一行数据，主键不能为空，
联合主键：表中字段不能作为唯一标识每一行数据时，选取表中两个或者多个字段作为联合主键,
联合主键的特点：如果去掉联合主键某个字段，剩下的字段任然可以构成联合主键，那么说明之前的联合主键是错误的

创建表的时候设置主键：

方式一：

create table class(
    -> id int(11) primary key,
    -> name varchar(12)
    -> );

方式二：

create table class1(
    -> id int(11),
    -> name varchar(12),
    -> primary key(id)
    -> );

添加多列主键(联合主键)

-- 创建联合主键
create table user1(
    -> name char(16),
    -> gender char(10),
    -> birthday date,
    -> city varchar(16),
    -> primary key(name,city)
    -> );

通过修改表结构添加主键

-- 在修改表时添加主键
alter table user add primary key(id);

删除主键约束

-- 删除主键：
alter table user drop primary key;

1.1、强制删除主键约束

如果主键的值被外键引用，还是要删除主键，可以使用如下的方法:

删除父表中的行，同时也删除子表中的行

ON DELETE CASCADE

-- 将子表中的外键约束修改为 ON DELETE CASCADE
ALTER TABLE 子表名 -- 删除旧的外键约束
    DROP FOREIGN KEY 外键名,
    -- 如果外键约束有名字，则需要指定，否则可以省略
    -- 创建新的外键约束，并指定 ON DELETE CASCADE 规则
ADD CONSTRAINT 外键名 FOREIGN KEY (子表外键列) REFERENCES 主表名称(主表主键列名) 
ON DELETE CASCADE;

将外键设为空值

ON DELETE SET NULL

-- 删除当前的外键约束（如果已经存在）
ALTER TABLE 子表
DROP FOREIGN KEY 外键名;

-- 添加一个新的外键约束，并设置 ON DELETE SET NULL
ALTER TABLE 子表
ADD CONSTRAINT 外键名 
FOREIGN KEY (子表外键列) REFERENCES 主表(主表主键列)
ON DELETE SET NULL;

2、主键自增 AUTO_INCREMENT

通过auto_increment设置主键自增

特点：

和主键结合使用，自增字段的数据类型是整数类型,
自增的数据开始值是1，每增加一行数据，自增1

指定自增字段初始值

在创建表时设置自增

添加自增

// 添加自增
create table tb_dept10(
id INT(11) PRIMARY key AUTO_INCREMENT,
name VARCHAR(25),
location VARCHAR(50)
);

设置自增默认值

// 设置自增默认值
CREATE TABLE tb_dept11(
id INT(11) PRIMARY key auto_increment,
name VARCHAR(25),
location VARCHAR(50)
)auto_increment=100;

在修改表时设置自增

-- 在修改表时设置自增
ALTER TABLE tb_dept12 CHANGE COLUMN id id INT(11) auto_increment;

修改表时指定初始值

-- 在修改表时设置自增
ALTER TABLE tb_dept12 auto_increment=100;

删除自增

// 删除自增
ALTER TABLE tb_dept10 MODIFY id int(11);

delete和truncate对自增长的影响

delete是只删除表的数据，自增长约束，是从断点处开始；
truncate类似于drop table完全删除表，然后再创建该表，自增长约束，是从1开始。

3、非空约束（NOT NULL）

特点：
一张表中可以设置多个字段非空，主键默认非空

添加非空约束

方式1：创建表是指定非空约束

-- 新建表时设置
CREATE TABLE tb_dept4(
  id INT(11) NOT NULL,
  name VARCHAR(35),
  location VARCHAR(50)
);

方式2:创建表后指定非空约束

-- 修改表时设置
ALTER TABLE tb_dept3
MODIFY name VARCHAR(34) NOT NULL;

删除非空约束

4、唯一约束UNIQUE

特点：
主键在一个表里面只能有一个
但是唯一性约束可以有多个
设置了唯一性约束的字段表中有且只能有一个空值

指定唯一约束

方式1:创建表时指定唯一约束

在MYSQL中NULL和任何值都不相同，甚至和NULL自己都不相同。

-- 方法1: 创建表时指定
-- <字段名> <数据类型> unique
CREATE table 表名(
    id int,
    name varchar(20),
    phone_number varchar(20) unique  -- 指定唯一约束
);

-- 创建表的时候设置唯一性约束
CREATE TABLE tb_dept1(
    id INT(11) PRIMARY KEY,
    name VARCHAR(25) UNIQUE,  -- 唯一约束
    location VARCHAR(50)
);

方式2:创建表之后指定唯一约束

-- 方法2: 创建表之后指定
-- 修改表的时候添加唯一性约束
ALTER TABLE tb_dept2
ADD UNIQUE(要唯一约束的列名);

为约束起别名：

# 创建表之后指定：为约束起别名：
alter table 表名 add constraint 约束名 unique(要唯一约束的列名);

-- 起别名：
ALTER TABLE tb_dept2
ADD CONSTRAINT 约束的别名 UNIQUE(要唯一约束的列名);

删除唯一约束

-- 删除唯一性约束
ALTER TABLE tb_dept2 DROP INDEX unique_pn;

5、默认约束DEFAULT ‘Beijing’

特点：
在插入数据时，如果不写入就使用默认值，如果写入就使用新值

指定默认约束

创建时指定默认约束

CREATE TABLE 表名(
    id int,
    name varchar(20),
    address varchar(20) default '北京'  --指定默认约束
);

-- 新建表时创建
CREATE TABLE tb_dept3(
  id INT(11) PRIMARY KEY,
  name VARCHAR(25),
  location VARCHAR(50) DEFAULT 'shanghai'  --指定默认约束
);

创建表之后指定默认约束

-- 创建表之后指定默认约束
-- ALTER TABLE 表名 modify 要设置默认约束的列名 数据类型 default 默认值；
ALTER TABLE 表名
modify 要设置默认约束的列名 varchar(20) default '默认值';

-- 修改表时创建
ALTER TABLE tb_dept1
MODIFY address VARCHAR(25) DEFAULT 'beijing';

删除默认约束

-- 方法1:删除默认约束
ALTER TABLE 表名 change column 列名 数据类型 default null;

-- 方法2:修改字段名为的默认约束为null
ALTER TABLE 表名
modify 列名 数据类型 default null;

6、零填充约束(zerofill)

添加零填充约束

-- 创建表时添加零填充约束
CREATE TABLE 表名(
    id int zerofill -- 零填充约束
    name 数据类型
);

删除零填充约束

-- 零填充约束
-- ALTER TABLE 表名 modify 零填充约束的字段 int;
ALTER TABLE 表名
modify id int;

7、自关联约束

-- 创建表，并建立自关联约束
create table t_sanguo(
    eit INT PRIMARY KEY,
    ename VARCHAR(20),
    manager_id INT,
    ---外键列
    FOREIGN KEY(manager_id) REFERENCES t_sanguo(eid) --添加自关联约束
);

8、外键约束

https://blog.csdn.net/omaidb/article/details/129985930

9、约束检查

预期结果：

实际是可以正常插入check的数据，所以：MySQL8没有内置的 CHECK 约束检查。

10、查看指定库和表的所有约束名称和类型

-- 从information_schema数据库中的table_constraints表中获取指定库（schema）和表（table）的所有约束（constraints）名称和类型
SELECT CONSTRAINT_NAME,
    CONSTRAINT_TYPE
FROM information_schema.table_constraints
WHERE lower(table_schema) = '库名'
    AND lower(table_name) = '表名';