mysql的六种约束

最新推荐文章于 2024-10-02 12:42:27 发布

什么？带我一个

最新推荐文章于 2024-10-02 12:42:27 发布

阅读量60

点赞数

分类专栏： mysql 数据库文章标签： mysql 数据库

本文链接：https://blog.csdn.net/m0_68154641/article/details/131989459

版权

数据库同时被 2 个专栏收录

12 篇文章 0 订阅

订阅专栏

mysql

11 篇文章 0 订阅

订阅专栏

#mysql的六种约束

1.分类：主键约束，自增长约束，唯一约束，默认约束，外键约束和检查约束；

查看约束语法

show create table_name

show create table class;

1.主键约束PK（primary key）

语法结构及说明

设置表的主键时有多种方法（创建列名为name和stu_no的class表为例）

1.创建表时

#未设置主键的表
create table class(id int,name varchar(10),stu_no varchar(20));
#设置主键为name
 create table class_a(id int primary key ,name varchar(10) comment '姓名',stu_no varchar(20) comment '学号');
 create table class_b(name varchar(10) not null,
        stu_no varchar(20),primary key(name));
 create table class_c(name varchar(10) not null,
        stu_no varchar(20),constraint pk primary key(name));

2.表创建完成后添加主键

语法结构

ALTER TABLE 表名 MODIFY 列名 列类型 PRIMARY KEY; -- 修改列的属性时添加主键约束
ALTER TABLE 表名 ADD PRIMARY KEY();  # ()内表示主键约束字段，一般为列名
ALTER TABLE 表名 ADD CONSTRAINT pk_name PRIMARY KEY();  #添加主键约束，同时给主键约束命名

alter table class modify id int primary key; 
alter table class add primary key(id);
alter table class add constraint pk primary key(id);  -- a,b表已有主键约束，不能再添加，一个表只能有一个主键约束

删除主键

语法结构

alter table 表名 drop primary key;

alter table class drop primary key;

Notes：

主键约束的列不允许有重复的值
主键约束的列不允许有NULL值
一个表只能有一个主键约束

自增长约束AI（auto_increment）

仍旧以class表为例

1.创建表时

-- 创建班级表时，id为自增长主键
create table class_d(id int primary key auto_increment,
       name varchar(10),stu_no varchar(20));  
-- 创建班级表时，设置id为自增长主键，设置自增长列初始值           
create table class_e(id int primary key auto_increment,
       name varchar(10),stu_no varchar(20)) auto_increment=3;

2.表创建完成后添加自增长约束

语法结构

ALTER TABLE 表名 AUTO_INCREMENT = ; -- 给表添加自增长约束且初始值为
ALTER TABLE 表名称 MODIFY COLUMN 列名 列属性 AUTO_INCREMENT;  -- 修改表时添加自增长约束

alter table class_d auto_increment = 3;   -- 给表添加自增长约束且初始值为3
alter table class_e modify column id int auto_increment;  --

3.删除自增长约束

ALTER TABLE 表名称 MODIFY COLUMN id INT;

alter table class modify column id int;

Notes：

自增长列不一定和主键key搭配，也可以是UNIQUE（唯一），外键等
一个表最多有一个自增长列
标识列的类型只能是数值型（int,float,double）
可以通过SET auto_increment_increment = 3; 设置步长（全局）
可以通过插入行时手动插入自增长列值设置起始值

唯一约束UQ（UNIQUE）

对于非主键列中的值也要求唯一性时，就需要唯一约束UNIQUE,以下仍以class表为例

1.创建表时

-- 创建班级表时，设置学号为唯一约束
create table class_f(id int not null,name varchar(10),stu_no varchar(20) unique);
-- 添加唯一约束，同时给唯一约束命名
create table class_g(id int not null,name varchar(10),
             stu_no varchar(20),constraint uq_name unique(stu_no));

2.表创建结束后添加唯一主键

语法结构

ALTER TABLE 表名 ADD UNIQUE(列名);  
-- 加唯一约束，同时给约束命名
ALTER TABLE 表名 ADD CONSTRAINT uq_name UNIQUE(列名);

alter table class_f ADD unique(stu_no);
alter table class_g add unique(stu_no);

3.删除唯一约束

ALTER TABLE tb_name DROP CONSTRAINT constraint_name;  -- 按照约束命名；删除
[alter table 表名称 drop index 设置唯一时的名称;] -- 删索引

alter table class_g drop constraint uq_name;  -- f表没有为约束命名
alter table class_g drop index uq_name;

Notes：

唯一约束不允许有重复值
唯一约束的列允许有多个NULL值
一张表可以有多个唯一约束
约束默认名称为其列名
唯一约束创建后会自动创建一个唯一索引index

默认约束

1.创建表时

create table class_h (id int default null ,#默认约束
       name varchar(10),stu_no varchar(20) unique);

2.表创建结束后添加约束

alter table 表名称 modify column 列名 列类型 default 默认值;

alter table class_h modify column stu_no int default 001;

3.删除默认约束

alter table 表名称 modify column 列名 列类型; -- 将默认值改为 NULL
ALTER TABLE tb_name ALTER col_name DROP DEFAULT; -- 删除了默认值，新增时必须有值

alter table class_h modify column stu_no varchar(20);
alter table class_h alter column drop default;

Notes：

创建表时，不写默认值都默认 NULL （在无非空约束的情况下）
默认约束能和主键约束可以同时存在
默认约束不能和自增长约束 AUTO_INCREMENT 同时使用

非空约束（NOT NULL）

1.创建表时

create table class_i(id int not null,# 非空约束
       name varchar(10),stu_no varchar(20));

2.表创建结束后添加约束

语法结构

alter table 表名称 modify column 列名 列类型 not null;

alter table class_i modify column stu_no varchar(20);

3.删除约束

语法结构

alter table 表名称 modify column 列名 列类型 [null];

alter table class_i modify column stu_no varchar(20) null;
-- 一个一个删，同时删会报错

Notes：

NOT NULL ：非空，用于保证该字段的值不能为空。
使用了非空约束的字段，若用户添加数据时没有添加指定值，系统就会报错

外键约束FK(FOREIGN KEY Constraints)

1.创建表时

create table class_j(age varchar(10), gender varchar(2),stu_id int,
       constraint fk_stu_id foreign key(stu_id) references class_k(id));  #主表 
create table class_k(id int primary key, # 从表
       name varchar(10));

2.表创建结束后添加约束

ALTER TABLE 从表 ADD CONSTRAINT 外键命名 FOREIGN KEY (从表中所引用主表的列) REFERENCES 主表(主表中被引用的列);   -- 外键命名默认为fk_`从表引用主表的列名`

alter table class_k add constraint fk_id foreign key(id) references class_j(stu_id);

3.删除约束

 -- 按照约束名删除
ALTER TABLE tb_name DROP CONSTRAINT constraint_name;  
alter table 表名称 drop foreign key fk_name; 
-- 通过删除索引删除外键
alter table 表名 drop index fk_name;

alter table class_k drop constraint fk_id;
alter table class_k drop foreign key fk_id;  
alter table class_k drop index fk_id;

级联更新、级联删除

1.级联更新： on update CASCADE, ON UPDATE SET NULL

区别： on update CASCADE：之后更新主表数据时，从表数据会一起更改

ON UPDATE SET NULL ：之后更新主表时，从表数据更新为NULL值

2.级联删除:

区别：on delete CASCADE：之后删除主表数据时，从表数据会一起删除

ON DELETE SET NULL ：之后删除主表时，从表数据删除为NULL值

3.语法结构

# 级联更新--设置外键并级联更新
ALTER TABLE 从表 ADD CONSTRAINT fk_name FOREIGN KEY(从表要设置外键的列) REFERENCES 主表(被引用的列) ON UPDATE CASCADE;
-- 之后更新主表数据时，从表数据会一起更改
# 级联更新--设置外键并级联更新为空值
ALTER TABLE 从表 ADD CONSTRAINT fk_name FOREIGN KEY() REFERENCES 主表(被引用列) ON UPDATE SET NULL;
-- 之后更新主表时，从表数据更新为NULL值
# 级联删除
ALTER TABLE 从表 ADD CONSTRAINT fk_name FOREIGN KEY(要设置外键的列) REFERENCES 主表(被引用的列) ON UPDATE CASCADE;
-- 之后删除主表数据时，从表数据会一起删除
ALTER TABLE 从表 ADD CONSTRAINT fk_name FOREIGN KEY() REFERENCES 主表(被引用列) ON DELETE SET NULL;
-- 之后删除主表时，从表数据删除为NULL值

alter table class_k add constraint fk_id foreign key(id) references class_j(stu_id) on update cascade;  
alter table class_k add constraint fk_id foreign key(id) references class_j(stu_id) on delete cascade;   -- 设置外键并级联删除

Notes：

A表中列的值来自于另外一张表B的主键或唯一键的列称为外键FK，将被引用值的表称为

主表或父表，将引用值得表称为从表或子表；
一般是从表的外键引用主表的主键
在从表中添加或修改被引用的主表的值时，这个值值必须是主表或被引主键字段中的

存在值或者 NULL
删除从表数据可以直接删除
删除主表数据时，会先检查被删除数据在从表中有没有对此数据的关联（引用），如果有不能直接删除。
不能直接删除外键约束，只能分步删除或者通过删除外键名或删除外键索引删除
级联更新：更新主键时，外键也随之更新；
级联删除：删除主键时，外键也随之删除；
添加记录时，先添加主表记录，再添加从表记录
删除记录时，先删除从表记录，再删除主表记录
主表被从表引用的键必须是主键或唯一键

检查约束CHECK

check 检查约束，在数据添加或修改时保证数据的有效性。符合表达式的才会正确执行。

1.创建表时

create table class_l (id int ,name varchar(10),
                      age double check(age>=18),stu_no varchar(20));           
create table class_m (id int ,
                      name varchar(10),stu_no varchar(20),
                      age double check(age>=18));

2.表创建后添加约束

语法结构

alter table 表名 modify 列名 列类型 check (条件);  -- 修改表时添加约束 
alter table 表名 add constraint chk_name check(条件); -- ()增加条件约束的列及其条件

alter table class_l modify stu_no varchar(20) check (stu_no >0);
alter table class_m add constraint chk_name check(stu_no>0);

3.删除约束

ALTER TABLE tb_name DROP CONSTRAINT chk_name;
alter table tb_name drop check chk_name;

alter table class_l drop constraint class_l_chk_2; -- class_l_chk_2 为默认命名
alter table class_m drop check class_m_chk_1; 
alter table class_k drop index class_m_chk_1;  -- 报错