MySQL之约束

简述：

概念:

约束是作用于表中字段上的规则，用于限制存储在表中的

数据目的:

保证数据库中数据的正确、有效性和完整性。

分类

约束	描述	关键字
非空约束	限制该字段的数据不能为 NULL	NOT NULL
唯一约束	保证该字段的所有数据都是唯一的，不重复的	UNIQUE
主键约束	主键是数据表中唯一标识，要求字段值非空且唯一	PRIMARY KEY
默认约束	在保存数据时，如果未指定该字段的值，则采用默认值	DEFAULT
检查约束	保证字段值满足某一条件（从 MySQL 8.0.16 版本之后开始支持）	CHECK
外键约束	用来让两张表的数据之间建立联系，保证数据的完整性和一致性	FOREIGN KEY

注意:约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束 

如何使用？

字段名	字段含义	字段类型	约束条件	约束关键字
id	ID 唯一标识	int	主键，且自动增长	PRIMARY KEY, AUTO_INCREMENT
name	姓名	varchar(10)	不为空，并且唯一	NOT NULL, UNIQUE
age	年龄	int	大于 0，并且小于等于 120	CHECK
status	状态	char(1)	如果没有指定该值，默认值为 1	DEFAULT
gender	性别	char(1)	无约束条件	无

create table emp2(
    id int primary key auto_increment comment '主键',
    name varchar(10) not null unique comment '姓名',
    age int check ( age >= 18 && age <= 80 ) comment '年龄',
    status char(1) default '1' comment '状态',
    gender char(1) comment '性别'
)comment '员工表'

解释：

1. id：设置为主键 (PRIMARY KEY)，并且自动递增 (AUTO_INCREMENT)。
2. name：设置为 VARCHAR(10)，并且不能为空 (NOT NULL)，同时要求唯一 (UNIQUE)。
3. age：设置为 int 类型，并添加检查约束 (CHECK)，保证年龄大于 18 且不超过 80。
4. status：设置为 CHAR(1)，如果插入时未指定值，则默认为 '1'。
5. gender：设置为 CHAR(1)，无其他额外的约束条件。

该表设计用于存储员工的 ID、姓名、年龄、状态和性别，并对这些字段进行了约束，以确保数据的完整性和准确性。

 外键约束

在 MySQL 中，外键约束用于维护两个表之间的关系，并确保参照完整性（即外键字段的值必须存在于另一个表的主键或唯一键字段中）。外键约束通常用于建立父表和子表之间的关系，确保数据的一致性。

如何建立外键约束？

外键约束语法：

CONSTRAINT constraint_name FOREIGN KEY (foreign_key_column) REFERENCES parent_table(parent_key_column)

• constraint_name：外键约束的名称（可选）。
• foreign_key_column：子表中的外键列。
• parent_table：父表名称。
• parent_key_column：父表中的对应主键或唯一键列。

创建外键约束的步骤：

1. 创建父表：父表是主键所在的表，外键将参照它的主键。
2. 创建子表：子表包含外键，该外键必须参照父表的主键或唯一键。

create table dept(
    id int primary key auto_increment comment  'ID',
    name varchar(50) not null comment '部门名称'
) comment '部门';
INSERT INTo dept(id, name) VALUES(1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经办');

create table emp2(
    id int primary key auto_increment comment '主键',
    name varchar(10) not null unique comment '姓名',
    age int check ( age >= 18 && age <= 80 ) comment '年龄',
    status char(1) default '1' comment '状态',
    gender char(1) comment '性别',
    dept_id int comment '部门ID'
)comment '员工表';

insert into emp2(name, age,gender,dept_id) values
('Tom1',18,'男',3),('Tom4',20,'男',1),
('Tom2',28,'女',1),('Tom5',25,'男',5),
('Tom3',19,'男',4),('Tom6',40,'女',1),
('小李子',18,'男',1),('小智障',30,'女',2);

#建立外键约束
alter table emp2 add constraint fk_emp2_id foreign key (dept_id) references dept(id);

外键的删除以及更新

类型：

行为	说明
NO ACTION	当在父表中删除/更新对应记录时，先检查该记录是否有关联的外键。如果有则不允许删除/更新（与 RESTRICT 一致）。
RESTRICT	当在父表中删除/更新对应记录时，先检查该记录是否有关联的外键。如果有则不允许删除/更新（与 NO ACTION 一致）。
CASCADE	当在父表中删除/更新对应记录时，所有相关的子表记录也会被删除/更新。
SET NULL	当在父表中删除对应记录时，相关子表中的外键值将被设置为 NULL（前提是该外键列允许 NULL 值）。
SET DEFAULT	当父表有变化时，子表的外键列会被设置成一个默认的值（注意：InnoDB 存储引擎不支持 SET DEFAULT 行为）。

 外键约束语法：

CONSTRAINT constraint_name FOREIGN KEY (foreign_key_column) REFERENCES parent_table(parent_key_column)

• constraint_name：外键约束的名称（可选）。
• foreign_key_column：子表中的外键列。
• parent_table：父表名称。
• parent_key_column：父表中的对应主键或唯一键列。

1. 删除外键约束： 

ALTER TABLE employees DROP FOREIGN KEY fk_department;

2. 添加外键约束（如果表已经创建）： 

ALTER TABLE employees
ADD CONSTRAINT fk_department
FOREIGN KEY (department_id) REFERENCES departments(department_id);

ON DELETE 和 ON UPDATE 选项：

可以在创建外键时指定外键的删除和更新行为。

• ON DELETE CASCADE：当父表中的记录被删除时，自动删除子表中与该记录相关的记录。
• ON UPDATE CASCADE：当父表中的记录被更新时，自动更新子表中与该记录相关的记录。

例子：

create table dept1(
    id int primary key comment  'ID',
    name varchar(50) not null comment '部门名称'
) comment '部门';
INSERT INTo dept1(id, name) VALUES(1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经办');

create table emp2(
    id int primary key auto_increment comment '主键',
    name varchar(10) not null unique comment '姓名',
    age int check ( age >= 18 && age <= 80 ) comment '年龄',
    status char(1) default '1' comment '状态',
    gender char(1) comment '性别',
    dept_id int comment '部门ID'
)comment '员工表';


insert into emp2(name, age,gender,dept_id) values
('Tom1',18,'男',3),('Tom4',20,'男',1),
('Tom2',28,'女',1),('Tom5',25,'男',5),
('Tom3',19,'男',4),('Tom6',40,'女',1),
('小李子',18,'男',1),('小智障',30,'女',2);

alter table emp2 add constraint fk_emp2_dept_id foreign key (dept_id) references dept(id) on update cascade on delete  cascade;

# 查看有哪些外键
SHOW CREATE TABLE emp2;

# 删除外键约束
ALTER TABLE emp2 DROP FOREIGN KEY fk_emp2_id;