【MySQL】第十七部分 约束

【MySQL】第十七部分 约束

---

---

17. 约束

约束: 可以在创建表的时候规定约束,也可以在表创建之后添加,约束顾名思义就是限制条件

17.1 约束的分类

单列约束：每个约束只约束一列

多列约束：每个约束可约束多列数据

列级约束：只能作用在一个列上，跟在列的定义后面

表级约束：可以作用在多个列上，不与列一起，而是单独定义

17.2 非空约束

添加非空约束

# 1. 在创建表的时候添加非空约束
CREATE TABLE test1(
	id INT NOT NUll,
	name VARCHAR(10) NOT NULL,
	salary DECIMAL(10,2)
);


# 2. 表创建后添加非空约束
ALTER TABLE test1
MODIFY salary DECIMAL(10,2) NOT NULL;

删除非空约束

ALTER TABLE test1
MODIFY salary DECIMAL(10,2);

17.3 唯一性约束

知识点:

1. 同一个表可以有多个唯一约束
2. 唯一约束可以是某一个列的值唯一，也可以多个列组合的值唯一
3. 唯一性约束允许列值为空
4. 在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同
5. MySQL会给唯一约束的列上默认创建一个唯一索引
6. 唯一约束可以使用表级约束的写法,上述的非空约束是不可以的

添加唯一约束

# 表级约束写法:
create table 表名称(
	字段名  数据类型,
  [constraint 约束名] UNIQUE(字段名,字段名)
);

# 1. 在创建表的时候添加唯一约束
CREATE TABLE test2
(
	id INT UNIQUE, # (这种就是列级写法)
	name VARCHAR(10)
);


# 2. 在创建表的时候使用表级约束写法
CREATE TABLE test3
(
	id INT,
	name VARCHAR(10),
	CONSTRAINT id_uni UNIQUE(id) 
);

CREATE TABLE test4
(
	id INT,
	name VARCHAR(10),
	# 将id和name组合约束,表示只有当id和name都重复才会报错,否则都符合条件
	CONSTRAINT id_name_uni UNIQUE(id,name) # 多列约束
);

# 3.在建表后添加唯一约束
ALTER TABLE test4 
ADD UNIQUE(name);

# 4.在建表后添加唯一约束
ALTER TABLE test3
MODIFY name VARCHAR(10) UNIQUE;

删除唯一约束

上述说过因为添加唯一性约束的列上也会自动创建唯一索引,所以删除唯一约束只能通过删除唯一索引

# 通过该命令可以查看指定表中有哪些约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名'; 

# 也可以通过该命令查看表的索引
SHOW INDEX FROM 表名称;

ALTER TABLE test3
DROP INDEX id_uni

17.4 主键约束

主键约束相当于唯一约束和非空约束的组合，主键约束列不允许重复，也不允许出现空值, 注意:

1. 一个表当中最多只能由一个主键约束,建立主键约束可以在在列级创建,也可以在表级创建
2. 主键约束可以对应一列或者多列(和上述唯一约束中说的多列约束一样)
3. 主键约束不需要去取约束名,MySQL的主键名总是PRIMARY
4. 创建主键约束时, 系统默认会在所在的列或列组合上建立对应的主键索引,如果删除主键约束了，主键约束对应的索引就自动删除了。

添加主键约束

# 1. 创建表时添加主键约束
CREATE TABLE test1
(
	id INT PRIMARY KEY ,
	name VARCHAR(10)
);

# 2. 创建表时添加主键约束(表级写法)
CREATE TABLE test2
(
	id INT,
	name VARCHAR(10),
	PRIMARY KEY(id)
);


CREATE TABLE test3
(
	id INT,
	name VARCHAR(10),
	PRIMARY KEY(id,name)
);

# 3. 表创建后添加
ALTER TABLE test3
MODIFY id INT PRIMARY key;

# 4. 表创建后添加
ALTER TABLE test4
ADD PRIMARY KEY(id);

删除主键约束

ALTER TABLE test5
DROP PRIMARY KEY;

17.5 自增列约束

使用的要求:

1. 一个表中只能有一个自增列
2. 自增列约束必须是主键列或者唯一键列
3. 约束列的数据类型必须是整数类型
4. 如果自增列指定了 0 和 null，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值。

添加自增约束

# 1. 创建表时添加自增约束
CREATE TABLE test5 
(
	id INT PRIMARY KEY auto_increment,
	name VARCHAR(10)
);

INSERT INTO test5(name)
VALUES('a'),('b');

# 2. 建表后添加自增约束
ALTER TABLE test6
MODIFY id INT auto_increment;

删除自增约束

ALTER TABLE test6
MODIFY id INT;

17.6 外键约束

主表（父表）：被引用的表，被参考的表

从表（子表）：引用别人的表，参考别人的表

例如：员工表的员工所在部门这个字段的值要参考部门表：部门表是主表，员工表是从表。

注意点:

1. 如果想要指定外键约束的话,需要先创建主表,在创建从表
2. 从表的外键列,必须参考主表中的主键列或者唯一约束列
3. 从表外键列对应主表的主键列或唯一约束列,二者数据类型必须要一致
4. 删除表的时候先删除从表,再删除主表
5. 一个表可以建立多个外键约束

添加外键约束

# 语法:
create table 从表名称(
		字段1  数据类型  primary key,
    字段2  数据类型,
    [CONSTRAINT <外键约束名称>] FOREIGN KEY（从表的某个字段名) references 主表名 (被参考字段名)
);

ALTER TABLE 从表名 ADD [CONSTRAINT 约束名] FOREIGN KEY (从表的某个字段名) REFERENCES 主表名(被引用字段名);

# 1. 创建表时添加外键约束
CREATE TABLE dep
(
	d_id INT PRIMARY KEY,
	d_name VARCHAR(12)
);

CREATE TABLE emp
(
	e_id INT PRIMARY KEY,
	e_name VARCHAR(10),
	department_id INT,
	FOREIGN KEY (department_id) REFERENCES dep(d_id)
);


# 2. 建表后添加外键约束
ALTER TABLE emp
ADD FOREIGN KEY(department_id) REFERENCES dep(d_id) ;

删除外键约束

# 第一步先查看约束名和删除外键约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';

ALTER TABLE 从表名 DROP FOREIGN KEY 外键约束名;

# 第二步查看索引名和删除索引
SHOW INDEX FROM 表名称; #查看某个表的索引名

ALTER TABLE 从表名 DROP INDEX 索引名;

约束关系是针对双方的

• 添加了外键约束后，主表的修改和删除数据受约束
• 添加了外键约束后，从表的添加和修改数据受约束
• 在从表上建立外键，要求主表必须存在
• 删除主表时，要求从表从表先删除，或将从表中外键引用该主表的关系先删除

17.7 默认约束

给某个字段设置默认值,如果此字段没有显式赋值，则赋值为默认值。

添加默认约束

# 1. 创建表时添加默认约束
CREATE TABLE test1
(
	id INT PRIMARY KEY auto_increment,
	name VARCHAR(10),
	salary DECIMAL(10,2) NOT NULL DEFAULT 2000,
	gender CHAR DEFAULT '男'
);

# 2. 建表后添加默认约束
# 如果这个字段原来有非空约束，还想保留非空约束，那么在加默认值约束时，还得保留非空约束，否则非空约束就被删除了,切记!!!!!!
ALTER table test2
MODIFY gender char(5) DEFAULT 'male' NOT NULL;

删除默认约束

ALTER table test2
MODIFY gender char(5) NOT NULL;

17.8 check约束

MySQL 5.7 不支持

check约束对数据进行校验,看是否满足预设的条件

CREATE TABLE test1
(
	id INT PRIMARY KEY,
	name VARCHAR(10),
	salary DECIMAL(10,2) CHECK(salary > 3000)
);

---

总结

以上就是今天要讲的内容，希望对大家有所帮助！！！