12-表的约束

知识背景

表的约束，就是在表中的数据上加上约束，也被称为数据完整性约束。数据完整性约束的目的是为了不被规定的、不符合规范的数据进入数据库

在录入数据库或数据发生变化时，DBMS(数据库管理系统)会按照一定的约束条件对数据进行监测，是不符合规范的数据不能进入数据库，使数据库中存储的数据正确、有效、相容，确保存储的数据是正确的。

在表中有三大类完整性约束：实体完整性(entity integrity)、参照完整性(referential integrity)和用户定义的完整性(user-defined integrity)

"单列约束可以定义在列级或者表级，多列约束则必须定义在表级"这是什么意思呢？

单列约束：指定是设计涉及字段(列)的约束。这类约束可以在定义字段时直接指定(即列级)，例如在创建表时直接为某个字段指定NOT NULL 或 UNIQUE约束，也可以在表级别定义，即在创建表之后单独为某个字段添加约束

多列约束：涉及两个或更多字段(列)的约束，这类约束必须在表级别定义，因为他们关联了多个字段，不能仅仅在单个字段级别进行定义，如最常见的复合主键(由多个列共同作为主键)和复合外键

实体完整性约束

实体完整性的定义

1、关系型数据库中每个元组应该是可区分的，是唯一的，使用这样的约束条件来保证实体完整性

2、实体完整性约束所对应的属性被称为表的主键(primary key)，或者称为关系的主码

3、实体完整性约束可以定义在表中的一列或多列上

实体完整性的规则

1、实体完整性的规则基本都是针对于表而言的，一个基本表通常对应于现实世界的一个实体集

2、主键对应的列值在表中是非空的、唯一的

3、每个表中最多只能拥有一个主键

实体完整性约束创建语法

1、定义主键约束

CREATE TABLE table_name (column1 INT,column2 VARCHAR(255),...PRIMARY KEY (column1)					//使得column1被定义为主键
);

2、定义主键约束并且给主键约束命名

CREATE TABLE table_name (column1 INT,column2 VARCHAR(255),...CONSTRAINT constraint_name PRIMARY KEY (column1)				//定义column为主键，并使这个主键被命名为constraint_name
);

3、如果表已经存在，还可以手动增加主键约束

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column1);

参照完整性约束

参照完整性约束的定义

1、参照完整性就好像现实世界中实体与实体的依赖关系，比如鱼与水，水就是鱼的生存环境，这样的关系在数据中，表与表之间也是存在的

2、一个表的属性需要依赖于另一个表的属性，在数据库中被映射为一个表的字段与另一个表的某个字段相关联，使用这样的约束条件来保证参照完整性，例如，员工表和部门表，因为每一个入职的员工都必须要又对应的部门，当员工表中有一条员工数据是没有部门的，那么就说明这一条员工的数据是不合理的

3、 参照完整性约束对应的属性被称为表的外键(foreign key)，或者被称为关系的外码

4、参照完整性约束可定义在表中的一列或者多列上

5、外键约束需要有主表和从表，如上述所说的例子，员工表就是从表，部门表就是主表(被参照的表)

参照完整性约束的规则

1、被参照的相对应列上必须被定义为主键约束或唯一约束，也就是说在主表上的被参照的字段必须被定义为主键约束或唯一约束

2、表的外键列可以取空值

参照完整性语法举例

设有两个表，分别为classes表和student表，其中classes表为主表，student表为从表，并且classes表的主键字段id被student_id字段给引用

CREATE TABLE classes (id INT PRIMARY KEY,					//被引用的字段必须被设置为主键约束或唯一约束name VARCHAR(255)
);
CREATE TABLE students (id INT PRIMARY KEY,name VARCHAR(255),class_id INT,CONSTRAINT ID FOREIGN KEY (class_id) REFERENCES classes(id)      //是class表中di字段被当前正在创建的表的class_id字段所引用，并且被命名为ID	
);

如果表已经存在还可添加外键约束

ALTER TABLE students
ADD CONSTRAINT ID
FOREIGN KEY (class_id)
REFERENCES classes(id);

在进行参照完整性的过程当中，还可进行级联操作，有更新级联和删除级联，以下这个例子就是删除级联(以DELETE为关键字，更新的关键字即为UPDATE)

CREATE TABLE classes (id INT PRIMARY KEY,name VARCHAR(255)
);
CREATE TABLE students (id INT PRIMARY KEY REFERENCES classes ON DELETE RESTRICT/CASCADE/SETNULL， //进行删除级联name VARCHAR(255),class_id INT,CONSTRAINT ID FOREIGN KEY (class_id) REFERENCES classes(id)	
);

或是

CREATE TABLE classes (id INT PRIMARY KEY,name VARCHAR(255)
);
CREATE TABLE students (id INT PRIMARY KEY,name VARCHAR(255),class_id INT,CONSTRAINT ID FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE RESTRICT/CASCADE/SETNULL
);

参数	介绍
ON DELETE RESTRICT	当在父表/主表(即外键的来源表)中删除对应记录时，首先检查该记录是否有对应的外键，如果有，则不允许删除
ON DELETE CASCADE	当在父表/主表(即外键的来源表)中删除对应记录时，首先检查该记录是否有对应外键，如果有，则也级联地删除外键在子表(即包含外键的表)中的记录
ON DELETE SETNULL	当在父表/主表(即外键的来源表)中删除对应记录时，首先检查该记录是否有对应外键，如果有，则将外键列设置为空值

用户定义的完整性约束

任何的关系型数据库都应该支持实体完整性和参照完整性，这是关系模型所要求的。除此之外，不同的关系型数据库系统根据其应用环境的不同，还需要一些特殊的约束条件。而用户定义的完整性约束就是针对某一具体关系数据库的约束条件

非空约束

概述：非空约束就是要求设置非空约束的列值不同为空值，使用NOT NULL关键字来设置

语法：

在下述的创建表的SQL语句中，"id integer NOT NULL"就是指定了t01表中id字段为非空约束

CREATE TABLE t01 (id integer NOT NULL,name text.score numeric);

唯一约束

概述：唯一约束需要保证设置唯一约束的列值是在整个列中是唯一的，但可以为空

语法：

“id integer UNQIUE”就是将id字段设置为唯一约束

CREATE TABLE t01(id integer UNQIUE,name text,score numeric);

check约束

概述：限制一个特定列中的值必须要满足一定的条件表达式，当插入数据是，DBMS将检查该数据是否满足check约束条件，当不满足时，将会拒绝这种操作

语法：

下述SQL语句中，"score numeric CHECK (score > 0)"的check约束为在插入或更新score的数据值必须大于0

CREATE TABLE t01(id integer,name text,score numeric CHECK (score > 0));

约束的控制状态

概述：

①enable/disable 对新增的数据进行检查/对新增的数据不进行检查

②validate/novalidate 对现有数据的约束检查/对现有数据的约束不检查

约束控制方法：

约束的控制方法都是，对现有的数据和对新增的数据各自执行一个策略，其中外严内峻是默认状态，如下图

参数	简介
enable+validate	默认状态；对新插入的数据执行完整性约束检查，对表中现有的数据执行完整性约束检查
disable+novalidate	对新插入的数据不执行完整性约束检查，对表中现有的数据不执行完整性约束检查
disable+validate	对新插入的数据不执行完整性约束检查，对表中现有的数据执行完整性约束检查，不允许对表执行DML操作，即对数据进行增删查改等操作
enable+novalidate	对新插入的数据执行完整性约束检查，对表中现有的数据不执行完整性约束检查

这里可能会问道，为什么在"disable+validate"的约束控制状态下，不能执行DML操作，也就是不能执行增删改等操作呢？

个人理解：我想是因为"disable+validate"这两个概念刚好起了冲突，①首先，disable是针对新增的数据进行检查。②其次，validate是针对表中已有的数据进行检查，③但是，当数据新增进来的时候，新增的数据在某一个时刻，有两种身份，即作为新增的数据，又是表中现有的数据，那么DBMS到底应不应该检查呢？所以这时就产生了矛盾，才会导致不允许执行DML操作

kimi解释：当DISABLE和VALIDATE组合在一起时，由于DISABLE允许插入不符合约束条件的数据，而VALIDATE在数据变化时会检查约束条件，这会导致在插入数据时不会检查约束，但在后续的检查中发现数据不符合约束条件，从而产生矛盾和数据不一致的问题

延迟约束

1、概述：

KES支持延迟检测约束的功能，便于在数据迁移和应用数据批量导入时保证成功率和操作效率

2、deferrable(可延迟的)和not deferrable(不可延迟的)，这两个选项控制了该约束是否能被延迟。一个不可延迟的约束将在每一次数据插入或修改后立刻被检查，可延迟的检查将被推迟到事务结束时进行

3、initially immediate(立即检查)和initially deferred(延迟检查)，延迟判断规则，如果该定义约束时指定为initially immediate，那么它在每一次数据插入或修改后被检查，这是默认值。如果指定为intially immediate，那么它会在每一次数据插入或修改后被检查，这是默认值。如果指定为initially deferred，那么它只会在事务结束之后被检查

4、用户可以在事务中设置延迟约束的延迟属性

SET CONSTRAINTS {ALL | constraint_name[,...]} {DEFERRED | IMMEDIATE}

实验1：主键约束

t01表的结构如下图

t02表的结构如下图

创建表时定义主键约束

1、在列级定义主键约束

创建t01表，并在id列上创建主键约束

test=# create table t01(id integer primary key,name text,score numeric);

2、在表级定义主键约束

将刚刚创建的t01表删除(无论是否存在的方法)，创建t01表，并在id列上创建主键约束

test=# drop table if exists t01;
test=# create table t01(id integer,name text,score numeric,constraint ID primary key(id));

修改表时增加主键约束

1、将刚刚创建的t01表删除(无论是否存在的方法)

test=# drop table if exists t01;

2、创建t01表

test=# create table t01(id integer,name text,socre numeric);

3、修改t01，为id列创建主键约束

test=# alter table t01 add constraint ID primary key(id);

验证主键约束

1、往t01表插入2条id相同的数据，将会产生报错

test=# insert INTO t01(id) values (100);
INSERT 0 1
test=# insert INTO t01(id) values (100);

实验2：外键约束

创建外键约束

1、在被参照表中创建主键约束

创建t01表，并在id列上定义主键约束，因为在外键约束的形成中，被参照表中的被引用字段需要是主键约束或唯一约束

test=# create table t01(id integer primary key,name text,score numeric);

2、在参照表中创建外键约束

创建t02表，并在no列上定义外键约束，引用t01表的id主键列

test=# create table t02(no integer,name text,constraint t02_fk foreign key (no) REFERENCES t01(id));

3、验证外键约束

往t01表插入1行数据

往t02表插入2行数据，其中1行数据违反外键约束

test=# insert INTO t01(id) values (100);
test=# insert INTO t02(no) values (100);
test=# insert INTO t02(no) values (200);

外键约束级联控制

注：

①存在外键依赖时，不可直接对父表的主键列执行更新操作

②使用ON DELETE CASCADE 选项可以在删除父表元组时实现级联删除子表元组

③使用ON DELETE SET NULL 选项可以在删除父表元组时实现级联更新子表元组的外键列为空值

1、创建t02表，在no列上创建主键约束

test=# create table t02(no integer primary key,name text); 

2、创建t01表，在id列上创建外键约束，参照t02表的no列并设置为级联控制

test=# create table t01 (id integer,name text,score numeric,constraint t02_fk foreign key (id) references t02(no) on delete cascade);

3、往t02表中插入1条记录，往t01表中插入1条满足外键约束的记录

test=# insert INTO t02(no) values (100);
test=# insert INTO t01(id) values (100);

4、更新t02表的no字段，该操作会因存在外键关联导致更新主键列失败

test=# update t02 SET no = 200 where no = 100;

5、删除t02表中的记录(因为子表中参照的外键设置为级联删除。所以可成功删除父表元组)

test=# delete from t02 where no=100;

6、查看t01表的记录

test=# select * from t01;id | name | score 
----+------+-------
(0 行记录)

实验3：非空约束

创建表时定义非空约束

1、创建t01表，并在id列上创建非空约束

test=# create table t01(id integer not null,name text,score numeric);

修改表时增加非空约束

1、修改t01表时，并在name列上创建非空约束

test=# alter table t01 alter COLUMN name set not null;

2、使用元命令查看t01表的结构信息

test=# \d t01数据表 "public.t01"栏位  |  类型   | 校对规则 |  可空的  | 预设 
-------+---------+----------+----------+------id    | integer |          | not null | name  | text    |          | not null | score | numeric |          |          | 

验证非空约束

1、往t01表中插入2条记录，其中1行数据违反非空约束

test=# insert INTO t01 values(100,'aaa',1);
test=# insert INTO t01 values(100,null,2);

实验4：唯一约束

创建表时定义唯一约束

1、创建t01表，并在id列上创建唯一约束

test=# create table t01 (id integer unique,name text,score numeric);

修改表时增加唯一约束

1、修改t01表，并在name列上增加唯一约束

test=# alter table t01 add constraint un_t01 unique(name);

验证唯一约束

1、往t01表中插入2条记录，其中1行数据违反唯一约束

test=# insert INTO t01 values (100,'aaa',1);
test=# insert into t01 values (100.'aaa',2);

实验5：check约束

创建表时定义check约束

1、创建t01表，并在score列上创建check约束，要求score取值必须大于0

test=# create table t01(id integer,name text,score numeric check(score>0));

修改表时增加check约束

1、修改t01表，并在socre列上增加check约束，要求score取值必须小于100

test=# alter table t01 add constraint check_score_t01 check(id<100);

验证check约束

1、往t01表中插入2条记录，其中1行数据违反check约束

test=# insert INTO t01(id) values (99);
test=# insert INTO t01(id) values (100);

实验6：约束的状态控制

enable+validate

1、创建测试表t01，并为id列定义check约束，要求id列值必须大于10

test=# create table t01(id integer,name text,score numeric,constraint T01_CK check(id>10));

2、插入一条无效的数据到t01表中(执行报错)

test=# insert INTO t01(id) values (9); 
错误:  关系 "t01" 的新列违反了检查约束 "t01_ck"
描述:  失败, 行包含(9, null, null).

3、通过数据字典表(dba_constraints)查看约束的信息(constraint_name,status,validated)，数据字典表示t01表时，需要使用大写，即针对T01表进行查询，

test=# select constraint_name,status,validated from dba_constraints where table_name='T01';constraint_name | status | validated 
-----------------+--------+-----------T01_CK          | ENABLE | VALIDATED
(1 行记录)

disable+novalidate

1、修改t01表的t01_ck约束为disable novalidate

test=# alter table t01 disable novalidate constraint t01_ck ;

2、插入一条无效的数据到t01表中(执行成功)

test=# insert INTO t01(id) values (9);
INSERT 0 1

3、插入一条有效的数据到t01表中(执行成功)

test=# insert INTO t01 (id) values (11);
INSERT 0 1

4、通过数据字典表查看约束的信息

test=# select constraint_name,status,validated from dba_constraints where table_name='T01';constraint_name | status  |  validated  
-----------------+---------+-------------T01_CK          | DISABLE | NOVALIDATED
(1 行记录)

disable+validate

1、调整约束t01_ck为disable validate状态会产生报错(因为在上面的实验中我们插入了违规数据)

test=# alter table t01 disable validate constraint t01_ck ;
错误:  一些行违反了检查约束 "t01_ck"

2、删除违规数据

test=# delete from t01 where id =9;

3、调整约束t01_ck为disable validate状态成功

test=# alter table t01 disable validate constraint t01_ck ;
ALTER TABLE

4、对t01表执行DML操作，应该是执行失败的

test=# insert INTO t01(id) values(12);  
错误:  不对表 （t01） 进行插入/更新/删除，在禁用并验证约束 （t01_ck） 的情况下
test=# update t01 SET id=12 where id=11;
错误:  不对表 （t01） 进行插入/更新/删除，在禁用并验证约束 （t01_ck） 的情况下
test=# delete from t01 where id=11;     
错误:  不对表 （t01） 进行插入/更新/删除，在禁用并验证约束 （t01_ck） 的情况下

5、通过数据字典查看约束的信息

test=# select constraint_name,status,validated from dba_constraints where table_name='T01';constraint_name | status  | validated 
-----------------+---------+-----------T01_CK          | DISABLE | VALIDATED
(1 行记录)

enable+novalidate

1、禁用约束t01_ck(即disable+novalidate)，插入一条违反约束的记录到t01表中

test=# alter table t01 disable novalidate constraint t01_ck ;
ALTER TABLE
test=# insert INTO t01(id) values (9);
INSERT 0 1

2、调整约束t01_ck为enable novalidate状态，可以发现这是修改成功的，因为只会对新增的数据进行检查，而不会对已有的违规数据进行检查

test=# alter table t01 enable novalidate constraint t01_ck ;

3、插入一条违反约束的记录到t01表中，如果不出意外的话，应该是插入失败的，因为DBMS对新增的数据进行了检查，发现新增的数据是一条违规数据，随后就被拒绝了

test=# insert INTO t01(id) values (8);
错误:  关系 "t01" 的新列违反了检查约束 "t01_ck"
描述:  失败, 行包含(8, null, null).

4、插入一条正常的记录到t01表中，当然这是会成功的

test=# insert INTO t01(id) values(12); 
INSERT 0 1

实验7：延迟约束

创建延迟约束

1、创建测试表t01，并在id列上创建主键约束

test=# create table t01(id integer primary key,name text,score numeric);      

2、创建测试表t02，并未id列定义延迟外键约束，参照t01表的id列

test=# create table t02 (id int,name text,constraint t02_fk foreign key (id) references t01(id) initially deferred);

3、显式开启事务

test=# begin ;
BEGIN

4、往子表t02中插入一条数据，执行成功，因为当前开启的事务，而当前的延迟状态时deferrable(可延迟的)，可延迟的检查将被推迟到事务结束时进行

test=# insert INTO t02 values (20,'a');

5、往父表t01中插入一条数据

test=# insert INTO t01 values (20,'a',1);

6、结束事务，自动提交成功

test=# end;

7、通过数据字典表查看约束的信息

test=# select constraint_name,deferred from user_constraints where table_name='T02';constraint_name | deferred 
-----------------+----------T02_FK          | DEFERRED
(1 行记录)

在事务中控制延迟约束

在事务中执行约束，可以使得我们原本在非延迟的环境下，进入事务后，改为延迟的环境，在进行数据的增删改，从而达到退出事务后，环境依然是而非延迟的环境。总之，在事务中控制延迟约束可以达到在非延迟的环境下，做到延迟的环境下事情，这一切都要归功于事务的强大性能

注：①、延迟约束只能在事务中使用。②提交事务时总会验证表中的数据是否满足约束，不满足时导致事务失败

1、修改t02表的t02_fk约束为非延迟

test=# alter table t02 alter CONSTRAINT t02_fk initially immediate;

2、开启事务，在事务中临时修改t02_fk约束为延迟约束

test=# begin ;        
BEGIN
test=# alter table t02 alter CONSTRAINT t02_fk initially deferred;

3、往子表t02中插入一条数据。执行成功，因为在事务的影响下，我们处在延迟约束的环境下的

test=# insert INTO t02 values (30,'abc'); 

4、往父表t02中插入一条数据

test=# insert INTO t01 values (30,'acac',8);

5、结束事务，自动提交成功

test=# end; 
COMMIT

使用约束时的注意事项

1、一张表只能有一个主键约束，其它非主键列可以结合使用唯一约束+非空约束来实现类似的功能

2、check约束的定义必须是一个条件表达式，表达式要有确定的结果，要么为true，要么为flase

3、我们将列的默认值定义也可以看作是一种定义在表上的约束，用于列的自动填充的缺省值。这句话的意思是，列的默认值其实也是一种约束，这个约束的作用是帮助列自动填充缺省值

4、定义外键约束的列可以与被参照的父表中的列拥有不同的名称，但列的数据类型必须要匹配。就是外键约束，参照表的参照字段与被参照表的被参照字段的数据类型必须要匹配