SQL语法之约束

一、什么是约束

1、为了保证数据的一致性和完整性，SQL规范以约束的方式对表数据进行额外的条件限制。
2、约束是表级的强制规定
3、可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后也可以（通过 ALTER TABLE 语句）

二、常见的6种约束

有以下六种约束:

• NOT NULL 非空约束，规定某个字段不能为空

• UNIQUE 唯一约束，规定某个字段在整个表中是唯一的

• PRIMARY KEY 主键(非空且唯一)

• FOREIGN KEY 外键

• CHECK 检查约束

• DEFAULT 默认值

注意： MySQL不支持check约束，但可以使用check约束，而没有任何效果；

三、约束分类

1、根据约束数据列的限制，约束可分为：
1) 单列约束：每个约束只约束一列
2)多列约束：每个约束可约束多列数据

2、根据约束的作用范围，约束可分为：
1)列级约束只能作用在一个列上，跟在列的定义后面
2)表级约束可以作用在多个列上，不与列一起，而是单独定义

四、如何给表中的字段（或列）添加约束？

通常情况下，我们会在create table 的同时，给表中的字段添加约束。
创建表之后，我们还可以通过 alter table 的方式添加或删除约束。

五、NOT NULL 约束

1、基本介绍
1)、 非空约束用于确保当前列的值不为空值，非空约束只能出现在表对象的列上。
2)、Null类型特征：

• 所有的类型的值都可以是null，包括int、float等数据类型
• 空字符串””不等于null，0也不等于null

3)、保证列值不能为空
4)、not null只能使用列级约束的方式添加

2、创建 not null 约束：

CREATE TABLE emp(
id INT(10) NOT NULL,
NAME VARCHAR(20) NOT NULL DEFAULT 'abc',
sex CHAR NULL
);

3、增加 not null 约束：

ALTER TABLE emp
MODIFY sex VARCHAR(30) NOT NULL;

4、取消 not null 约束

ALTER TABLE emp
MODIFY sex VARCHAR(30) NULL;

5、取消 not null 约束，增加默认值：

ALTER TABLE emp
MODIFY NAME VARCHAR(15) DEFAULT 'abc' NULL;

四、UNIQUE 约束

1、基本介绍

1)、 唯一约束，允许出现多个空值：NULL。
2)、同一个表可以有多个唯一约束，多个列组合的约束。在创建唯一约束的时候，如果不给唯一约束名称，就默认和列名相同。
3)、MySQL会给唯一约束的列上默认创建一个唯一索引

CREATE TABLE USER(
 id INT NOT NULL,
 NAME VARCHAR(25),
 PASSWORD VARCHAR(16),
 #使用表级约束语法
 CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD)
);
表示用户名和密码组合不能重复

4、 添加唯一约束

ALTER TABLE USER 
ADD UNIQUE(NAME,PASSWORD);

ALTER TABLE USER 
ADD CONSTRAINT uk_name_pwd UNIQUE(NAME,PASSWORD);

ALTER TABLE USER 
MODIFY NAME VARCHAR(20) UNIQUE;

5、删除约束

ALTER TABLE USER 
DROP INDEX uk_name_pwd;

实战案例：

#  unique 唯一性约束

CREATE TABLE emp2(
id INT UNIQUE,# 列级约束
NAME VARCHAR(15),
email VARCHAR(25),
salary DOUBLE(10,2),
#表级约束
CONSTRAINT emp2_email_uk UNIQUE(email)
);

DESC emp2;
#添加成功
INSERT INTO emp2(id,NAME,email,salary)
VALUES(1,'Tom','tom@126.com',6000);

SELECT * FROM emp2;

#添加失败
INSERT INTO emp2(id,NAME,email,salary)
VALUES(1,'Tom1','tom1@126.com',6000);

#添加成功
INSERT INTO emp2(id,NAME,email,salary)
VALUES(2,'jerry',NULL,6000);
#添加成功
#可以向声明为unique的字段上多次添加null值。
INSERT INTO emp2(id,NAME,email,salary)
VALUES(3,'jerry1',NULL,7000);

# 修改表时添加或删除约束
# 声明为unique约束作用的列上，会自动添加索引。
#删除
ALTER TABLE emp2
DROP INDEX emp2_email_uk;

DESC emp2;
# 添加
ALTER TABLE emp2
ADD CONSTRAINT emp2_emai_uk UNIQUE(email);

五、PRIMARY KEY 约束

1、基本介绍
1) 主键约束相当于唯一约束+非空约束的组合，主键约束列不允许重复，也不允许出现空值
2) 如果是多列组合的主键约束，那么这些列都不允许为空值，并且组合的值不允许重复。
3)每个表最多只允许一个主键，建立主键约束可以在列级别创建，也可以在表级别上创建。
4)MySQL的主键名总是PRIMARY KEY，当创建主键约束时，系统默认会在所在的列和列组合上建立对应的唯一索引。

#列级模式

CREATE TABLE emp4(
id INT AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(20)
);

#表级模式
CREATE TABLE emp5(
id INT NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20),
pwd VARCHAR(15),
CONSTRAINT emp5_id_pk PRIMARY KEY(id)
);
#组合模式

CREATE TABLE emp5(
id INT NOT NULL AUTO_INCREMENT,
NAME VARCHAR(20),
pwd VARCHAR(15),
CONSTRAINT emp5_id_pk PRIMARY KEY(id)
);

2、删除主键约束

ALTER TABLE emp5
DROP PRIMARY KEY;

3、添加主键约束

ALTER TABLE emp5
ADD PRIMARY KEY(NAME,pwd);

4、修改主键约束

ALTER TABLE emp5
MODIFY id INT PRIMARY KEY;

实战案例：

# 主键约束：primary key 
# 一个表中，只能声明一个主键约束
# 特点：声明为主键的列要求非空且唯一。通过主键约束作用的字段，可以唯一确定表中的一条记录。
CREATE TABLE emp3(
id INT ,
NAME VARCHAR(15),
email VARCHAR(25),
salary DOUBLE(10,2),
CONSTRAINT emp3_id_pk PRIMARY KEY(id)
);

DESC emp3;

INSERT INTO emp3(id,NAME,email,salary)
VALUES(1,'Tom','tom@126.com',3400);

#添加失败
INSERT INTO emp3(id,NAME,email,salary)
VALUES(1,'Tom','tom@126.com',3400);

#添加失败
INSERT INTO emp3(id,NAME,email,salary)
VALUES(NULL,'Tom','tom@126.com',3400);

#开发中，常常创建表时：
CREATE TABLE emp4(
id INT AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(15)
);

INSERT INTO emp4(NAME)
VALUES('Tom');

INSERT INTO emp4(NAME)
VALUES('Jerry');

SELECT *
FROM emp4;

六、FOREIGN KEY

1、基本介绍
1) 外键约束是保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。
2)从表的外键值必须在主表中能找到或者为空。当主表的记录被从表参照时，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据。
3)还有一种就是级联删除子表数据。
4)注意：外键约束的参照列，在主表中引用的只能是主键或唯一键约束的列
5)同一个表可以有多个外键约束

2、创建外键约束:

#主表
CREATE TABLE dept(
dept_id INT AUTO_INCREMENT PRIMARY KEY,
dept_name VARCHAR(20)
);

#从表
CREATE TABLE emp(
emp_id INT AUTO_INCREMENT PRIMARY KEY,
last_name VARCHAR(15),
dept_id INT,
CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) 
REFERENCES dept(dept_id)
);

3、创建多列外键组合，必须使用表级约束

#主表
CREATE TABLE classes(
id INT,
NAME VARCHAR(20),
number INT,
PRIMARY KEY(NAME,number)
);
#从表
CREATE TABLE student(
id INT AUTO_INCREMENT PRIMARY KEY,
classes_name VARCHAR(20),
classes_number INT,
FOREIGN KEY(classes_name,classes_number) 
REFERENCES classes(NAME,number)
);

4、删除外键约束：

ALTER TABLE emp
DROP FOREIGN KEY emp_dept_id_fk;

5、增加外键约束

ALTER TABLE emp
ADD [CONSTRAINT emp_dept_id_fk] FOREIGN KEY(dept_id) 
REFERENCES dept(dept_id);

6、FOREIGN KEY 约束的关键字

• FOREIGN KEY: 在表级指定子表中的列
• REFERENCES: 标示在父表中的列
• ON DELETE CASCADE(级联删除): 当父表中的列被删除时，子表中相对应的列也被删除
• ON DELETE SET NULL(级联置空): 子表中相应的列置空

CREATE TABLE student( 
id INT AUTO_INCREMENT PRIMARY KEY,
NAME VARCHAR(20), 
classes_name VARCHAR(20),
classes_number INT, 
/*表级别联合外键*/ 
FOREIGN KEY(classes_name, classes_number) 
REFERENCES classes(NAME, number) ON DELETE CASCADE);

实战案例：

# 外键约束：foreign key
# 给表A的字段a设置外键约束，关联表B中的字段b.
# 约束添加以后：要求给字段a赋值时，a的值只能是表B中字段b中出现过的值 或 a赋值为null
# 约束添加以后，把表A称为字表，表B称为父表
# 能够添加成功的要求：表B中的字段b必须声明为primary key 或 unique约束。
CREATE TABLE dept5(
dept_id INT,
dept_name VARCHAR(15)

);
CREATE TABLE emp5(
id INT,
NAME VARCHAR(15),
dept_id INT,
#表级约束
CONSTRAINT emp5_deptid_fk FOREIGN KEY(dept_id) REFERENCES dept5(dept_id) 
);

ALTER TABLE dept5
ADD CONSTRAINT dept5_deptid_pk PRIMARY KEY(dept_id);

DESC emp5;

#添加失败
INSERT INTO emp5(id,NAME,dept_id)
VALUES(1,'Tom',10);

SELECT *
FROM dept5;

INSERT INTO dept5(dept_id,dept_name)
VALUES(10,'IT');

#添加成功
INSERT INTO emp5(id,NAME,dept_id)
VALUES(1,'Tom',10);

SELECT *
FROM emp5;

#删除失败
DELETE FROM dept5
WHERE dept_id = 10;

七、CHECK 约束

MySQL可以使用check约束，但check约束对数据验证没有任何作用,添加数据时，没有任何错误或警告

CHECK 约束
•   MySQL可以使用check约束，但check约束对数据验证没有任何作用,添加数据时，没有任何错误或警告
CREATE TABLE temp(
id INT AUTO_INCREMENT,
NAME VARCHAR(20),
age INT CHECK(age > 20),
PRIMARY KEY(id)
);

实战案例：

#  check：检查约束
# 在mysql 中check 约束失效

CREATE TABLE emp6(
id INT,
NAME VARCHAR(15),
salary DOUBLE(10,2) CHECK(salary >= 2000)

);

DESC emp6;

INSERT INTO emp6(id,NAME,salary)
VALUES(1,'Tom',1500);

SELECT *
FROM emp6;

八、默认值约束

# 默认值约束：default

CREATE TABLE emp7(
id INT,
NAME VARCHAR(15),
salary DOUBLE(10,2) DEFAULT 2500

);

INSERT INTO emp7(id,NAME)
VALUES(1,'Tom');

INSERT INTO emp7(id,NAME,salary)
VALUES(2,'Tom1',5000);

SELECT * FROM emp7;