ORACLE中CONSTRAINT的四对属性
summary:
在data migrate时,某些表的约束总是困扰着我们,让我们的migratet举步维艰,如何利用约束本身的属性来处理这些问题呢?本文详细介绍了约束的四对属性:
Deferrable/not deferrable,
Deferred/immediate,
enalbe/disable,
validate/novalidate,
以及如何应用这些属性灵活应对相关问题
是否要求满足约束
Validate
Novalidate
------------------------------------------------------------------------------------
已有记录
新增/修改记录
已有记录
新增/修改记录
Enable
Yes
Yes
No
Yes
Disable
Yes
No
No
No
Validate确保已有数据符合约束;
Novalidate不必考虑已有数据是否符合约束。
除非Novalidate被指定,Enable默认Validate;
除非Validate被指定,Disable默认Novalidate;
Enable Validate与Enable相同,检查已有记录和新增记录,确保都符合约束;
Enable Novalidate 允许已有记录不必满足约束条件,但新增/修改的记录必须满足;
Disable Validate禁用约束,删除约束上的索引,不允许修改任何被约束的记录;
Disable Novalidate与Disable相同,禁用约束,删除约束上的索引,且允许修改被约束的记录。
1. Deferrable,not deferrable(default value)
1)
这对属性是定义是否可以defer,defer是指作检查的时机,如果在commit时check为Defer,否则为immediate .只有在设置Deferrable才可以设置另一个属性2-- Deferred,immediate.
2)
设置defer check的方法有两种(前提是建立了Deferrable的contraint)
a.
通过建contraint时指定Deferred值
b.
通过会话级别的语句修改
SET CONSTRAINT(s) contraint_name/all immediate/deferred.
3)
这对属性是在创建的constraint的时候定义的,不能被修改.
4) notice:如果建立了Deferrable的uk或pk,只会建立相应的nonuniquce index,而不会建立uniquce index
2. Deferred,immediate(default value)
1)
这对属性定义是否defer.
Deferred: check on commit; immediate: check immediate.
2)
If constraint is not deferrable,immediate is only choice.
3)
For
example:
CREATE TABLE games
(scores NUMBER, CONSTRAINT unq_num UNIQUE (scores)
INITIALLY DEFERRED DEFERRABLE);
insert into games values(1);
insert into games values(1);
commit;--在此报错
You will not get a error util you commit it;
SET CONSTRAINT(s) unq_num immediate;--修改属性
insert into games values(2);
insert into games values(2);--在此报错
commit;
You will get a error when you execute the second sql;
3. novalidate, validate(default value)
1)
这对属性定义constraint是否对表中已经存在的数据作检查,例如:
create table t(id number);
insert into t values(1);
insert into t values(2);
alter table t add constraint ch_100 check(id>=100); --失败
alter table t add constraint ch_100 check(id>=100) novalidate;--成功
2)
notice:与唯一索引相关的contraint(例如pk,uk),要做到以上的效果还必须设置为Deferrable(只是建立非唯一性索引),因为在维护索引是,如果违反了唯一性也会报错,所以必须建立非唯一性索引.例如:
drop table t;
create table t(id number);
insert into t values(1);
insert into t values(1);
alter table t add constraint ch_100 unique(id) ; --报错
alter table t add constraint ch_100 unique(id) novalidate; --报错
alter table t add constraint ch_100 unique(id) deferrable
novalidate;--成功
4. disable, enalbe(default value)
1)
启用和禁用constraint.在新建pk和uk时定义了disable,将不建立相应的索引.
ALTER TABLE dept DISABLE CONSTRAINT dname_ukey;
ALTER TABLE dept ENABLE CONSTRAINT dname_ukey;
alter table t add constraint ch_100 unique(id) disable;
2)
DISABLE
uk或pk作了些什么:
Disable非deferrable 的pk、uk,将删除相应的索引(除非指定了keep index,但是keep下来的索引是唯一性的,insert数据时还是要作唯一性检查的),在enable时重建索引.
Disbale deferrable 的pk、uk将保留原来的索引(因为原来的索引就是非唯一性的,不影响insert的操作).
3)
一些操作经验
KEEP INDEX要注意的:
a.ALTER TABLE games DISAble CONSTRAINT fk_num keep index;--唯一索引被保留,所以还是不能插入重复的数据.不应该keep index.
b.ALTER TABLE games
DISAble CONSTRAINT fk_num;
--如果上一步被执行,那么此语句什么都不做,唯一索引仍被保留,此时应该先enable在disable.如果原来的状态是able的话,那么唯一索引将被删除.
5. 报告约束异常
如果校验约束时存在异常,则返回一个错误,且完整性约束仍保持未被校验状态。当约束存在异常时,一个语句就不能正确执行,则此语句被回滚。如果存在异常,必须要更新或删除了约束的所有异常后,才可以校验约束。但是在使用ALTER TABLE语句不能确定哪一行违反约束,为了确定哪一行,在发布ENABLE子句中带有EXCEPTION选项的ALTER TABLE语句。
EXCEPTION选项将ROWID、OWNER、TABLE、ROWID、CONSTRAINT放到一个指定的表中。在启用约束前,硬创建一个合适的异常报告表,用来接收ENABLE子句的EXCEPTION选项信息,可以直接执行'?\rdbms\admin\utlexcpt.sql'或'?\rdbms\admin\utlexcpt1.sql'脚本来进行创建。注意:这两个脚本的区别在于数据库的兼容性级别和所分析的表的类型。
使用的语法如下:
ALTER TABLE dept ENABLE PRIMARY KEY EXCEPTIONS INTO EXCEPTIONS;
或者
alter table team disable validate constraint id_num EXCEPTIONS INTO EXCEPTIONS;--这个方法不创建索引
如果存在异常,则直接查询 SELECT * FROM EXCEPTIONS; 即可。
如果需要更加详细的信息,则可以使用ROWID与原表的ROWID进行关联,这样就可以查出原始表中当前行的所有信息,并进行修改。
如何利用EXCEPTIONS来处理实践中的一些问题:
(1)在已有的constraint上加载数据
先 disable
再加载
再enable and EXCEPTIONS INTO EXCEPTIONS
再根据EXCEPTIONS表的纪录delete
最后enable
(2)新建constraint,原有的数据违反了这个约束
先create disable
再enable and EXCEPTIONS INTO EXCEPTIONS
以下是一个新建外键的例子
alter table team add constraint id_num foreign key(id) references games(scores) INITIALLY disable;
alter table team enable constraint id_num EXCEPTIONS INTO EXCEPTIONS;
select * from EXCEPTIONS;
6. 关于contraint操作的一些脚本
定义约束
alter table t add constraint ch_100 unique(id) disable ;
alter table t add constraint ch_100 unique(id);
alter table t add constraint ch_100 unique(id) deferred deferrable novalidate;
CREATE TABLE dept_20
(employee_id NUMBER(4),
commission_pct NUMBER(7,2),
department_id,
CONSTRAINT fk_deptno FOREIGN KEY (department_id) REFERENCES departments(department_id)
on delete/set null cascade);
ALTER TABLE dept_20
ADD CONSTRAINT fk_empid_hiredate FOREIGN KEY (employee_id, hire_date) REFERENCES hr.job_history(employee_id, start_date) EXCEPTIONS INTO EXCEPTIONS;
CREATE TABLE divisions
(div_no NUMBER CONSTRAINT check_divno CHECK (div_no BETWEEN 10 AND 99) DISABLE,
div_name VARCHAR2(9) CONSTRAINT check_divname CHECK (div_name = UPPER(div_name)) DISABLE,
office VARCHAR2(10) CONSTRAINT check_office CHECK (office IN ('DALLAS','BOSTON', 'PARIS','TOKYO')) DISABLE);
修改或删除现有的约束
注:在使用外键参考了PRIMARY或UNIQUE键时,不能停用或删除被参考约束
延迟校验的启用停用:
SET CONSTRAINT(s) unq_num/all immediate;
SET CONSTRAINT(s) unq_num/all deferred;
停用:
ALTER TABLE dept DISABLE CONSTRAINT dname_ukey;
ALTER TABLE dept DISABLE PRIMARY KEY KEEP INDEX,
DISABLE UNIQUE (dname, loc) KEEP INDEX;
启用非校验:
ALTER TABLE dept ENABLE CONSTRAINT dname_ukey;
ALTER TABLE dept ENABLE NOVALIDATE PRIMARY KEY,
ENABLE NOVALIDATE UNIQUE (dname, loc);
启用校验:
ALTER TABLE dept MODIFY CONSTRAINT dname_ukey VALIDATE;
ALTER TABLE dept MODIFY PRIMARY KEY ENABLE NOVALIDATE;
若要停用/删除相关的FOREIGN KEY约束,则:
ALTER TABLE dept DISABLE PRIMARY KEY CASCADE;
删除:
ALTER TABLE dept DROP UNIQUE (dname, loc);
ALTER TABLE emp DROP PRIMARY KEY KEEP INDEX,
DROP CONSTRAINT dept_fkey;
转自:http://daizj.iteye.com/blog/1724303
summary:
在data migrate时,某些表的约束总是困扰着我们,让我们的migratet举步维艰,如何利用约束本身的属性来处理这些问题呢?本文详细介绍了约束的四对属性:
Deferrable/not deferrable,
Deferred/immediate,
enalbe/disable,
validate/novalidate,
以及如何应用这些属性灵活应对相关问题
是否要求满足约束
------------------------------------------------------------------------------------
Enable
Disable
Validate确保已有数据符合约束;
Novalidate不必考虑已有数据是否符合约束。
除非Novalidate被指定,Enable默认Validate;
除非Validate被指定,Disable默认Novalidate;
Enable Validate与Enable相同,检查已有记录和新增记录,确保都符合约束;
Enable Novalidate 允许已有记录不必满足约束条件,但新增/修改的记录必须满足;
Disable Validate禁用约束,删除约束上的索引,不允许修改任何被约束的记录;
Disable Novalidate与Disable相同,禁用约束,删除约束上的索引,且允许修改被约束的记录。
1.
1)
2)
a.
b.
3)
4)
2.
1)
2)
3)
CREATE TABLE games
(scores NUMBER, CONSTRAINT unq_num UNIQUE (scores)
INITIALLY DEFERRED DEFERRABLE);
insert into games values(1);
insert into games values(1);
commit;--在此报错
You will not get a error util you commit it;
SET CONSTRAINT(s) unq_num immediate;--修改属性
insert into games values(2);
insert into games values(2);--在此报错
commit;
You will get a error when you execute the second sql;
3.
1)
create table t(id number);
insert into t values(1);
insert into t values(2);
alter table t add constraint ch_100 check(id>=100);
alter table t add constraint ch_100 check(id>=100) novalidate;--成功
2)
drop table t;
create table t(id number);
insert into t values(1);
insert into t values(1);
alter table t add constraint ch_100 unique(id) ; --报错
alter table t add constraint ch_100 unique(id) novalidate; --报错
alter table t add constraint ch_100 unique(id)
4.
1)
ALTER TABLE dept DISABLE CONSTRAINT dname_ukey;
ALTER TABLE dept ENABLE CONSTRAINT dname_ukey;
alter table t add constraint ch_100 unique(id) disable;
2)
Disable非deferrable 的pk、uk,将删除相应的索引(除非指定了keep index,但是keep下来的索引是唯一性的,insert数据时还是要作唯一性检查的),在enable时重建索引.
Disbale
3)
KEEP INDEX要注意的:
5.
或者
如何利用EXCEPTIONS来处理实践中的一些问题:
(1)在已有的constraint上加载数据
(2)新建constraint,原有的数据违反了这个约束
6.
定义约束
CREATE TABLE dept_20
ALTER TABLE dept_20
CREATE TABLE divisions
修改或删除现有的约束
延迟校验的启用停用:
停用:
启用非校验:
启用校验:
若要停用/删除相关的FOREIGN KEY约束,则:
删除:
转自:http://daizj.iteye.com/blog/1724303