关于表约束constraint

关于表约束constraint
===========================================================
作者: xsb(http://xsb.itpub.net)
发表于:2005.08.19 09:18
分类: Oracle
出处：http://xsb.itpub.net/post/419/38481
---------------------------------------------------------------
可以使用disable,enable novalidate,enable validate选项。
可用于检查历史数据是否违反某种业务规则，并找出这些记录！

CREATE TABLE t1 (c1 NUMBER,c2 NUMBER);
INSERT INTO t1 VALUES (1,2);
INSERT INTO t1 VALUES (1,20);
COMMIT;
SELECT * FROM t1;
alter table T1
add constraint ck_t1
check (c2 BETWEEN 18 and 80)
ENABLE novalidate;
create table exceptions(row_id rowid,
owner varchar2(30),
table_name varchar2(30),
constraint varchar2(30));
ALTER TABLE t1 ENABLE Validate CONSTRAINT ck_t1
EXCEPTIONS INTO EXCEPTIONS;
SELECT *
FROM T1
WHERE ROWID IN (SELECT ROW_ID FROM EXCEPTIONS WHERE TABLE_NAME = 'T1')
FOR UPDATE;
TRUNCATE TABLE EXCEPTIONS;
------------------------------------------------------------------------------------------------

constraints 三个需要注意的地方

Ref: http://spaces.msn.com/sunmoonking/
工作许多年，一直没有看重CONSTRINTS的作用，除了用CHECK，NOT NULL，INDEX，等，其他的一般也就看看就过去了。最近把零散的知识整理一下，才发现CONSTRAINTS发展15年来的成熟与重要。
ORACLE提供了众多的constraint，如果没有充分利用这些constraints，那么也就是没有充分利用关系型数据库。如果能了解各种 constraint的各种参数，那么就能减少locking的时间，减少constraint检验数据的时间，减少影响其他应用的时间。
CONSTRAINTS：就是让数据满足某些规则。
CONSTRAINTS TYPE： NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
CONSTRAINTS 不但可以建立在TABLE上，也可以建立在VIEW上，
CONSTRAINTS 的状态：ENABLED/DISABLED
VALIDATED/NOVALIDATED
DEFERRABLE/NON-DEFERRABLE
DEFERRED/IMMEDIATE
RELY/NORELY
constraint只能被difered如果最初定义时是deferrable
view的constraints必须被设置成disabled，novalidated或rely
1. deferrable
一个constraint如果被定义成deferrable那么这个constraints可以在deferred和imediate两种状态相互转换。 deferred只在transaction中有效，也就是只可以在transaction过程中使constraint失效，但如果 transaction commit的话，transaction会变成immediate。

1* create table cons_parent (id number(10),name varchar2(10))
SQL> /
Table created.
SQL> create table cons_child (id number(10),name varchar2(10));
Table created.
1* alter table cons_parent add primary key (id)
SQL> /
Table altered.
alter table cons_child add constraints chi_fk_par foreign key (id)
references cons_parent(id)
SQL> alter table cons_child add constraints chi_fk_par foreign key (id)
2 references cons_parent(id)
3 /
Table altered.
一个constraints默认是NOT DEFERRABLE的。
1 select constraint_name||' '||deferrable from all_constraints
2* where constraint_name='CHI_FK_PAR'
SQL> /
CONSTRAINT_NAME||''||DEFERRABLE
---------------------------------------------
CHI_FK_PAR NOT DEFERRABLE
NOT DEFERRABLE的不能在deferred和imediate两种状态相互转换
SQL> set constraints chi_fk_par deferred;
SET constraints chi_fk_par deferred
*
ERROR at line 1:
ORA-02447: cannot defer a constraint that is not deferrable
SQL> alter table cons_child drop constraints chi_fk_par;
Table altered.
1 alter table cons_child add constraints chi_fk_par foreign key (id)
2* references cons_parent(id) deferrable
SQL> /
Table altered.
1 select constraint_name||' '||deferrable from all_constraints
2* where constraint_name='CHI_FK_PAR'
SQL> /
CONSTRAINT_NAME||''||DEFERRABLE
---------------------------------------------
CHI_FK_PAR DEFERRABLE
一个constraint如果被定义成deferrable那么这个constraints可以在deferred和imediate两种状态相互转换
SQL> set constraints chi_fk_par immediate;

Constraint set.

1* insert into cons_child values (2,'llll')
SQL> /
insert into cons_child values (2,'llll')
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.CHI_FK_PAR) violated - parent key not found

SQL> set constraints chi_fk_par deferred;

Constraint set.

SQL> insert into cons_child values (2,'llll');

1 row created.

SQL> set constraints chi_fk_par immediate;
SET constraints chi_fk_par immediate
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.CHI_FK_PAR) violated - parent key not found

deferred只在transaction中有效，也就是只可以在transaction过程中使constraint失效，但如果transaction commit的话，transaction会变成immediate。

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (SYSTEM.CHI_FK_PAR) violated - parent key not found
deferrable会影响CBO的计划，并且正常情况下没有应用的必要，所以建议不要修改，而用系统默认的non deferrable

2. enable/disable validate/novalidate

enable/disable对未来的数据有约束/无约束。

validate/novalidate对已有的数据有约束/无约束。

如果加约束到一个大表，那么ORACLE会LOCK这个表，然后SCAN所有数据，来判断是否符合CONSTRAINT的要求，在繁忙的系统里显然是不合适的。所以用enable novalidate比较合适，因为ORACLE仅仅会LOCK表一小段时间来建立CONSTRAINT，当CONSTRAINT建立后再 VALIDATE，这时检验数据是不会LOCK表的。

这方面很多书上都有例子，就不在这里累述了

3.REFERENCE 让人疑惑的地方

1* create table wwm_father (id number,name varchar2(10),primary key (id,name))
SQL> /

Table created.

SQL> create table wwm_child (id number,name varchar2(10),
2 foreign key (id,name) references wwm_father on delete set null);

Table created.

SQL> insert into wwm_father values (6,'wwm');

1 row created.

SQL> insret into wwm_child values (6,'fff');
SP2-0734: unknown command beginning "insret int..." - rest of line ignored.


可以看出，REFERENCE是起作用的。但下面就有点让人疑惑了，似乎ORACLE不用该用这种策略来做，

SQL> insert into wwm_child values (6,null);
1 row created.

SQL> insert into wwm_child values(null,'lll');

1 row created.

1* insert into wwm_child values (null,null)
SQL> /

1 row created.
SQL> select * from wwm_father;

ID NAME
---------- --------------------
6 wwm

SQL> select * from wwm_child;

ID NAME
---------- --------------------
6
lll


SQL> select count(*) from wwm_child;

COUNT(*)
----------
3

可见，如果向CHILD表插入NULL的话，ORACLE默认认为NULL是匹配FATHER表里相关的REFERENCE的字段内容的。因此FOREIGN KEY的COLUMN大家就需要认真考虑是否要设置成NOT NULL了