Oracle約束

一、約束的種類

not null：非空約束

unique：唯一約束，需要索引支援，沒有索引會自動建立，但列允許多個NULL，NULL不在其索引中

primary key：主鍵約束，實質是unique + not null，需要索引支援，沒有索引會自動建立

foreign key：外來鍵約束，定義在子表上，對應父表的主鍵，允許多個NULL

check：規則檢查約束，規則是值為true或false的表示式，可以是字面值、其他列、部分函式，但不能是sysdate等函式，不能用子查詢

約束型別

二、約束的定義

建立表時定義

create table dept(

deptno number(2,0)

constraint pk_dept_deptno primary key

constraint ck_dept_deptno check(deptno between 10 and 90),

Dname varchar2(20)

constraint nn_dept_dname not null);

create table emp(

empno number(4,0)

constraint pk_emp_empno primary key,

ename varchar2(20)

constraint nn_emp_ename not null,

mgr number(4,0)

constraint fk_emp_mgr references emp(empno),

dob date,

hiredate date,

deptno number(2,0)

constraint fk_emp_deptno references dept(deptno) on delete set null,

email varchar2(30)

constraint uk_emp_email unique,

constraint ck_emp_hiredate check(hiredate>=dob+365*16),

constraint ck_emp_email check((instr(email,'@')>0) and (instr(email,'.')>0)));

建立表後使用alter table命令新增約束
新增主鍵約束
alter table emp add constraint pk_emp primary key(empno);
新增外來鍵約束
alter table sales add constraint fk_prod_id foreign key(prod_id) references products(prod_id);
新增非空約束
alter table customers modify customer_name constraint nn_cust_name not null;
如果直接使用系統預設定義的約束名，則可簡化為
alter table customers modify customer_name not null;
新增唯一約束
alter table c_material_t add constraint uk_material_no unique(material_no);
新增check約束
alter table cmes.c_material_t add constraint ck_validaterule check(validaterule not like '%*.%');

具有主外來鍵約束關係的父子表，刪除主錶行將引發錯誤，處理辦法：

1、外來鍵約束定義為on delete cascade，則父錶行刪除時，子表匹配行自動刪除

2、外來鍵約束定義為on delete set null，則父錶行刪除時，子表匹配行外來鍵列自動置空

unique和primary key約束需要索引，如果不存在會自動建立，因此最好在定義約束之前就先建立好索引，便於主動維護。

利用子查詢建表時，非空約束可以被帶過來，其它約束不行，以下舉例說明：

建立四張表

create table customers(cust_id number, country_id number);

create table products(prod_id number(6), prod_name varchar2(50));

create table times(time_id date, day_name varchar2(9));

create table sales(prod_id number, cust_id number, time_id date, channel_id number);

新增約束

alter table customers add constraint pk_cust_id primary key(cust_id);

alter table customers modify country_id constraint nn_country_id not null;

alter table products add constraint pk_prod_id primary key(prod_id);

alter table products modify prod_name constraint nn_prod_name not null;

alter table times add constraint pk_time_id primary key(time_id);

alter table times modify day_name constraints nn_day_name not null;

alter table sales add constraint fk_prod_id foreign key (prod_id) references products(prod_id);

alter table sales add constraint fk_cust_id foreign key (cust_id) references customers(cust_id);

alter table sales add constraint fk_time_id foreign key (time_id) references times(time_id);

alter table sales modify channel_id constraint nn_channel_id not null;

建立新表

create table new_sales(prod_id, cust_id, order_date default sysdate, channel_id)

as select prod_id, cust_id, time_id, channel_id from sales;

檢視新表約束，非空約束系統已定義，其它約束沒有

col constraint_name for a20

col table_name for a20

col column_name for a20

col r_constraint_name for a20

select constraint_name, constraint_type, table_name, column_name, r_constraint_name, status from user_constraints natural join user_cons_columns where table_name = 'NEW_SALES';

CONSTRAINT_NAME      C TABLE_NAME           COLUMN_NAME          R_CONSTRAINT_NAME    STATUS

-------------------- - -------------------- -------------------- -------------------- --------

SYS_C0019014         C NEW_SALES            CHANNEL_ID                                ENABLED

三、檢視約束

約束的查詢主要透過兩個資料字典dba_constraints（user_constriants）、dba_cons_columns（user_cons_columns）。

檢視錶包含的約束

col owner for a10

col constraint_name for a20

col table_name for a20

col r_constraint_name for a20

select owner, constraint_name, constraint_type, table_name, r_constraint_name, status from dba_constraints where owner = 'SCOTT' and table_name = 'EMP';

OWNER      CONSTRAINT_NAME      CONSTRAINT_TYPE TABLE_NAME           R_CONSTRAINT_NAME    STATUS

---------- -------------------- --------------- -------------------- -------------------- --------

SCOTT      PK_EMP               P               EMP                                       ENABLED

SCOTT      FK_DEPTNO            R               EMP                  PK_DEPT              ENABLED

檢視錶的約束對應的列

col constraint_name for a20

col table_name for a20

col column_name for a20

col r_constraint_name for a20

select owner, constraint_name, constraint_type, table_name, column_name, r_constraint_name, status from dba_constraints natural join dba_cons_columns where owner = 'SCOTT' and table_name = 'EMP';

OWNER      CONSTRAINT_NAME      C TABLE_NAME           COLUMN_NAME          R_CONSTRAINT_NAME    STATUS

---------- -------------------- - -------------------- -------------------- -------------------- --------

SCOTT      PK_EMP               P EMP                  EMPNO                                     ENABLED

SCOTT      FK_DEPTNO            R EMP                  DEPTNO               PK_DEPT              ENABLED

四、約束的狀態轉化和延遲檢查

約束的狀態有兩類

啟用狀態：啟用或禁用

驗證狀態：驗證或不驗證

啟用狀態和驗證狀態的幾種可能的組合

enable validate：無法輸入違反約束的行，表中的所有行都符合約束

disable novalidate：可以輸入任何資料，表中可能存在不符合約束要求的資料

enable novalidate：表中可以存在不符合約束要求的資料，但現在輸入的行必須符合要求

disable validate：可以有該狀態，但沒有意義，DML操作報錯，不能對帶有禁用和驗證約束條件的表進行插入/更新/刪除

約束的狀態可以透過修改約束命令來轉化

insert時可先禁用約束檢查，提高資料插入效率，之後再統一修改

alter table <table_name> modify constraint <constraint_name> disable novalidate;

或者

alter table <table_name> disable novalidate constraint <constraint_name>;

執行insert後啟用約束，但不驗證

alter table <table_name> modify constraint <constraint_name> enable novalidate;

執行update後開啟驗證

alter table <table_name> modify constraint <constraint_name> enable validate;

如果是主外來鍵約束的關係，在解除主鍵約束的同時，可以透過cascade級聯解除外來鍵約束，如

alter table emp disable constraint pk_empno cascade;

但如果恢復，則以下只能恢復主鍵約束，外來鍵約束需要另外手動恢復

alter table emp enable constraint pk_empno;

約束的狀態轉化是全域性的，對所有會話有效。

 

約束的延遲檢查

immediate：執行語句時檢查，預設，不延遲

set constraint <constraint_name> immediate;

deferred：延遲約束，在事務提交時檢查

set constraint <constraint_name> deferred;

切換可延遲約束是針對會話而特有的，不是全域性的。

 

要使用延遲的約束，必須用適當的方法建立，指明可延遲

alter table <table_name> add constraint <constraint_name> check(<check_condition>) deferrable initially immediate;

延遲約束通常用於外來鍵，防止程式在更新父子表時因處理順序不當導致的程式操作失敗。

五、約束的維護

更改約束名

alter table rename constraint  to ;

刪除約束

alter table drop constraint ;

alter table drop primary key cascade;

透過異常表找到違規資料

建表及其約束，插入異常資料，使用異常表排除異常：

建立test表

create table test(c1 number, c2 char(10));

插入正常資料

insert into test values(1, 'aaa');

insert into test values(2, 'bbb');

為test表新增一個unique約束un_c2在c2欄位上

alter table test add constraint un_c2 unique(c2);

插入重複資料

insert into test values(3, 'aaa');

此時報錯

ORA-00001: 違反唯一約束條件 (SYS.UN_C2)

臨時禁用約束un_c2

alter table test modify constraint un_c2 disable;

插入重複資料

insert into test values(3, 'aaa');

commit;

建立exceptions表

@?\rdbms\admin\utlexcpt.sql

恢復約束檢查，異常記錄登記到異常表

alter table test enable constraint un_c2 exceptions into exceptions;

此時報錯

ORA-02299: 無法驗證 (SYS.UN_C2) - 找到重複關鍵字

查詢一下異常表，已經有異常記錄

select * from exceptions;

ROW_ID             OWNER                          TABLE_NAME                     CONSTRAINT

------------------ ------------------------------ ------------------------------ ------------------------------

AAAS1PAABAAAWDBAAC SYS                            TEST                           UN_C2

AAAS1PAABAAAWDBAAA SYS                            TEST                           UN_C2

找出這些異常記錄

select rowid, c1, c2 from test where rowid in (select row_id from exceptions);

ROWID                      C1 C2

------------------ ---------- ----------

AAAS1PAABAAAWDBAAA          1 aaa

AAAS1PAABAAAWDBAAC          3 aaa

更新到正確的資料

update test set c2='ccc' where rowid='AAAS1PAABAAAWDBAAC';

commit;

恢復約束檢查

alter table test enable constraint un_c2;

清理異常表

truncate table exceptions;