MySQL约束

约束

约束(constraint)概述

为什么要约束

为了保证数据完整性

什么是约束

对表中字段的（强制）限制

约束的分类

角度一：字段个数

单类约束，多列约束

角度二：约束的作用范围 / 定义位置

列级约束：将此约束声明在字段的后面

表级约束：在表中所有字段声明完，在所有字段的后面声明的约束

约束的作用（功能）

not null (非空约束)

unique (唯一性约束)

primary key (主键约束)

foreign key (外键约束)

check (检查约束)

default (默认值约束)

如何添加约束

CREATE TABLE 添加约束
ALTER TABLE 增加、删除约束

如何查看表中约束

select * from information_schema.table_constraints
where table_name ='表的名字';

NOT NULL (非空约束)

限定某个字段 / 某列的值不能为空

特点

默认所有类型的值都可以是NULL，包括INT,FLOAT等
非空约束只能出现在表对象的列上，只能某个列单独非空，不能组合非空（只能列级约束）
一个表可以有很多列都分别设置非空
字符串' '不等于NULL；0也不等于NULL

添加非空约束

CREATE TABLE 添加约束

CREATE TABLE emp2(           #建议大写
id int not null,
name varchar(15) not null,
email varchar(25),
salary decimal(10,2)
);
desc emp2;

#正确
insert into emp2(id,name,email,salary)
values(1,'Tom','tom@1126.com',3400);
#错误
insert into emp2(id,name,email,salary)
values(null,'Tom','tom@1126.com',3400);
insert into emp2(id,name,email,salary)
values(1,null,'tom@1126.com',3400);

ALTER TABLE 增加约束

#增加
alter table emp2
modify email varchar(25) not null;

删除非空约束

#删除
alter table emp2
modify email varchar(25) null;

注：若在之前已经让要修改的字段中有null，则要先将null去除

UNIQUE (唯一性约束)

用来限制某个字段 / 某列的值不能重复

特点

同一个表可以有多个约束
唯一约束是以某一列的值唯一，也可以是多个列组合的值唯一
唯一性约束允许列值为空(可以多次添加null)
在创建唯一性约束时，如果不给唯一约束命名，则默认和列同名（若是多列，则和第一个列名相同）

添加唯一约束

单列

CREATE TABLE 添加约束

CREATE TABLE emp3(
id int unique,
name varchar(15),
email varchar(25),
salary decimal(10,2),
#表级约束
constraint uk_emp3_email unique (email)
           #约束名的名字
);
desc emp3;

#正确
insert into emp3(id,name,email,salary)
values(1,'Tom','tom@126.com',4500);
insert into emp3(id,name,email,salary)
values(2,'Tom1',null,4700);
insert into emp3(id,name,email,salary)
values(3,'Tom2',null,4600);
#错误
insert into emp3(id,name,email,salary)
values(1,'Tom3','to1@126.com',4500); #id重复

ALTER TABLE 增加约束

#添加
方式一：
alter table emp3
add constraint uk_emp3_sal unique (salary);#和表级约束差一样
                #约束的名字
方式二：
alter table emp3
modify name varchar(15) unique;#和列级约束一样

多列（复合型唯一性约束）

约束后的字段中的数据，不能都是一样，只要有一点不同，就可以执行

CREATE TABLE USER(
id int ,
name varchar(15),
·password· varchar(25),
#表级约束
constraint uk_USER_name_pwd unique (name,`password`)
           #约束名的名字      
);
#正确
insert into USER
values (1,'Tom','abc');
insert into USER
values (1,'Tom','abcd');
#错误
insert into USER
values (1,'Tom','abc'); #

删除唯一性约束

添加唯一约束的列上会自动创建唯一索引
删除唯一约束只能通过删除唯一索引的方式删除
删除时需要指明唯一索引名，唯一索引名和唯一约束名一样（见唯一约束的特点）

#删除
alter table emp3
drop index 索引名；

PRIMARY KEY（主键约束）

用来唯一标识表中的一行记录（区分不同的行）

特点

主键约束相当于非空约束+唯一约束，主键约束列不可以重复和出现空值
一个表最多一个主键约束（可以列级，也可以表级）
如果是多列约束，这些列也是不能有空值的，且组合的值不可以重复
MySQL的主键名是PRIMARY, 就算自己命名了主键名也没用
创建主键约束时，系统会建立对应的主键索引（主键查询，效率高）
不要修改主键字段的值（主键是数据记录的唯一标识），可能会破坏数据的完整性

添加主键约束

单列

CREATE TABLE 添加约束

CREATE TABLE emp4(
id int primary key,#列级约束
name varchar(15),
email varchar(25),
salary decimal(10,2)
);

CREATE TABLE emp5(
id int,
name varchar(15),
email varchar(25),
salary decimal(10,2),
constraint pk_emp5_id primary key (id)#表级约束，但是没有必要起名字
);

#正确
insert into emp4(id,name,email,salary)
values(1,'Tom','tom@1126.com',3400);
#错误
insert into emp4(id,name,email,salary)
values(1,'Tom','tom@1126.com',3400); #id重复
insert into emp4(id,name,email,salary)
values(null,'Tom','tom@1126.com',3400); #出现null值

ALTER TABLE 增加约束

ALTER TABLE emp6
add primary key (id);

多列

CREATE TABLE USER1(
id int ,
name varchar(15),
·password· varchar(25),
primary key (name,`password`)
);

删除主键约束(实际开发中不会删除)

alter table emp6
drop primary key;

自增列：AUTO_INCREMENT

让某个字段的值自增

特点和要求

一个表只能有一个自增长列
自增列必须是键列（主键列，唯一键列）
自增约束的列类型必须是整型
如果自增列指定了0和null，会在当前最大值上自增；如果自增列手动指定了具体值，直接赋值具体值

注：在实际开发中如果主键作用的字段有auto_increment，则在添加数据是不需要对该字段去赋值

添加自增长列

CREATE TABLE 添加约束

CREATE TABLE emp7(
id int primary key auto_increment ,
name varchar(15)
);

insert into emp7(name)
values('Tom'); #执行一次，id+1不需要赋值

ALTER TABLE 增加约束（一般不会使用）

alter table emp8 #id已经是主键
modify id int auto_increment;

删除自增列

alter table emp8
modify id int;

MySQL8.0新特性——自增量的持久性

若删除一行记录，重启服务器，再执行添加语句，则该自增列的值=刚才删除的行的自增列的数+1

FOREIGN KEY(外键约束)

限定某个表的某个字段的引用完整性

不可以跨存储引擎

主表和从表 / 父表和子表

主表（父表）：被引用的表，被参与的表
从表（子表）：引用别人的表，参考别人的表

eg:学生表和课程表是主表，选课表是从表

特点：

从表的外键列，必须是主表的主键列或是唯一约束列（被参考的数要是唯一的）
创建外键约束时，如果不起名字，默认名不是列名，而是自动产生一个外键名
创建外键时，要先创建主表，再创建从表
删除时，先删除从表（或是外键约束），再删除主表（要是删除其中数据道理也是一样）
在从表中建立外键约束，一个表也可建立多个外键约束
从表的外键列和主表的参照列名字可以不同，但是数据类型必须是一样的
当创建外键约束时，系统默认在所在的列上建立对应的普通索引，但是索引名是列名，不是外键的约束名
删除外键后，必须手动删除对应的索引

添加外键约束

CREATE TABLE 添加约束

#先创建主表
CREATE TABLE dept1(
dept_id int primary key,#必须给要关联的列添加主键或是唯一约束
dept_name varchar(15)
);
#创建从表
CREATE TABLE emp01(
emp01_id int primary key auto_increment,
emp01_name varchar(15),
department_id int,
#表级约束
constraint fk_emp01_id foreign key(department_id) references dept1(dept_id)
);

#演示外键效果
insert into dept1
values (10,'IT');#先创建主表的数据10号部门

INSERT INTO emp01
values (1001,'Tom',10);
#若是删除或是更改当前主表中的10号部门，是不可行的

ALTER TABLE 增加约束

#主表
CREATE TABLE dept2(
dept_id int primary key,
dept_name varchar(15)
);
#从表
CREATE TABLE emp02(
emp01_id int primary key auto_increment,
emp01_name varchar(15),
department_id int,
);

alter table emp02
add constraint fk_emp02_deot_id foreign key (department_id) references dept2(dept_id);

约束等级

最好采用ON UPDATE CASADE ON DELETE RESTRICT 的方式，声明位置在约束条件的后面

删除约束

先查看约束名和删除外键约束

select * from information_schema.table_constraints
where table_name ='表的名字';

alter table 从表名 
drop foreign key 约束名;

查看索引名，并删除（只能手动）

show index from 表名;

alter table 从表名
drop index 索引名;

开发场景

外键会让操作受到限制

不建议使用外键和级联，一切外键概念必须在应用层解决（经典白学）

CHECK (检查约束)

检查某个字段的值是否符合相应的要求，一般是指值的范围

MySQL5.7不支持CHECK约束

CREATE TABLE emp10(
id int,
name varchar(15),
salary decimal(10,2) check (salary > 2000)  
);

insert into emp10
values (1,'Tom',3000);#成功
insert into emp10
values (2,'Tom1',1500);#失败

DEFAULT(默认值约束)

给某个字段 / 某列指定一个默认值，一旦指定以后，在插入数据时，如果此字段没有显示赋值，则赋值为默认值

添加默认值约束

CREATE TABLE 添加约束

CREATE TABLE emp11(
id int,
name varchar(15),
salary decimal(10,2) default 2000
);

insert into emp11
values (1,'Tom',3000);#成功
insert into emp11(id,name)
values (2,'Tom1');#默认添加2000的工资

ALTER TABLE 增加约束

ALTER TABLE emp12
modify salary varchar(8,2) default 2500;

删除默认值约束

ALTER TABLE emp12
modify salary varchar(8,2)；#直接去掉（NOT NULL差不多）

posted @ 2022-03-14 21:50 T,a,o 阅读(131) 评论(0) 编辑收藏举报

刷新页面返回顶部

MySQL约束

约束

约束(constraint)概述

为什么要约束

什么是约束

约束的分类

如何添加约束

如何查看表中约束

NOT NULL (非空约束)

特点

添加非空约束

删除非空约束

UNIQUE (唯一性约束)

特点

添加唯一约束

单列

多列（复合型唯一性约束）

删除唯一性约束

PRIMARY KEY（主键约束）

特点

添加主键约束

单列

多列

删除主键约束(实际开发中不会删除)

自增列：AUTO_INCREMENT

特点和要求

添加自增长列

删除自增列

MySQL8.0新特性——自增量的持久性

FOREIGN KEY(外键约束)

主表和从表 / 父表和子表

特点：

添加外键约束

约束等级

删除约束

开发场景

CHECK (检查约束)

DEFAULT(默认值约束)

添加默认值约束

删除默认值约束

公告