--=============================
--SQL 基础 --> 约束 (CONSTRAINT)
--=============================
一、几类数据完整性
实体完整性：表中记录不重复 ( 任何两条记录不全等 ) 并且每条记录都有一个非空主键
域完整性：表中字段值必须与字段数据类型、格式、有效范围相吻合
参照完整性：不能引用不存在的值
自定义完整性：根据特定业务领域定义的需求完整性
保证数据完整性的几种常用方法
约束 ( 最常用 )
过程
函数
触发器
实体完整性： primary key 、 unique 、索引 ( index )
域完整性： check 、 foreign key 、 not null 、数据类型
参照完整性： foreign key
自定义完整性：根据业务选用相应的约束类型
二、约束
约束是表、列级的强制规定、是防止那些无效或有问题的数据输入到表中。当对该表进行 DML
操作时，如果操作违反约束条件或规则， ORACLE 就会拒绝执行，并给出提示。
约束放置在表中，以下五种约束 :
NOT NULL 非空约束 C 指定的列不允许为空值
UNIQUE 唯一约束 U 指定的列中没有重复值，或该表中每一个值或者每一组值都将是唯一的
PRIMARY KEY 主键约束 P 唯一的标识出表的每一行 , 且不允许空值值 , 一个表只能有一个主键约束
FOREIGN KEY 外键约束 R 一个表中的列引用了其它表中的列，使得存在依赖关系，可以指向引用自身的列
CHECK 条件约束 C 指定该列是否满足某个条件
约束命名规则
如果不指定约束名 Oracle server 自动按照 SYS_Cn 的格式指定约束名，也可手动指定，
推荐的约束命名是：约束类型 _ 表名 _ 列名。
NN ： NOT NULL 非空约束，比如 nn_emp_sal
UK ： UNIQUE KEY 唯一约束
PK ： PRIMARY KEY 主键约束
FK ： FOREIGN KEY 外键约束
CK ： CHECK 条件约束
何时创建约束 :
建表的同时
建表之后
可以在表级或列级定义约束
列级约束：只能引用一个列并且它属于列定义的一部分，可定义成任意类型的完整性约束。
表级约束：可引用一个或多个列，并且它属于表定义的一部分，可定义除 NOT NULL 外的其它约束。
可以通过数据字典视图查看约束
user_constraints
dba_constraints
all_constraints
user_cons_columns 列级上的约束
dba_cons_columns 列级上的约束
建表时约束定义的基本格式：
字段定义 constraint 约束名约否类型（字段名）－－ > unique , primary key , check
字段定义 constraint 约否名 foreingn key ( 字段名） references 表名（字段名） --->foreign
三、建表时约束定义
1. 定义各种不同的约束
-- 创建一个用于作外键的表 tb_dept
SQL > CREATE TABLE tb_dept
2 (
3 deptno NUMBER ( 4 ) PRIMARY KEY ,
4 deptname VARCHAR2 ( 20 ),
5 loc VARCHAR ( 50 )
6 );
-- 建表时创建约束，没有指定约束名，则系统将自动命名约束名
SQL > CREATE TABLE tb_constraint_1
2 (
3 empno NUMBER PRIMARY KEY , -- 主键约束
4 ename VARCHAR2 ( 20 ) NOT NULL, -- 非空约束
5 email VARCHAR2 ( 60 ) UNIQUE , -- 唯一约束
6 sal NUMBER ( 5 ) CHECK ( sal > 1500 ), -- 核查约束
7 deptno NUMBER ( 4 ) REFERENCES tb_dept ( deptno ) -- 外键约束
8 );
-- 建表时指定了约束名
SQL > CREATE TABLE tb_constraint_2
2 (
3 empno NUMBER CONSTRAINT pk_tb_cons2_empno PRIMARY KEY ,
4 ename VARCHAR2 ( 20 ) CONSTRAINT nn_tb_cons2_empno NOT NULL,
5 email VARCHAR2 ( 60 ) CONSTRAINT un_tb_cons2_email UNIQUE ,
6 sal NUMBER ( 5 ) CONSTRAINT ck_tb_cons2_sal CHECK ( sal > 1500 ),
7 deptno NUMBER ( 4 ) CONSTRAINT fk_tb_cons2_dept REFERENCES tb_dept ( deptno )
8 );
2. 查看约束的定义信息
-- 查看表的约束
SQL > SELECT owner , constraint_name , constraint_type , table_name , status , deferrable , validated
2 FROM user_constraints
3 ORDER BY table_name ;
OWNER CONSTRAINT_NAME C TABLE_NAME STATUS DEFERRABLE VALIDATED
------------- ------------------- - -------------------- -------- -------------- ---------
ROBINSON SYS_C005543 C TB_CONSTRAINT_1 ENABLED NOT DEFERRABLE VALIDATED
ROBINSON SYS_C005545 P TB_CONSTRAINT_1 ENABLED NOT DEFERRABLE VALIDATED
ROBINSON SYS_C005546 U TB_CONSTRAINT_1 ENABLED NOT DEFERRABLE VALIDATED
ROBINSON SYS_C005544 C TB_CONSTRAINT_1 ENABLED NOT DEFERRABLE VALIDATED
ROBINSON SYS_C005547 R TB_CONSTRAINT_1 ENABLED NOT DEFERRABLE VALIDATED
ROBINSON UN_TB_CONS2_EMAIL U TB_CONSTRAINT_2 ENABLED NOT DEFERRABLE VALIDATED
ROBINSON PK_TB_CONS2_EMPNO P TB_CONSTRAINT_2 ENABLED NOT DEFERRABLE VALIDATED
ROBINSON NN_TB_CONS2_EMPNO C TB_CONSTRAINT_2 ENABLED NOT DEFERRABLE VALIDATED
ROBINSON CK_TB_CONS2_SAL C TB_CONSTRAINT_2 ENABLED NOT DEFERRABLE VALIDATED
ROBINSON FK_TB_CONS2_DEPT R TB_CONSTRAINT_2 ENABLED NOT DEFERRABLE VALIDATED
ROBINSON SYS_C005542 P TB_DEPT ENABLED NOT DEFERRABLE VALIDATED
-- 查看列的约束
SQL > SELECT * FROM user_cons_columns ;
OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION
--------------- ---------------------- ---------------------- --------------- ----------
ROBINSON FK_TB_CONS2_DEPT TB_CONSTRAINT_2 DEPTNO 1
ROBINSON CK_TB_CONS2_SAL TB_CONSTRAINT_2 SAL
ROBINSON UN_TB_CONS2_EMAIL TB_CONSTRAINT_2 EMAIL 1
ROBINSON NN_TB_CONS2_EMPNO TB_CONSTRAINT_2 ENAME
ROBINSON PK_TB_CONS2_EMPNO TB_CONSTRAINT_2 EMPNO 1
ROBINSON SYS_C005542 TB_DEPT DEPTNO 1
ROBINSON SYS_C005547 TB_CONSTRAINT_1 DEPTNO 1
ROBINSON SYS_C005544 TB_CONSTRAINT_1 SAL
ROBINSON SYS_C005546 TB_CONSTRAINT_1 EMAIL 1
ROBINSON SYS_C005543 TB_CONSTRAINT_1 ENAME
ROBINSON SYS_C005545 TB_CONSTRAINT_1 EMPNO 1
3. 定义符合主键约束
-- 定义复合主键
SQL > CREATE TABLE tb_constraint_3
2 (
3 empno NUMBER ,
4 ename VARCHAR2 ( 20 ),
5 email VARCHAR2 ( 20 ) UNIQUE ,
6 CONSTRAINT pk_tb_cons3_empno_ename PRIMARY KEY ( empno , ename )
7 );
-- 查询 TB_CONSTRAINT_3 的约束信息
SQL > SELECT owner , constraint_name , constraint_type , table_name , status , deferrable , validated
2 FROM user_constraints
3 WHERE table_name = 'TB_CONSTRAINT_3' ;
OWNER CONSTRAINT_NAME C TABLE_NAME STATUS DEFERRABLE VALIDATED
------------ ------------------------- - ----------------- -------- -------------- ---------
ROBINSON PK_TB_CONS3_EMPNO_ENAME P TB_CONSTRAINT_3 ENABLED NOT DEFERRABLE VALIDATED
ROBINSON SYS_C005554 U TB_CONSTRAINT_3 ENABLED NOT DEFERRABLE VALIDATED
-- 两列上具有相同的约束名
SQL > SELECT * FROM user_cons_columns WHERE table_name = 'TB_CONSTRAINT_3' ;
OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION
------------------ ---------------------- ------------------- --------------- ----------
ROBINSON SYS_C005554 TB_CONSTRAINT_3 EMAIL 1
ROBINSON PK_TB_CONS3_EMPNO_ENAME TB_CONSTRAINT_3 ENAME 2
ROBINSON PK_TB_CONS3_EMPNO_ENAME TB_CONSTRAINT_3 EMPNO 1
4. 几种不同约束的冲突提示
-- 创建一个序列用于产生主键
SQL > CREATE SEQUENCE cons_sequence
2 INCREMENT BY 1
3 START WITH 100
4 MAXVALUE 200
5 NOCACHE
6 NOCYCLE ;
-- 为表 tb_dept 插入记录
SQL > INSERT INTO tb_dept
2 SELECT 10 , 'Development' , 'ShenZhen' FROM DUAL
3 UNION ALL
4 SELECT 20 , 'Customer' , 'ShangHai' FROM DUAL ;
2 rows created .
-- 为表 tb_constraint_2 插入记录
SQL > INSERT INTO tb_constraint_2
2 VALUES ( cons_sequence . nextval , 'Robinson' , 'Robinson@hotmail.com' , 2000 , 10 );
1 row created .
a . 主键约束提示
-- 下面使用 currval 值，提示主键冲突，从 PK_TB_CONS2_EMPNO 即可得知是主键列冲突，这就是自定义约束名的好处
SQL > INSERT INTO tb_constraint_2
2 VALUES ( cons_sequence . currval , 'Jack' , 'Jack@hotmail.com' , 2200 , 10 );
INSERT INTO tb_constraint_2
*
ERROR at line 1 :
ORA - 00001 : unique constraint ( ROBINSON . PK_TB_CONS2_EMPNO ) violated
b . 非空约束提示
-- 注意在 Oracle 中，空字符串 ('') 被当成空值，下面的错误提示即是，什么原因不清楚
SQL > INSERT INTO tb_constraint_2
2 VALUES ( cons_sequence . nextval , '' , 'Jack@hotmail.com' , 2200 , 10 );
VALUES ( cons_sequence . nextval , '' , 'Jack@hotmail.com' , 2200 , 10 )
*
ERROR at line 2 :
ORA - 01400 : cannot insert NULL into ( "ROBINSON" . "TB_CONSTRAINT_2" . "ENAME" )
-- 下面这条记录插入的才是 ename 为空值的插入语句
SQL > INSERT INTO tb_constraint_2
2 VALUES ( cons_sequence . nextval ,NULL, 'Jack@hotmail.com' , 2200 , 10 );
VALUES ( cons_sequence . nextval ,NULL, 'Jack@hotmail.com' , 2200 , 10 )
*
ERROR at line 2 :
ORA - 01400 : cannot insert NULL into ( "ROBINSON" . "TB_CONSTRAINT_2" . "ENAME" )
--------------------------------------------------------------------------------------------
-- 下面是在 SQL server 2005 中的演示，不存在上述出现的问题
-- 理论上空字符串 ('') 并不等于 NULL, 不知道为什么在 Oracle 10g 中出现了错误提示
CREATE TABLE tb_constraint_1
(
empno int PRIMARY KEY , -- 主键约束
ename VARCHAR ( 20 ) NOT NULL, -- 非空约束
email VARCHAR ( 60 ) UNIQUE , -- 唯一约束
sal int CHECK ( sal > 1500 ) -- 核查约束
-- deptno NUMBER(4) REFERENCES tb_dept(deptno) -- 外键约束
);
INSERT INTO tb_constraint_1
SELECT 15 , 'Andy' , 'Andy@hotmail.com' , 1800 ;
INSERT INTO tb_constraint_1 --ename 为 '' 的记录插入成功
SELECT 16 , '' , 'John@hotmail.com' , 1800 ;
SELECT * FROM tb_constraint_1 WHERE ename IS NOT NULL;
empno ename email sal
----------- -------------------- ----------------------------- ---------
15 Andy Andy@hotmail . com 1800
16 John@hotmail . com 1800
( 2 row ( s ) affected )
---------------------------------------------------------------------------------------------
c . 唯一约束提示
--ORACLE 在唯一键列上自动生成一个唯一索引以实现唯一性
-- 提示 email 字段唯一性冲突
SQL > INSERT INTO tb_constraint_2
2 VALUES ( cons_sequence . nextval , 'Jack' , 'Robinson@hotmail.com' , 2400 , 20 );
INSERT INTO tb_constraint_2
*
ERROR at line 1 :
ORA - 00001 : unique constraint ( ROBINSON . UN_TB_CONS2_EMAIL ) violated
d . CHECK 约束提示
-- 提示 check 约束 sal 字段冲突
SQL > INSERT INTO tb_constraint_2
2 VALUES ( cons_sequence . nextval , 'Henry' , 'Henry@hotmail.com' , 1350 , 40 );
INSERT INTO tb_constraint_2
*
ERROR at line 1 :
ORA - 02290 : check constraint ( ROBINSON . CK_TB_CONS2_SAL ) violated
e . 外键约束提示
-- 提示不符合外键约束
SQL > INSERT INTO tb_constraint_2
2 VALUES ( cons_sequence . nextval , 'Henry' , 'Henry@hotmail.com' , 1550 , 40 );
INSERT INTO tb_constraint_2
*
ERROR at line 1 :
ORA - 02291 : integrity constraint ( ROBINSON . FK_TB_CONS2_DEPT ) violated - parent
key not found
5. 补充
CHECK 约束
定义每一行必须满足的条件
以下的表达式是不允许的 :
– 出现 CURRVAL , NEXTVAL , LEVEL , 和 ROWNUM 伪列
– 使用 SYSDATE , UID , USER , 和 USERENV 函数
– 在查询中涉及到其它列的值
FOREIGN KEY 约束
外键约束是用来维护从表和主表的引用完整性的，所以外键约束要涉及两个表。
FOREIGN KEY : 在表级指定子表中的列
REFERENCES : 标示在父表中的列
ON DELETE CASCADE : 当父表中的列被删除时，子表中相对应的列也被删除
ON DELETE SET NULL: 子表中相应的列置空
如果子表在建外键时，该列的数据并不在父表，则无法创建该约束。
-- 外键约束对 delete 语句的影响：
SQL > DELETE FROM tb_constraint_2 ;
2 rows deleted .
SQL > ROLLBACK ;
Rollback complete .
-- 子表 tb_constraint_2 中有记录存在，故不能删除父表中的相关记录
SQL > DELETE FROM tb_dept ;
DELETE FROM tb_dept
*
ERROR at line 1 :
ORA - 02292 : integrity constraint ( ROBINSON . FK_TB_CONS2_DEPT ) violated - child record found
外键约束对 insert 语句的影响：
插入数据的外键字段值必须在主表中存在，只有从表才有可能违反约束，主表不会。
外键约束对 delete 语句的影响：
删除主表数据时，如果从表有对该数据的引用，要先将从表中的数据处理好。主表才有可能违反约束。
外键约束对 update 语句的影响：
主从表都有可能违反外键约束，操作一个表必须将另一个表的数据处理好。
外键约束对 DDL 语句的影响：
删除主表时，才有可能违约约束。
ON DELETE SET NULL 和 ON DELETE CASCADE 对外键约束的影响
ON DELETE SET NULL 子句的作用是，当主表中的一行数据被删除时， ORACLE 自动将从表中依赖于
它的记录外键值改为空。
-- 为从表删除约束后新增带 on delete set null 的外键约束
SQL > ALTER TABLE tb_constraint_2
2 DROP CONSTRAINT FK_TB_CONS2_DEPT ;
SQL > ALTER TABLE tb_constraint_2
2 ADD CONSTRAINT fk_tb_cons2_deptno
3 FOREIGN KEY ( deptno ) REFERENCES tb_dept ( deptno )
4 ON DELETE SET NULL;
SQL > SELECT * FROM tb_constraint_2 ;
EMPNO ENAME EMAIL SAL DEPTNO
---------- -------------------- --------------------------------------- ---------- ----------
113 Robinson Robinson@hotmail . com 2000 10
114 Mark Mark@hotmail . com 3000 20
-- 删除主表中 deptno 为的记录
SQL > DELETE FROM tb_dept WHERE deptno = 10 ;
1 row deleted .
-- 从表中 deptno 为的被置为 NULL
SQL > SELECT * FROM tb_constraint_2 ;
EMPNO ENAME EMAIL SAL DEPTNO
---------- -------------------- ------------------------------------------ ---------- ----------
113 Robinson Robinson@hotmail . com 2000
114 Mark Mark@hotmail . com 3000 20
ON DELETE CASCADE 子句的作用是，当主表中的一行数据被删除时， ORACLE 自动将从表中依赖于它的记录外键也删除。
-- 为从表删除约束后新增带 on delete cascade 的外键约束
SQL > ALTER TABLE tb_constraint_2
2 DROP CONSTRAINT fk_tb_cons2_deptno ;
SQL > ALTER TABLE tb_constraint_2
2 ADD CONSTRAINT fk_tb_cons2_deptno
3 FOREIGN KEY ( deptno ) REFERENCES tb_dept ( deptno )
4 ON DELETE CASCADE ;
-- 主表中 deptno 为的记录被删除
SQL > DELETE FROM tb_dept WHERE deptno = 20 ;
1 row deleted .
-- 从表中 deptno 为的记录被删除
SQL > SELECT * FROM tb_constraint_2 ;
EMPNO ENAME EMAIL SAL DEPTNO
---------- -------------------- ------------------------------- ---------- ----------
113 Robinson Robinson@hotmail . com 2000
四、建表后的约束定义
使用 ALTER TABLE 语句 :
添加或删除约束 , 但是不能修改约束
有效化或无效化约束
添加 NOT NULL 约束要使用 MODIFY 语句
SQL > DROP TABLE tb_constraint_2 ;
Table dropped .
SQL > CREATE TABLE tb_cons2
2 (
3 empno NUMBER ,
4 ename VARCHAR2 ( 20 ),
5 email VARCHAR2 ( 60 ),
6 sal NUMBER ( 5 ),
7 deptno NUMBER ( 4 )
8 );
1. 添加主键约束
SQL > ALTER TABLE tb_cons2
2 ADD CONSTRAINT pk_tb_cons2_empno PRIMARY KEY ( empno );
2. 添加非空约束
-- 注意添加非空约束使用的是 modify 而非 add
SQL > ALTER TABLE tb_cons2
2 ADD CONSTRIANT nn_tb_cons2_ename NOT NULL( ename );
ADD CONSTRIANT nn_tb_cons2_ename NOT NULL
*
ERROR at line 2 :
ORA - 00902 : invalid datatype
SQL > ALTER TABLE tb_cons2
2 ADD CONSTRIANT nn_tb_cons2_ename ename NOT NULL;
ADD CONSTRIANT nn_tb_cons2_ename ename NOT NULL
*
ERROR at line 2 :
ORA - 01735 : invalid ALTER TABLE option
SQL > ALTER TABLE tb_cons2
2 MODIFY ( ename CONSTRAINT nn_tb_cons2_ename NOT NULL);
3. 添加唯一约束
SQL > ALTER TABLE tb_cons2
2 ADD CONSTRAINT uk_tb_cons2_email UNIQUE ( email );
4. 添加 CHECK 约束
SQL > ALTER TABLE tb_cons2
2 ADD CONSTRAINT ck_tb_cons2_sal CHECK ( sal > 1500 );
5. 添加外键约束
SQL > ALTER TABLE tb_cons2
2 ADD CONSTRAINT fk_tb_cons2_tb_dept_deptno
3 FOREIGN KEY ( deptno ) REFERENCES tb_dept ( deptno )
4 ON DELETE CASCADE ;
6. 禁用约束
默认情况下创建的约束是启用的
-- 添加一个新列 comm 以及一个 check 约束并将其置为禁用模式
SQL > ALTER TABLE tb_cons2
2 ADD comm NUMBER ( 4 ) CONSTRAINT ck_cons2_comm CHECK ( comm > 0 ) DISABLE ;
SQL > SELECT owner , constraint_name , constraint_type , table_name , status , deferrable , validated
2 FROM user_constraints
3 WHERE table_name = 'TB_DEPT' ;
OWNER CONSTRAINT_NAME C TABLE_NAME STATUS DEFERRABLE VALIDATED
--------------- ------------------------- - ------------------- -------- -------------- ----------
ROBINSON SYS_C005542 P TB_DEPT ENABLED NOT DEFERRABLE VALIDATED
-- 下面禁用 tb_dept 表的主键约束 , 提示存在依赖性 , 不能成功禁用该约束
SQL > ALTER TABLE tb_dept
2 DISABLE CONSTRAINT SYS_C005542 ;
ALTER TABLE tb_dept
*
ERROR at line 1 :
ORA - 02297 : cannot disable constraint ( ROBINSON . SYS_C005542 ) - dependencies exist
-- 通过增加 CASCADE 来实现级联禁用约束
SQL > ALTER TABLE tb_dept
2 DISABLE CONSTRAINT SYS_C005542 CASCADE ;
-- 下面的查询可以看到基于 tb_dept 表存在外键约束的 tb_cons2 ,tb_constraint_1 上的外键列约束都被禁用
SQL > SELECT constraint_name , constraint_type , table_name , status , deferrable , validated
2 FROM user_constraints
3 WHERE validated = 'NOT VALIDATED' ;
CONSTRAINT_NAME C TABLE_NAME STATUS DEFERRABLE VALIDATED
------------------------------ - ---------------------- -------- -------------- -------------
CK_CONS2_COMM C TB_CONS2 DISABLED NOT DEFERRABLE NOT VALIDATED
FK_TB_CONS2_TB_DEPT_DEPTNO R TB_CONS2 DISABLED NOT DEFERRABLE NOT VALIDATED
SYS_C005542 P TB_DEPT DISABLED NOT DEFERRABLE NOT VALIDATED
SYS_C005547 R TB_CONSTRAINT_1 DISABLED NOT DEFERRABLE NOT VALIDATED
UK_TB_CONS2_EMAIL U TB_CONS2 DISABLED NOT DEFERRABLE NOT VALIDATED
7. 启用约束
ENABLE 子句可将当前无效的约束启用
当定义或启用 UNIQUE 或 PRIMARY KEY 约束时系统会自动创建 UNIQUE 或 PRIMARY KEY 索引
-- 启用约束时不支持 CASCADE ，对被级联禁用的约束应根据需要逐个启用
SQL > ALTER TABLE tb_dept
2 ENABLE CONSTRAINT SYS_C005542 CASCADE ;
ENABLE CONSTRAINT SYS_C005542 CASCADE
*
ERROR at line 2 :
ORA - 00933 : SQL command not properly ended
SQL > ALTER TABLE tb_dept
2 ENABLE CONSTRAINT SYS_C005542 ;
-- 可以使用 ENABLE NOVALIDATE ，实现只对新数据应用某个约束
-- 约束默认的是 ENABLE VALIDATE ，即对所有的行实现约束检查
SQL > ALTER TABLE tb_cons2
2 ENABLE NOVALIDATE CONSTRAINT ck_cons2_comm ;
8. 删除约束：
SQL > ALTER TABLE tb_cons2
2 DROP CONSTRAINT uk_tb_cons2_email ;
使用下面的方法可以级联删除主表主键及从表的外键
ALTER TABLE table_name DROP PRIMARY KEY CASCADE
-- 使用 drop primary key cascade 删除主表主键及从表外键
SQL > ALTER TABLE tb_dept
2 DROP PRIMARY KEY CASCADE ;
-- 删除后可以看到不存在 tb_dept 主键约束及 tb_cons2 外键的记录
SQL > SELECT constraint_name , constraint_type , table_name , status , deferrable , validated
2 FROM user_constraints
3 ORDER BY table_name ;
CONSTRAINT_NAME C TABLE_NAME STATUS DEFERRABLE VALIDATED
------------------------------ - ------------------- -------- -------------- -------------
CK_TB_CONS2_SAL C TB_CONS2 ENABLED NOT DEFERRABLE VALIDATED
NN_TB_CONS2_ENAME C TB_CONS2 ENABLED NOT DEFERRABLE VALIDATED
PK_TB_CONS2_EMPNO P TB_CONS2 ENABLED NOT DEFERRABLE VALIDATED
UK_TB_CONS2_EMAIL U TB_CONS2 DISABLED NOT DEFERRABLE NOT VALIDATED
SYS_C005546 U TB_CONSTRAINT_1 ENABLED NOT DEFERRABLE VALIDATED
SYS_C005545 P TB_CONSTRAINT_1 ENABLED NOT DEFERRABLE VALIDATED
SYS_C005543 C TB_CONSTRAINT_1 ENABLED NOT DEFERRABLE VALIDATED
SYS_C005544 C TB_CONSTRAINT_1 ENABLED NOT DEFERRABLE VALIDATED
PK_TB_CONS3_EMPNO_ENAME P TB_CONSTRAINT_3 ENABLED NOT DEFERRABLE VALIDATED
SYS_C005554 U TB_CONSTRAINT_3 ENABLED NOT DEFERRABLE VALIDATED
9. 级联约束
CASCADE CONSTRAINTS 子句在 DROP COLUMN 子句中使用
该子句会删除涉及到在已删除列上定义的主键或唯一关键字的所有引用完整性约束
该子句也将删除在已删除列上定义的所有多列约束
SQL > CREATE TABLE tb_cons3
2 (
3 empno NUMBER PRIMARY KEY ,
4 sal NUMBER ,
5 comm NUMBER ,
6 mgr NUMBER ,
7 CONSTRAINT fk_tb_cons3 FOREIGN KEY ( mgr ) REFERENCES tb_cons3 ( empno ),
8 CONSTRAINT ck_tb_cons3_sal CHECK ( empno > 0 AND sal > 0 ),
9 CONSTRAINT ck_tb_cons3_comm CHECK ( comm > 0 )
10 );
Table created .
-- 下面提示主键列不能删除
SQL > ALTER TABLE tb_cons3 DROP COLUMN empno ;
ALTER TABLE tb_cons3 DROP COLUMN empno
*
ERROR at line 1 :
ORA - 12992 : cannot drop parent key column
-- 下面提示 sal 被多列约束，也不能删除
SQL > ALTER TABLE tb_cons3 DROP COLUMN sal ;
ALTER TABLE tb_cons3 DROP COLUMN sal
*
ERROR at line 1 :
ORA - 12991 : column is referenced in a multi - column constraint
-- 使用带有 CASCADE CONSTRAINTS 的 DROP COLUMN 该表中的 pk,fk, 及 ck_sal 都将被删除
SQL > ALTER TABLE tb_cons3 DROP COLUMN empno CASCADE CONSTRAINTS ;
Table altered .
10. 延迟约束
指仅当事物被提交时强制执行约束
在添加约束时可以使用 DEFERRABLE 子句来指定约束为延迟约束
对于已经存在的约束不能修改为 DEFERRABLE 延迟约束，只能删除后重建时指定 DEFERRABLE 子句
使用 DEFERRABLE 子句时可以使用 INITIALY IMMEDIATE 或 INITIALY DEFERRED
INITIALY IMMEDIATE ：缺省的行为，即实时实施约束行为
INITIALY DEFERRED ：延迟约束行为到提交时予以检查
-- 创建 tb_cust 表
SQL > CREATE TABLE tb_cust
2 (
3 custid NUMBER ( 4 ) NOT NULL,
4 custname VARCHAR2 ( 20 )
5 );
Table created .
-- 为表添加主键约束并启用延迟约束
SQL > ALTER TABLE tb_cust
2 ADD CONSTRAINT pk_tb_cust_custid PRIMARY KEY ( custid )
3 DEFERRABLE INITIALLY DEFERRED ;
Table altered .
-- 插入条记录后提交，给出违反了约束并出现回滚
SQL > INSERT INTO tb_cust SELECT 10 , 'Jay' FROM DUAL ;
1 row created .
SQL > INSERT INTO tb_cust SELECT 10 , 'SAM' FROM DUAL ;
1 row created .
SQL > COMMIT ;
COMMIT
*
ERROR at line 1 :
ORA - 02091 : transaction rolled back
ORA - 00001 : unique constraint ( ROBINSON . PK_TB_CUST_CUSTID ) violated
-- 将约束置为实时启用
SQL > SET CONSTRAINT pk_tb_cust_custid IMMEDIATE ;
Constraint set .
-- 插入两条新纪录，未执行 commit 前时约束已起作用
SQL > INSERT INTO tb_cust SELECT 10 , 'Robinson' FROM DUAL ;
1 row created .
SQL > INSERT INTO tb_cust SELECT 10 , 'Jack' FROM DUAL ;
INSERT INTO tb_cust SELECT 10 , 'Jack' FROM DUAL
*
ERROR at line 1 :
ORA - 00001 : unique constraint ( ROBINSON . PK_TB_CUST_CUSTID ) violated
SQL > COMMIT ;
Commit complete .
-- 查看最后插入的记录
SQL > SELECT * FROM tb_cust ;
CUSTID CUSTNAME
---------- --------------------
10 Robinson
五、更多
SQL 基础 --> ROLLUP 与 CUBE 运算符实现数据汇总
SQL 基础 --> 层次化查询 (START BY ... CONNECT BY PRIOR)
