简述:
概念:
约束是作用于表中字段上的规则,用于限制存储在表中的
数据目的:
保证数据库中数据的正确、有效性和完整性。
分类
约束 | 描述 | 关键字 |
---|---|---|
非空约束 | 限制该字段的数据不能为 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 '员工表'
解释:
- id:设置为主键 (
PRIMARY KEY
),并且自动递增 (AUTO_INCREMENT
)。 - name:设置为
VARCHAR(10)
,并且不能为空 (NOT NULL
),同时要求唯一 (UNIQUE
)。 - age:设置为
int
类型,并添加检查约束 (CHECK
),保证年龄大于 18 且不超过 80。 - status:设置为
CHAR(1)
,如果插入时未指定值,则默认为'1'
。 - 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
:父表中的对应主键或唯一键列。
创建外键约束的步骤:
- 创建父表:父表是主键所在的表,外键将参照它的主键。
- 创建子表:子表包含外键,该外键必须参照父表的主键或唯一键。
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;