我的Oracle 9i學習日志（18）-- 維護資料完整性.b

建立禁止性限制：

•加disable關鍵字即可。

SQL> create table t(id int, name char(10));

表已建立。

SQL> desc t

名稱是否為空? 類型

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

ID NUMBER(38)

NAME CHAR(10)

SQL> alter table t add constraint uq_t_id unique(id) disable;

表已更改。

SQL> insert into t values(0,'aaa');

已建立 1 行。

SQL>

SQL> select * from t;

ID NAME

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

0 aaa

啟用限制：

SQL> delete from t;

已删除4行。

SQL> alter table t enable unique;

alter table t enable unique

ERROR 位于第 1 行:

ORA-00906: missing left parenthesis

SQL> alter table t enable unique(id);

insert into t values(0,'aaa')

ORA-00001: unique constraint (LUO.UQ_T_ID) violated

SQL> select object_name,object_type from user_objects;

OBJECT_NAME OBJECT_TYPE

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

BONUS TABLE

CUSTOMERS TABLE

DINGDAN TABLE

ORDERS TABLE

PK_CUST INDEX

T TABLE

UQ_EMP_ID INDEX

UQ_T_ID INDEX

已選擇8行。

SQL> drop table t;

表已丢棄。

SQL> alter table t add constraint pk_t_id primary key(id) disable;

已選擇7行。

SQL> insert into t values(1,'aaa');

SQL> insert into t values(1,'bbb');

SQL> commit;

送出完成。

1 aaa

1 bbb

SQL> delete from t where name='bbb';

已删除 1 行。

SQL> commit ;

SQL> alter table t enable primary key; #啟用主鍵限制可以不用帶列名。

insert into t values(1,'bbb')

ORA-00001: unique constraint (LUO.PK_T_ID) violated

SQL> insert into t values(2,'bbb');

2 bbb

ORDERS TABLE

PK_T_ID INDEX

SQL> alter table t disable primary key; #再次禁用。

BONUS TABLE

已選擇7行。 #索引消失。

删除限制：

Alter table bonus drop constraint ck_bonus;

删除unique或主鍵限制時，如果是被引用為外鍵的則帶上cascade參數，删除unique限制同時删除了子表的外鍵限制。

Alter table bonus drop primary key cascade;

實驗：

SQL> select constraint_name,status,validated from user_constraints;

CONSTRAINT_NAME STATUS VALIDATED

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

SYS_C002763 ENABLED VALIDATED

CK_BONUS ENABLED VALIDATED

CK_BONUS2 ENABLED VALIDATED

UQ_EMP_ID ENABLED VALIDATED

PK_CUST ENABLED VALIDATED

FK_CUST ENABLED VALIDATED

CK_DPTID ENABLED VALIDATED

SQL> alter table bonus drop constraint ck_bonus2;

CONSTRAINT_NAME STATUS VALIDATED

FK_CUST ENABLED VALIDATED

已選擇6行。

SQL> alter table customers drop primary key cascade;

限制狀态：

圖5

可以啟用(ENABLE) 或禁用(DISABLE) 完整性限制。如果啟用某個限制，則在資料庫中輸入或更新資料時，就會對資料進行檢查。禁止輸入不符合限制規則的資料。如果禁用某個限制，則可以在資料庫中輸入不符合限制規則的資料。完整性限制可處于以下狀态之一：

• DISABLE NOVALIDATE

• DISABLE VALIDATE

• ENABLE NOVALIDATE

• ENABLE VALIDATE

DISABLE NOVALIDATE：不檢查處于DISABLE NOVALIDATE 狀态的限制。表中的資料（包括輸入或更新的新資料）可以不符合限制所定義的規則。

DISABLE VALIDATE：當限制處于此狀态時，不允許對受限制的列進行任何修改。另外，限制上的索引将被删除并且禁用限制。注：如果限制可延遲，則不删除索引。

ENABLE NOVALIDATE：如果限制處于此狀态，則不能輸入違反限制的新資料。但是，表可能包含無效的資料，即資料違反限制。啟用處于NOVALIDATE 狀态的限制對正在上載有效OLTP 資料的資料倉庫配置是非常有用的。

ENABLE VALIDATE：如果限制處于此狀态，則不能将違反限制的行插入到表中。但是，禁用該限制時，可以插入此類行。此類行稱為該限制的例外。如果限制處于ENABLE NOVALIDATE 狀态，則在禁用限制時輸入的資料所引起的違反情況仍然存在。要将限制置于已驗證狀态，必須更新或删除違反限制的行。

當某一限制由禁用狀态更改為ENABLE VALIDATE 時，将鎖定表并對表中的所有資料進行一緻性檢查。這可能會引起DML 操作（如等待資料加載），是以，建議先從禁用狀态轉為ENABLE NOVALIDATE，然後再轉為ENABLE VALIDATE。

這些狀态之間的轉換須符合以下規則：

• 除非指定NOVALIDATE，否則ENABLE 表示VALIDATE。

• 除非指定VALIDATE，否則DISABLE 表示NOVALIDATE。

• VALIDATE 和NOVALIDATE 沒有預設的ENABLE 和DISABLE 狀态。

• 當唯一鍵或主鍵從DISABLE 狀态轉為ENABLE 狀态且沒有現有索引時，将自動建立唯一索引。（如果索引可延遲，則将存在異常。）與此類似，當唯一鍵或主鍵從ENABLE 轉為DISABLE 且是使用唯一索引啟用時，則删除該唯一索引。

• 當任何限制從NOVALIDATE 狀态轉為VALIDATE 狀态時，必須檢查所有的資料。但是，從VALIDATE 轉為NOVALIDATE 時，将忽略資料已經過檢查這一事實。

• 将單個限制從ENABLE NOVALIDATE 狀态轉為ENABLE VALIDATE 狀态時，并不禁止使用讀取、寫入或其它DDL 語句。

SQL> select constraint_name,table_name,status,validated from user_constraints;

CONSTRAINT_NAME TABLE_NAME STATUS VALIDATED

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

CK_BONUS BONUS ENABLED VALIDATED

UQ_EMP_ID BONUS ENABLED VALIDATED

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

DPT_ID NUMBER(38)

SQL> alter table t add constraint ck_dptid check(dpt_id>=10) disable;

SQL> insert into t values(1,'aaa ',1);

SQL> insert into t values(2,'bbb',2);

SQL> insert into t values(3,'ccc',3);

ID NAME DPT_ID

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

1 aaa 1

2 bbb 2

3 ccc 3

SQL> alter table t enable novalidate constraint ck_dptid;

CK_BONUS BONUS ENABLED VALIDATED

CK_DPTID T ENABLED NOT VALIDATED

SQL> insert into t values(3,'ddd',4);

insert into t values(3,'ddd',4)

ORA-02290: check constraint (LUO.CK_DPTID) violated

SQL> insert into t values(3,'ddd',11);

#啟用enable novalidate狀态成功。

SQL> select constraint_name,table_name,status,validated from user_constrai

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

UQ_EMP_ID BONUS ENABLED VALIDATED

PK_T_ID T DISABLED NOT VALIDATED

1 aaa 1

3 ccc 3

3 ddd 11

SQL> alter table t enable novalidate primary key;

alter table t enable novalidate primary key

ORA-02437: cannot validate (LUO.PK_T_ID) - primary key violated

#報錯的原因是因為啟用主鍵限制後會建立一個非唯一性索引，但表中有重複值，索引建立不成功。解決方法可以删除表中主鍵列的重複值，或用下面的方法。

SQL> create index t_id on t(id);

索引已建立。

T_ID INDEX

UQ_EMP_ID INDEX

SQL> insert into t values(2,'eee',12);

insert into t values(2,'eee',12)

SQL> insert into t values(4,'eee',12);

當有大量資料處理時，一緻性限制同索引一樣會降低資料庫性能。是以，可以按以下方法提高性能。

圖6

限制檢查時間：

圖7

在限制定義完成以後，nondeferred與deferred不能互相轉變。

SET CONSTRAINTS 語句用于将特定事務處理的限制設定為DEFERRED 或IMMEDIATE。

可以使用此語句設定限制名稱清單或限制的模式。SET CONSTRAINTS 模式将一直持續到事務處理完成或者另一個SET CONSTRAINTS 語句重置模式。SET CONSTRAINTS 語句不允許在觸發器内部使用。

ALTER SESSION 語句還包含将限制設定為IMMEDIATE 或DEFERRED 的子句SET CONSTRAINTS。此指令預設為設定所有(ALL) 可延遲的限制（不能指定限制名稱清單）。

ALTER SESSION SET CONSTRAINTS 語句僅适用于目前的會話。

ALTER SESSION

SET CONSTRAINT[S] =

{IMMEDIATE|DEFERRED|DEFAULT}

SET CONSTRAINT | CONSTRAINTS

{constraint |ALL }

{IMMEDIATE|DEFERRED}

SQL> create table t(id int,name char(10),dpt_id int);

SQL> desc t;

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

SQL> alter table t add constraint pk_t_id primary key(id);

SQL> select index_name,uniqueness from user_indexes;

INDEX_NAME UNIQUENESS

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

PK_T_ID UNIQUE

SQL> alter table t drop primary key;

SQL> alter table t add constraint pk_t_id primary key(id) deferrable;

PK_T_ID NONUNIQUE

SQL> alter table t disable primary key;

#如果索引是非唯一性索引，在disable主鍵限制時，不會删除相應索引。

SQL> insert into t values(1,'aaa',1);

PK_T_ID T ENABLED NOT VALIDATED

insert into t values(1,'aaa',1)

SQL> set constraints all deferred;

限制條件已設定。

1 aaa 1

commit

ORA-02091: transaction rolled back

本文轉自 d185740815 51CTO部落格，原文連結：http://blog.51cto.com/luotaoyang/293804，如需轉載請自行聯系原作者

我的Oracle 9i學習日志（18）-- 維護資料完整性.b

繼續閱讀

ASP程式設計中20個非常有用的例子

龍珠訓練營task04

阿裡雲天池龍珠計劃SQL訓練營打卡

阿裡雲天池龍珠計劃SQL訓練營day1

賦予使用者dba權限--解釋以上：

實驗樓sql進階之成績管理系統的資料操作(window)

Oracle的基本操作

為什麼要選擇UniDAC

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

windows不能在本地計算機上運作oracleDbConsoleorcl

關于SQL語言

SQL語言基礎：常用的資料查詢語句

neo4j之cypher使用文檔

sqlServer根據經緯查距離

Oracle 批量查詢傳入List 傳回List