ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 关于表约束constraint

关于表约束constraint

原创 Linux操作系统 作者:qgw521 时间:2009-05-22 11:27:12 0 删除 编辑
可以使用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了

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13387766/viewspace-600816/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-06-06

  • 博文量
    128
  • 访问量
    277167