笔记系列----约束

alter table name add [constraint constraint_name] {unique() | primary key() |check()}

1.什么是约束

约束是能实施业务规则以及保证数据遵循实体-关系模型的一种手段。

若违反约束，将自动回滚整个问题语句，而不是语句中的单个操作，也不是整个事务

如：姓名非空；电话号码主键；年龄性别需要check约束；一条insert插入1万行，若是某行违反约束，回滚这一万行，但是这条insert之前的dml不会回滚。

2.约束的语法

列级定义：只能引用一个列，表中可以有多个列级约束

表级定义：引用一个或多个列，通常用来定义主键

追加定义：建表后，再通过alter table命令追加的约束

SQL> select search_condition from user_constraints;

SEARCH_CONDITION
--------------------------------------------------------------------------------
"ID" IS NOT NULL
"ID" IS NOT NULL

SQL> desc a1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(38)
 NAME                                               CHAR(10)

SQL> insert into a1 values(1,'a');

1 row created.

SQL> insert into a1 values(null,'a');
insert into a1 values(null,'a')
                      *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."A1"."ID")


SQL> alter table a1 modify name not null;

Table altered.

SQL> desc a1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER(38)
 NAME                                      NOT NULL CHAR(10)

SQL> insert into a1 values(2,null);
insert into a1 values(2,null)
                        *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."A1"."NAME")

查看约束的两个数据字典视图

select * from user_constraints;

select constraint_name,column_name,table_name from user_cons_columns;

select search_condition from user_constraints;  检查具体约束语句

3.物种约束的语法

3.1非空约束

本是check约束的一种

列级定义：

create table stud (id number(2) not null,name varchar(4))；

追加非空约束：

not null约束比较特殊，一般只是列级定义和表外定义，当使用表外（追加）时，要使用modify关键字

如:  alter table emp1 modify ename not null;

或   alter table emp1 modify ename constraint xyz not null;

3.2唯一性约束（唯一性约束允许列中输入空值）

列级定义:

• create table a1(id number(2) unique , name varchar2(4));

表级定义：

• create table a2（id number(2) ,name varchar2(4) , constraint id_uk unique(id)）;

追加定义：

• alter table a2 add constraint id_uk unique(id);

唯一加非空约束可以多列。unique和not null之间没有“，”

• create table a （id init unique not null, name char(10) unique not null）；

插入唯一约束和主键约束时，会在建立约束时会创建一个索引，目的是为了检查插入数据的唯一性

3.3主键约束

非空、唯一，且只能有一个；主键约束创建时，若无该主键的索引，oracle会自动创建索引便于确定唯一性，若先前已经创建索引，则该主键约束会自动引用该索引；当删除主键约束时，若是该主键上的索引是oracle创建主键约束时自动创建的，则该主键索引随主键约束被删除，若是该主键上的索引是oracle引用的已创建的索引，则该主键索引不会随主键约束删除而被删除

alter table name drop constraint constraint_name;

alter table name add constraint constraint_name [unique() | primary key() ]

1)每个表只能建立一个主键约束，primary key=unique key + not null，主键约束可以是一列，也可以是组合多列

2）主键列上需要索引，如果该列没有索引会自动建立一个unique index，如果该列上已有索引（非唯一也可以），那么纠结用这个索引，由于是借用的索引，当主键约束被删除后，借用的索引不会被删除。同理，多列组合的主键，需要建立多列组合索引，而多列主键的单列上还可以另建单列索引

3）主键约束和唯一约束不能同时建立在一个列上。

主键约束的六种写法

列级定义

• create table u1 (id char(10) primary key,name char(20));  -- 主键名字，oracle起的
• create table u2 (id char(10) constraint pk_u2 primary key,name char(20)); -- 主键名字，自己制定

表记定义：

• create table u3 (id char(10),name cgar(20),primary key(id)); --- 主键名字，oracle指定
• create table u4 (id char(10),name char(20),constraints pk_u4 primary key (id)); -- 主键名字自己指定

追加定义：

• create table u5 (id char(10),name char(20));
• alter table u5 add primary key(id);  //desc u5;会发现u5已经自动加上了nut null属性
• alter table u5 add constraint pk_u5 primary key(id);  ---表外，后来加上主键

关于主键和索引关联问题：
 

create table t (id int,name char(10));
insert into t values (1,'sohu');
insert into t values (2,'sina');
commit;

create index t_idx on t(id);

下面这两句话效果一致，因为缺省情况下id列有索引t_id了，建主键时就会自动引用这个索引

• alter table t add constraint pk_id primary key(id);
• alter table t add constraint pk_id key(id) using index t_idx;

select constraint_name,table_name,index_name from user_constraints;

alter table t drop constraint pk_id;   //删除约束，但是索引还在，因为索引是借用的；

select index_name from user_indexes;

drop table t purge;   //t_idx是和t表关联的，关键字purge 使表和索引一并永久删除了。

也可以使用using 子句在建表、键约束、建索引一条龙，当然primary key也会自动使用这个索引。删除还约束，索引还存在

• create table t (id int,name char(10),constraint pk_id primary key(id) using index (create index t_idx on t(id)));
• create table u1(id int constraint pk_u1 primary key using index (create index u1_idx on u1(id)),name char(10));

select constraint_name,table_name,index_name from user_constraints;

select index_name from user_indexes;

3.4 外键约束（引用完整性约束）

作用：是为了和同一个表或其他表的主关键字（唯一关键字）建立连接关系，外键值必须和父表中的值匹配或者为空值

注：

外键约束和unique约束都可以有空值。

外键需要参考主键约束，但也可以参考唯一键约束。

外键和主键一般分别在两个表中，但也可以同处在一个表中

• create table emp1 as select * from emp;
• create table dept1 as select * from dept;

列级定义：

• alter table dept1 add constraint pk_dept1 primary key(deptno);
• cretae table emp100(empno int,deptno int references dept1(deptno),deptno2 int);

///外键的列级定义特殊，使用references不适用foreign key关键字

表记定义

create table emp200 (empno int,deptno int,sal int,foreign key(deptno) references dept1(deptno));

追加定义

alter table emp1 add constraint fk_emp1 foreign key(deptno) references dept1(deptno);

关于on delete cascade关键字

测试：

delete from dept1 where deptno=30;   //出现error，发现约束

alter table emp1 drop constraint fk_emp1;  ///删除外键约束

使用on delete cascade关键字重建外键，

• alter table emp1 add constraint fk_emp1 foreign key(deptno) references dept1(deptno) on delete cascade;
• select constraint_name,constraint_tye,status,delete_rule from user_constraints; ///注：delete_rule列会显示cascade，否则会显示no action

测试：

delete from dept1 where deptno=30

再查看emp1表的deptno 已经没有30号部门了，如果再对dept1的操作进行rollback，emp1的子记录也随之rollback

on delete cascade要慎用，父表中删除一行数据就可能引发子表中大量数据丢失。

为此，还有on delete set null子句，顾名思义是子表中不会删除（丢失）记录，而是将外键值填充null

如果disable dept1主键约束并使用级联cascade关键字，则emp1的外键也会disable，若再次enable dept1主键，则emp1外键仍然保持disable

alter table dept1 disable constraints pk_dept1 cascade;

alter table dept1 enable constraints pk_dept1;

select constraint_name,constraint_type,status,delete_rule from user_constraints;

drop table dept1 purge;  ///出现error，主键被外键引用

drop table dept1 cascade constraint purge;  ///此时可成功删除，而且这时外键约束也被删除了

3.5 check约束

列级定义：
create table emp100 (empno int,sal int check (sal>0),comm int);

表级定义：

create table emp200 (empno int,sal int,comm int,check (sal>1000));

追加定义：

alter table emp200 add constraint e_no_ck check (empno is not null);

验证：

insert intoemp200 values(null,1,1);  ///违反check约束，error

3.6check 约束中的表达式中不能使用变量日期函数

alter table emp1 add constraint chk check(hiredate<sysdate);

//error，日期or系统变量在check约束中错误

而应该这样写：

alter table emp1 add constraint chk check(hiredate<to_date('2000-01-01','yyyy-mm-dd'))；

3.7 级联约束

测试表

create table test2 (
    pk number primary key,
    fk number,
    col1 number,
    col2 number,
    constraint fk_constraint foreign key(fk) references test2,
    constraint ck1 check (pk>0 and col1>0),
    constraint ck2 check (col2>0)
    )
/

当删除列时：

alter table test2 drop (col2)  ;  //true

alter table test2 drop (fk); //true

alter table test2 drop (pk);  //error,在执行constraint ck1中使用了该列，约束级联问题

alter table test2 drop (col1);  //error,在constraint ck1中使用了该列，约束级联问题

若一定要删除级联约束的列，带上cascade constraints才行

alter table test2 drop (pk) cascade constraint;  //所有与pk列有关的约束统统随该列被删除掉

或(alter table test2 drop (col1) cascade constraints;)

cascade constraints将丢弃在删除列上的唯一键或主键约束

///注：constraint ck1约束只能是表级约束

3.8约束的4种状态

• enable validate：无法输入违反约束的行，而且表中所有行都要符合约束
• enable novalidate：表中可以存在不和约束的状态，但是新加入的数据必须符合约束条件
• disable novalidate：可以输入任何数据，表中或已存在不合约束条件的数据。
• disable validate：不能对表进行插入/更新/删除等操作，相当于整个表read only设定

更改约束状态是一个数据字典更新，将对所有session有效。

举例：

1）enable novalidate组态用法：

表中输入测试数据后，上线后并不想清除违规数据，但想从此刻开始执行约束

假设建立了emp1表，也插入数据，若有一天想在empno上加入primary key但是之前有不符合（not null+unique）约束的，怎么样才可以既往不咎？

create table emp1 as select * from emp;(CTAS语句使约束并未拷过来)

update emp1 set empno=7788 where empno=7902;(设置重号)

alter table emp1 add constraint pk_emp1 primary key (empno);因为要检查主键唯一索引，拒绝建立此约束

SQL> alter table emp1 add constraint pk_emp1 primary key(empno);
alter table emp1 add constraint pk_emp1 primary key(empno) enable novalidate
                                *
ERROR at line 1:
ORA-02437: cannot validate (SCOTT.PK_EMP1) - primary key violated

alter table emp1 add constraint pk_emp1 primary key (empno) enable novalidate;(因为存在唯一索引，故此不可行)

SQL> alter table emp1 add constraint pk_emp1 primary key(empno) enable novalidate;
alter table emp1 add constraint pk_emp1 primary key(empno) enable novalidate
                                *
ERROR at line 1:
ORA-02437: cannot validate (SCOTT.PK_EMP1) - primary key violated

create index empno_index on emp1(empno); 建立一个普通索引，不受unique

alter table emp1 add constraint pk_emp1 primary key(empno) enable novalidate;

此时约束可成功建上，因为直接建主键约束时，会随之创建主键索引，这个过程是固定的；但是可以通过先建立普通约束，再通过创建约束时附带enable novalidate参数，即可成功建上主键约束

此时主键约束成功建上，从此后不可以让数据重复

2）disable validate组态特点

alter table emp1 add constraint pk_emp1 primary key(empno);

select index_name from user_indexes;

alter table emp1 modify constraint pk_emp1 disable validate; //主键索引将自动删除

select index_name from user_indexes;

update emp1 set sal=8000;  ///error  不能对带有禁用和验证约束条件的表进行插入/更新/删除

而应该这样操作：

alter table emp1 modify constraint pk_emp1 enable validate;

select index_name from user_indexes;

3)将disable novalidate，enable novalidate和enable validate三种状态组合使用：

可避免因有个别符合条件的数据而导致大量数据的传输失败。

假设a表是源数据表，有控制，b表是a表的归档表，设有非空约束，先要将a表数据（远程）大批量插入b表（本地）。

当a表中出现重复时，前面所有的传输均失败,可通过下面的语句实现语句传输即使遇到空值也不失败

alter table b modify constraint b_nnl disable novalidate;
insert into b select * from a;
alter table b modify constraint b_nnl enable novalidate;
update b set channel='NOT KNOWN' where channel is null;
alter table b modify constraint b_nnl enable validate;

3.9 延迟约束

当需要大批量数据插入时，若不设置延迟约束，则需要对每条插入的数据进行验证，非常麻烦。于是延迟约束可以等数据全传输过去后，再同一进行验证，但是为了保持事务的原子性，如果出现一条语句不符合错误，所有的插入都将回退

可延迟可通过查询user_constraints视获取房钱所有关于约束的系统信息

查看user_constraints中的两个字段：

deferrable     //是否能为延迟约束 值为：deferrable 或not deferrable（缺省）

deferred       //是否采用延迟  值为：immediate（缺省）或deferred

 当deferred 参数值为deferred，若deferrable不能为延迟，则延迟约束不起作用。

注：

• 约束默认方式下是：enable/valicate和 not deferrable
• 若创建约束是没有指定deferrable则无法在后来使约束称为延迟约束（只有通过重建约束时再指定它是延迟约束）
• 一个约束只有被定义成deferrable，则这个约束session级才可以在deferred和immediate两种状态间相互转换

alter table emp1 add constraint chk_sal check(sal>500) deferrable;

set constraint chk_sal immediate;
set constraint chk_sal deferred;
此set语句是session级别的，只在本session上起作用，而对其他session不起作用，
故而在user_constraints表中deferred字段值仍是immediate；

若是想使user_constraints表中deferred字段值为deferred，需要通过以下语句重建约束

alter table emp1 add constraint chk_sal check(sal>500) deferrable initially immediate;
alter table emp1 add constraint chk_sal check(sal>500) deferrable initially deferred;

注：

使用set 切换只是在此session中起作用，换了其他session则不起作用

只有创建约束时initally定义，才可以使每个session都是延迟约束

SQL> alter table emp1 add constraint chk_sal check(sal>500) deferrable initially deferred;

Table altered.

SQL> insert into emp1(empno,sal) values(8889,400);

1 row created.