SQL-高级命令（二）约束（Constraints）

SQL 约束（Constraints） 

SQL 约束用于规定表中的数据规则

约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);

在 SQL 中，我们有如下约束：

（1）NOT NULL - 指示某列不能存储 NULL 值

（2）UNIQUE - 保证某列的每行必须有唯一的值

（3）PRIMARY KEY（简称PK）- NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录

（4）FOREIGN KEY（简称FK）- 保证一个表中的数据匹配另一个表中的值的参照完整性

（5）CHECK - 保证列中的值符合指定的条件

（6）DEFAULT - 规定没有给列赋值时的默认值

列级约束：约束单个字段

表级约束： 约束多个字段

1、SQL NOT NULL 约束

NOT NULL 约束强制列不接受 NULL 值

CREATE TABLE Persons (
    ID int NOT NULL,
    Age int
);

 如果给字段加了not null 约束，插入数据时没给值，就会报错

insert into user(name) values('admin');

ERROR 1364 (HY000): Field 'name' doesn't have a default value

添加 NOT NULL 约束

ALTER TABLE Persons
MODIFY Age int NOT NULL;

删除 NOT NULL 约束

ALTER TABLE Persons
MODIFY Age int NULL;

2、SQL UNIQUE 约束

UNIQUE 约束唯一标识数据库表中的每条记录

UNIQUE 约束的字段不能重复，但是可以为NULL

UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。

PRIMARY KEY 约束拥有自动定义的 UNIQUE 约束。

注：

每个表可以有多个 UNIQUE 约束；但是每个表只能有一个 PRIMARY KEY 约束

约束单列

MySQL：

CREATE TABLE Persons
(
    P_Id int NOT NULL,
    Address varchar(255),
    UNIQUE (P_Id)
)

（1）只约束一个字段，属于列级约束；如果约束多个字段，则为表级约束，如下同时约束name 和 email两个字段

drop table if exists user;
create table t_vip(
    id int,
    name varchar(255),
    email varchar(255),
    unique(name,email) // 约束没有添加在列的后面，这种约束被称为表级约束
);

（2）unique 和 not null 联合使用

drop table if exists user;
create table user(
    id int not null unique,
    name varchar(255) 
);

mysql> desc user;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

这时会发现变成了主键

 在mysql当中，如果一个字段同时被not null和unique约束的话，该字段自动变成主键字段。（oracle中不一样）

SQL Server / Oracle / MS Access：

CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
Address varchar(255)
)

约束多列

MySQL / SQL Server / Oracle / MS Access：

CREATE TABLE Persons
(
P_Id int NOT NULL,
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,Address)
)

ALTER TABLE 时的 SQL UNIQUE 约束

MySQL / SQL Server / Oracle / MS Access：

单列
ALTER TABLE Persons
ADD UNIQUE (P_Id)

MySQL / SQL Server / Oracle / MS Access：

多列
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,Address)

撤销 UNIQUE 约束

MySQL：

ALTER TABLE Persons
DROP INDEX uc_PersonID

SQL Server / Oracle / MS Access：

ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID

3、SQL PRIMARY KEY 约束

PRIMARY KEY 约束唯一标识数据库表中的每条记录

主键必须包含唯一的值

主键列不能包含 NULL 值

每个表都应该有一个主键，并且每个表只能有一个主键

（1）CREATE TABLE 时的 SQL PRIMARY KEY 约束

在 "Persons" 表创建时在 "P_Id" 列上创建 PRIMARY KEY 约束

MySQL：

CREATE TABLE Persons
(
P_Id int NOT NULL,
Address varchar(255),
PRIMARY KEY (P_Id)
)

 如下：

drop table if exists user;
		// 1个字段做主键，叫做：单一主键
		create table user(
			id int primary key,  //列级约束
			name varchar(255)
		);

或者

drop table if exists user;
		create table user(
			id int,
			name varchar(255),
			primary key(id)  // 表级约束
		);

 不能为null，也不能重复

//错误：不能重复
insert into user(id,name) values(2,'wangwu');
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

//错误：不能为NULL
insert into user(name) values('zhaoliu');
ERROR 1364 (HY000): Field 'id' doesn't have a default value

单一主键：唯一的1个字段做主键

复合主键： 多个字段联合作为主键

在实际开发中不建议使用：复合主键；主键值存在的意义就是唯一标识数据库表中的每条记录

（1）一张表，主键约束只能添加1个

drop table if exists user;
		create table user(
			id int primary key,
			name varchar(255) primary key
		);
		
ERROR 1068 (42000): Multiple primary key defined

（2）主键值建议使用：int，bigint，char 等类型

不建议使用：varchar来做主键；主键值一般都是数字，一般都是定长的

（3）主键除了：单一主键和复合主键之外，还可以如下分类

自然主键：主键值是一个自然数，和业务没关系

业务主键：主键值和业务紧密关联，例如拿银行卡账号做主键值

在实际开发中使用自然主键，主键只要做到不重复就行，主键一旦和业务挂钩，那么当业务发生变动的时候，可能会影响到主键值

（4）auto_increment 自动维护主键值

drop table if exists user;
create table user(
    id int primary key auto_increment, //auto_increment表示自增，从1开始，以1递增！
    name varchar(255)
);

SQL Server / Oracle / MS Access：

CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
Address varchar(255)
)

名 PRIMARY KEY 约束，并定义多个列的 PRIMARY KEY 约束

MySQL / SQL Server / Oracle / MS Access

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
Address varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

注：在上面的实例中，只有一个主键 PRIMARY KEY（pk_PersonID）。然而，pk_PersonID 的值是由两个列（P_Id 和 LastName）组成的

（2）ALTER TABLE 时的 SQL PRIMARY KEY 约束

当表已被创建时，如需在 "P_Id" 列创建 PRIMARY KEY 约束

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)

命名 PRIMARY KEY 约束，并定义多个列的 PRIMARY KEY 约束

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

注 ：使用 ALTER TABLE 语句添加主键，必须把主键列声明为不包含 NULL 值（在表首次创建时）

（3）撤销 PRIMARY KEY 约束

MySQL：

ALTER TABLE Persons
DROP PRIMARY KEY

SQL Server / Oracle / MS Access：

ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID

4、SQL FOREIGN KEY 约束

一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)

"Persons" 表：

P_Id	LastName	Address	City
1	Hansen	Timoteivn 10	Sandnes
2	Svendson	Borgvn 23	Sandnes
3	Pettersen	Storgt 20	Stavanger

"Orders" 表：

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	2
4	24562	1

"Orders" 表中的 "P_Id" 列指向 "Persons" 表中的 "P_Id" 列。

"Persons" 表中的 "P_Id" 列是 "Persons" 表中的 PRIMARY KEY。

"Orders" 表中的 "P_Id" 列是 "Orders" 表中的 FOREIGN KEY。

FOREIGN KEY 约束用于预防破坏表之间连接的行为。

FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

当两张表存在关联字段的时候，利用外键可以保证主表和从表的一致性和完整性

（1）CREATE TABLE 时的 SQL FOREIGN KEY 约束

在 "Orders" 表创建时在 "P_Id" 列上创建 FOREIGN KEY 约束：

MySQL：

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)

如下，创建了学生表和班级表，学生的班级号就是班级表的班级号

drop table if exists t_student;
drop table if exists t_class;

//创建班级表
create table class(
	classno int primary key,
	classname varchar(255)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

//创建学生表
create table student(
	no int primary key auto_increment,
	name varchar(255),
	cno int,
	foreign key(cno) references class(classno) //外键：学生表的cno 关联 班级表的 classNo 字段
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

当cno字段没有任何约束的时候，可能会导致数据无效。如班级号102不存在；为了保证cno字段中的值都是100和101，需要给cno字段添加外键约束

insert into student(name,cno) values('zhaolin', 102);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`mydb`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `class` (`classno`))

子表中的外键引用的父表中的某个字段，被引用的这个字段不一定是主键，但至少具有unique约束 

外键值可以为null

注：

class是父表；student是子表

先删子表，再删父表

先创建父表，再创建子表

先删子表数据，再删父表数据

先插入父表数据，再插入子表数据

SQL Server / Oracle / MS Access：

CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)

如需命名 FOREIGN KEY 约束，并定义多个列的 FOREIGN KEY 约束

MySQL / SQL Server / Oracle / MS Access：

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)

（2）ALTER TABLE 时的 SQL FOREIGN KEY 约束

当 "Orders" 表已被创建时，如需在 "P_Id" 列创建 FOREIGN KEY 约束

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

如需命名 FOREIGN KEY 约束，并定义多个列的 FOREIGN KEY 约束

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

（3）撤销 FOREIGN KEY 约束

如需撤销 FOREIGN KEY 约束

MySQL：

ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

SQL Server / Oracle / MS Access：

ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders

5、SQL CHECK 约束

CHECK 约束用于限制列中的值的范围。

如果对单个列定义 CHECK 约束，那么该列只允许特定的值。

如果对一个表定义 CHECK 约束，那么此约束会基于行中其他列的值在特定的列中对值进行限制

（1）CREATE TABLE 时的 SQL CHECK 约束

在 "Persons" 表创建时在 "P_Id" 列上创建 CHECK 约束。CHECK 约束规定 "P_Id" 列必须只包含大于 0 的整数。

MySQL：

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
City varchar(255),
CHECK (P_Id>0)
)

SQL Server / Oracle / MS Access：

CREATE TABLE Persons
(
P_Id int NOT NULL CHECK (P_Id>0),
LastName varchar(255) NOT NULL,
City varchar(255)
)

如需命名 CHECK 约束，并定义多个列的 CHECK 约束

MySQL / SQL Server / Oracle / MS Access：

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

（2）ALTER TABLE 时的 SQL CHECK 约束

当表已被创建时，如需在 "P_Id" 列创建 CHECK 约束

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Persons
ADD CHECK (P_Id>0)

如需命名 CHECK 约束，并定义多个列的 CHECK 约束

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

（3）撤销 CHECK 约束

如需撤销 CHECK 约束，请使用下面的 SQL：

SQL Server / Oracle / MS Access：

ALTER TABLE Persons
DROP CONSTRAINT chk_Person

MySQL：

ALTER TABLE Persons
DROP CHECK chk_Person

6、SQL DEFAULT 约束

DEFAULT 约束用于向列中插入默认值

（1）CREATE TABLE 时的 SQL DEFAULT 约束

在 "Persons" 表创建时在 "City" 列上创建 DEFAULT 约束：

My SQL / SQL Server / Oracle / MS Access：

CREATE TABLE Persons
(
    P_Id int NOT NULL,
    LastName varchar(255) NOT NULL,
    City varchar(255) DEFAULT 'Sandnes'
)

通过使用类似 GETDATE() 这样的函数，DEFAULT 约束也可以用于插入系统值：

CREATE TABLE Orders
(
    O_Id int NOT NULL,
    OrderNo int NOT NULL,
    P_Id int,
    OrderDate date DEFAULT GETDATE()
)

（2）ALTER TABLE 时的 SQL DEFAULT 约束

当表已被创建时，如需在 "City" 列创建 DEFAULT 约束

MySQL：

ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'

SQL Server / MS Access：

ALTER TABLE Persons
ADD CONSTRAINT ab_c DEFAULT 'SANDNES' for City

Oracle：

ALTER TABLE Persons
MODIFY City DEFAULT 'SANDNES'

（3）撤销 DEFAULT 约束

如需撤销 DEFAULT 约束

MySQL：

ALTER TABLE Persons
ALTER City DROP DEFAULT

SQL Server / Oracle / MS Access：

ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT