Oracle資料庫開發——表（資料完整性約束）
建立表時候，都要考慮到資料完整性，所以先提一下，資料完整性約束：
主鍵約束(primary key)、唯一性約束(unique)、檢查約束(check)、外來鍵約束(foreign key)、非空約束(not null)。
主鍵約束(primary key)：主鍵可以確保一個表中沒有重複的鍵值資料行。作為主鍵的列或者列的組合，其值必須唯一，且不能為NULL。
一個表只能定義一個主鍵約束，同時Oracle Database自動為主鍵列建立一個唯一性索引，使用者可以為該索引指定儲存位置和儲存引數。
主鍵約束可以定義在列級，也可以定義在表級。
由多列組成的主鍵叫做複合主鍵，一個複合主鍵中的列的數量不能超過32個。
唯一性約束(unique)：確保列或列的組合具有唯一值(若沒有非空約束，這個值是可以為空的)，Oracle Database自動為主鍵列建立一個唯一性索引，使用者可以為該索引指定儲存位置和儲存引數。
唯一性約束可以定義在列級，也可以定義在表級。
檢查約束(check)：檢查約束用來限制列的取值範圍，實現對資料的自動檢查。
一個列可以定義多個檢查約束，其表示式中必須引用相應的列，且表示式中不能包含子查詢，SYSDATE/USER等SQL函式和ROWID、ROWNUM等偽劣。
檢查約束可以定義在列級或表級。
外來鍵約束(foreign key)：外來鍵約束的定義使得資料庫中表與表之間建立了父子關係。外來鍵約束用來定義子表中列的取值只能是父表中參照列的值，或者為空。
父表中被參照的列必須有唯一性約束或主鍵約束，外來鍵約束可以定義在一列或多列組合上，可以定義在列級或表級。
外來鍵可以是自參照約束，即外來鍵可以指向同一個表。
非空約束(not null)：非空約束限制列的取值不能為NULL，一個表中可以定義多個非空約束。非空約束只能定義在列級。
例1：驗證列主鍵系統自動建立唯一索引
建立一個訂單表，訂單號為主鍵：
create table tab_orders
( order_id varchar2(20) constraint O_PK primary key
,order_date date default sysdate
,qty integer
,payterms varchar2(12)
,goods_id number(6)
);
檢視系統是否為主鍵列自動建立了唯一索引?
SQL> select t.index_name,t.index_type,t.table_name,t.uniqueness from user_indexes t where index_name = 'O_PK';
INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENESS
------------------------------ --------------------------- ------------------------------ ----------
O_PK NORMAL TAB_ORDERS UNIQUE
SQL> select x.constraint_name,x.constraint_type,x.index_name from user_constraints x where x.table_name = 'TAB_ORDERS';
CONSTRAINT_NAME CONSTRAINT_TYPE INDEX_NAME
------------------------------ --------------- ------------------------------
O_PK P O_PK
結果顯示系統自動建立了唯一索引。
例2： 表級主鍵——聯合主鍵
上面一個是列級約束，那麼表級約束是怎麼建立的？
其實表級約束通常是針對多個列建立的約束，因此，表級約束的定義獨立於列的定義。以( ，)形式
create table tab_bookauthors
(author_id varchar2(15)
,book_id number(6)
,author_ord number
,constraint BA_PK primary key (author_id,author_ord)
);
SQL> select t.index_name,t.index_type,t.table_name,t.uniqueness from user_indexes t where index_name = 'BA_PK';
INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENESS
------------------------------ --------------------------- ------------------------------ ----------
BA_PK NORMAL TAB_BOOKAUTHORS UNIQUE
SQL> select x.constraint_name,x.constraint_type,x.index_name from user_constraints x where x.table_name = 'TAB_BOOKAUTHORS';
CONSTRAINT_NAME CONSTRAINT_TYPE INDEX_NAME
------------------------------ --------------- ------------------------------
BA_PK P BA_PK
SQL> select * from user_ind_columns z where z.INDEX_NAME = 'BA_PK';
INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND
------------------------------ ------------------------------ --------------- --------------- ------------- ----------- -------
BA_PK TAB_BOOKAUTHORS AUTHOR_ID 1 15 15 ASC
BA_PK TAB_BOOKAUTHORS AUTHOR_ORD 2 22 0 ASC
中間休息了兩天，下面繼續...
約束的定義：
約束的定義，約束可以在建立表的時候定義，也可以在表建立完整之後，單獨新增。
create table中定義約束的語法：
定義列級約束:
create table [schema].table_name
(
column1 datatype [constraint constraint_name]
,column2 datatype [constraint constraint_name]
,...
);
帶有定義表級約束的:
create table [schema].table_name
(
column1 datatype [constraint constraint_name]
,column2 datatype [constraint constraint_name]
,...
,[constraint constraint_name] constraint_type ([column1,column2,...]|[condition])
);
先建立表,然後新增約束
create table [schema].table_name(...);
alter table [schema].table_name add constraint constraint_name constraint_type (column1,column2)[condition];
個人感覺上先建立表，然後去新增更好一些，感覺比較清晰吧= =#。
習慣上，NOT NULL 、unique、 check 約束直接在建立表的時候定義，然後單獨去定義主外來鍵約束。
還是看看oracle是怎麼儲存的吧
例3：
create table demo1
(col1 varchar2(4) constraint DO1_PK primary key
,col2 varchar2(10) unique
,col3 number not null
)
;
create table demo2
(col1 varchar2(4)
,col2 varchar2(10)
,col3 number not null
);
alter table demo2 add constraint DO2_PK primary key(col1);
alter table demo2 add constraint DO2_UI unique(col2);
用PL/SQL檢視建表語句
-- Create table
create table DEMO1
(
COL1 VARCHAR2(4) not null,
COL2 VARCHAR2(10),
COL3 NUMBER not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255;
-- Create/Recreate primary, unique and foreign key constraints
alter table DEMO1
add constraint DO1_PK primary key (COL1)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255;
alter table DEMO1
add unique (COL2)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255;
==================================================================
-- Create table
create table DEMO2
(
COL1 VARCHAR2(4) not null,
COL2 VARCHAR2(10),
COL3 NUMBER not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255;
-- Create/Recreate primary, unique and foreign key constraints
alter table DEMO2
add constraint DO2_PK primary key (COL1)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255;
alter table DEMO2
add constraint DO2_UI unique (COL2)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255;
這裡可以看到表demo1在PL/SQL裡面儲存的語句被拆分開了
在檢視下使用者檢視
SQL> select x.table_name,x.constraint_name,x.constraint_type,x.index_name from user_constraints x where x.table_name in ('DEMO1','DEMO2');
TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE INDEX_NAME
------------------------------ ------------------------------ --------------- ------------------------------
DEMO1 SYS_C003893 C
DEMO1 DO1_PK P DO1_PK
DEMO1 SYS_C003895 U SYS_C003895
DEMO2 SYS_C003891 C
DEMO2 DO2_PK P DO2_PK
DEMO2 DO2_UI U DO2_UI
這裡第二列SYS開頭的是系統定義唯一標示名，因為使用者沒有定義約束名。最後一列SYS開頭是索引名唯一標示名。
看到這裡，我的感覺是雖然建立方式不同，但是oracle儲存方式是相同的。
通常先create table，再alter add約束，使用者會給約束起名字(constraint)，需要系統建立唯一索引的約束，索引名與約束名相同。
直接建立的約束，有時候為了建表語句書寫的簡便(或者使用者忽略忘記了)，新增約束名，系統會預設分給約束一個唯一標示名，需要系統建立唯一索引的約束，索引名與約束名相同。
以上是個人觀點，有不對的地方請指正。
新增約束
例4:
create table employees
(emp_id char(10)
,ename varchar2(20)
,job_id number(6)
,job_lv integer
,pub_id char(4)
,hiredate date
);
create table jobs
(jobid number(6) primary key
,jobname varchar2(20) not null
,minlvl integer
,maxlvl integer
);
--為employee的emp_id新增一個主鍵約束
alter table employees add constraint E_PK primary key(emp_id);
--為employee的job_id新增外來鍵，參照表為jobs的job_id
alter table employees add constraint E_FK foreign key(job_id) references jobs(jobid) on delete cascade;
--為employee的ename新增非空約束
alter table employees modify ename constraint E_NN not null;
注意:當為表新增非空約束時，必須使用modify子句，而不是add子句。
刪除約束
--刪除employees的主鍵約束
alter table employees drop constraint E_PK; --指定約束名刪除約束
--也可以用
alter table employees drop primary key; --指定約束內容刪除約束
注意,刪除主鍵約束、唯一約束的同時系統為自動刪除對應的唯一性索引。如果想保留唯一性索引,可以使用keep index子句。
alter table employees drop constraint E_PK keep index;
如果主鍵被其他外來鍵參照，可以使用cascade關鍵字指出刪除主鍵約束的同時把參照該主鍵的外來鍵一起刪除。
alter table employees drop constraint E_PK cascade;
下面來驗證
例5:
接著例4，先檢視EMPLOYEES，JOBS的約束檢視
SQL> select x.table_name,x.constraint_name,x.constraint_type,x.index_name from user_constraints x where x.table_name in ('EMPLOYEES','JOBS');
TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE INDEX_NAME
------------------------------ ------------------------------ --------------- ------------------------------
EMPLOYEES E_PK P E_PK
EMPLOYEES E_FK R
EMPLOYEES E_NN C
JOBS SYS_C003897 C
JOBS SYS_C003898 P SYS_C003898
SQL> select z.table_name,z.index_type,z.table_name,z.uniqueness from user_indexes z where z.table_name in ('EMPLOYEES','JOBS');
TABLE_NAME INDEX_TYPE TABLE_NAME UNIQUENESS
------------------------------ --------------------------- ------------------------------ ----------
JOBS NORMAL JOBS UNIQUE
EMPLOYEES NORMAL EMPLOYEES UNIQUE
然後刪除EMPLOYEES的主鍵
alter table employees drop constraint E_PK;
SQL> select x.table_name,x.constraint_name,x.constraint_type,x.index_name from user_constraints x where x.table_name in ('EMPLOYEES','JOBS');
TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE INDEX_NAME
------------------------------ ------------------------------ --------------- ------------------------------
EMPLOYEES E_FK R
EMPLOYEES E_NN C
JOBS SYS_C003897 C
JOBS SYS_C003898 P SYS_C003898
SQL> select z.table_name,z.index_type,z.table_name,z.uniqueness from user_indexes z where z.table_name in ('EMPLOYEES','JOBS');
TABLE_NAME INDEX_TYPE TABLE_NAME UNIQUENESS
------------------------------ --------------------------- ------------------------------ ----------
JOBS NORMAL JOBS UNIQUE
結果可以看到constraint E_PK 對應的唯一索引被刪除了。
下面刪除jobs的主鍵jobid，表employees的外來鍵參照該列
SQL> alter table jobs drop primary key cascade;
Table altered
SQL> select x.table_name,x.constraint_name,x.constraint_type,x.index_name from user_constraints x where x.table_name in ('EMPLOYEES','JOBS');
TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE INDEX_NAME
------------------------------ ------------------------------ --------------- ------------------------------
EMPLOYEES E_NN C
JOBS SYS_C003897 C
SQL> select z.table_name,z.index_type,z.table_name,z.uniqueness from user_indexes z where z.table_name in ('EMPLOYEES','JOBS');
TABLE_NAME INDEX_TYPE TABLE_NAME UNIQUENESS
------------------------------ --------------------------- ------------------------------ ----------
結果驗證了，如果主鍵被其他外來鍵參照，使用cascade關鍵字指出刪除主鍵約束的同時把參照該主鍵的外來鍵一起刪除。
約束的狀態和延遲檢查
(1)約束的狀態
Oracle資料庫表中的約束出於啟用狀態，即約束對錶的插入或更新操作進行檢驗，不符合約束的操作被回退。但是，實際工作中，我們有時候
需要將約束的狀態置為禁用，這樣可以提高效率。
使用 alter table ... disable ...語句
語法: alter table [schema.]table_name
modify constraint constraint_name {enable|disable};
需要注意的幾點:
1.禁用主鍵使用 alter table ... disable ... cascade;後面的cascade是為了防止主鍵被其他表引用為外來鍵，而導致該主鍵約束無法禁用。
2.禁用primary key 和unique約束時，系統會將它們對應的唯一性索引刪除，並且，在重新啟用時，系統重新建立相應的唯一性索引。
如果希望保留唯一性索引，可以在禁用時候，使用keep index子句。
(2)約束的檢查狀態
約束的啟用和禁用狀態只對設定狀態後的資料操作起作用，要想對錶中已存在的資料也進行約束檢查，則必須結合"檢查狀態"——validate(檢查)
和novaildate(非檢查)。
語法:alter table [schema.]table_name
modify constraint constraint_name validate|novalidate;
結合，啟用 和 禁用:
組合一下
enable validate :啟用檢查狀態，Oracle資料庫預設的檢查狀態。對錶所有都進行約束條件檢查。
enable novalidate : 啟用非檢查狀態，只對更新或新插入的資料進行約束檢查，不對已存在資料進行約束檢查。
disable validate :禁用檢查狀態，在該狀態下Oralce資料庫不允許使用者進行更新或者插入操作。(因為約束已禁用卻又要求檢查，無法檢查)
disable novalidate : 禁用非檢查狀態，不對資料進行約束檢查。
(3)約束的延遲檢查
立即約束檢查 initially immediate: 預設為該選項，執行外一個DML語句後立即檢驗
延遲約束檢查 initially deferred: 當 事務提交或呼叫set constraint immediate語句時才檢驗。
主鍵約束(primary key)、唯一性約束(unique)、檢查約束(check)、外來鍵約束(foreign key)、非空約束(not null)。
主鍵約束(primary key)：主鍵可以確保一個表中沒有重複的鍵值資料行。作為主鍵的列或者列的組合，其值必須唯一，且不能為NULL。
一個表只能定義一個主鍵約束，同時Oracle Database自動為主鍵列建立一個唯一性索引，使用者可以為該索引指定儲存位置和儲存引數。
主鍵約束可以定義在列級，也可以定義在表級。
由多列組成的主鍵叫做複合主鍵，一個複合主鍵中的列的數量不能超過32個。
唯一性約束(unique)：確保列或列的組合具有唯一值(若沒有非空約束，這個值是可以為空的)，Oracle Database自動為主鍵列建立一個唯一性索引，使用者可以為該索引指定儲存位置和儲存引數。
唯一性約束可以定義在列級，也可以定義在表級。
檢查約束(check)：檢查約束用來限制列的取值範圍，實現對資料的自動檢查。
一個列可以定義多個檢查約束，其表示式中必須引用相應的列，且表示式中不能包含子查詢，SYSDATE/USER等SQL函式和ROWID、ROWNUM等偽劣。
檢查約束可以定義在列級或表級。
外來鍵約束(foreign key)：外來鍵約束的定義使得資料庫中表與表之間建立了父子關係。外來鍵約束用來定義子表中列的取值只能是父表中參照列的值，或者為空。
父表中被參照的列必須有唯一性約束或主鍵約束，外來鍵約束可以定義在一列或多列組合上，可以定義在列級或表級。
外來鍵可以是自參照約束，即外來鍵可以指向同一個表。
非空約束(not null)：非空約束限制列的取值不能為NULL，一個表中可以定義多個非空約束。非空約束只能定義在列級。
例1：驗證列主鍵系統自動建立唯一索引
建立一個訂單表，訂單號為主鍵：
create table tab_orders
( order_id varchar2(20) constraint O_PK primary key
,order_date date default sysdate
,qty integer
,payterms varchar2(12)
,goods_id number(6)
);
檢視系統是否為主鍵列自動建立了唯一索引?
SQL> select t.index_name,t.index_type,t.table_name,t.uniqueness from user_indexes t where index_name = 'O_PK';
INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENESS
------------------------------ --------------------------- ------------------------------ ----------
O_PK NORMAL TAB_ORDERS UNIQUE
SQL> select x.constraint_name,x.constraint_type,x.index_name from user_constraints x where x.table_name = 'TAB_ORDERS';
CONSTRAINT_NAME CONSTRAINT_TYPE INDEX_NAME
------------------------------ --------------- ------------------------------
O_PK P O_PK
結果顯示系統自動建立了唯一索引。
例2： 表級主鍵——聯合主鍵
上面一個是列級約束，那麼表級約束是怎麼建立的？
其實表級約束通常是針對多個列建立的約束，因此，表級約束的定義獨立於列的定義。以( ，)形式
create table tab_bookauthors
(author_id varchar2(15)
,book_id number(6)
,author_ord number
,constraint BA_PK primary key (author_id,author_ord)
);
SQL> select t.index_name,t.index_type,t.table_name,t.uniqueness from user_indexes t where index_name = 'BA_PK';
INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENESS
------------------------------ --------------------------- ------------------------------ ----------
BA_PK NORMAL TAB_BOOKAUTHORS UNIQUE
SQL> select x.constraint_name,x.constraint_type,x.index_name from user_constraints x where x.table_name = 'TAB_BOOKAUTHORS';
CONSTRAINT_NAME CONSTRAINT_TYPE INDEX_NAME
------------------------------ --------------- ------------------------------
BA_PK P BA_PK
SQL> select * from user_ind_columns z where z.INDEX_NAME = 'BA_PK';
INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND
------------------------------ ------------------------------ --------------- --------------- ------------- ----------- -------
BA_PK TAB_BOOKAUTHORS AUTHOR_ID 1 15 15 ASC
BA_PK TAB_BOOKAUTHORS AUTHOR_ORD 2 22 0 ASC
中間休息了兩天，下面繼續...
約束的定義：
約束的定義，約束可以在建立表的時候定義，也可以在表建立完整之後，單獨新增。
create table中定義約束的語法：
定義列級約束:
create table [schema].table_name
(
column1 datatype [constraint constraint_name]
,column2 datatype [constraint constraint_name]
,...
);
帶有定義表級約束的:
create table [schema].table_name
(
column1 datatype [constraint constraint_name]
,column2 datatype [constraint constraint_name]
,...
,[constraint constraint_name] constraint_type ([column1,column2,...]|[condition])
);
先建立表,然後新增約束
create table [schema].table_name(...);
alter table [schema].table_name add constraint constraint_name constraint_type (column1,column2)[condition];
個人感覺上先建立表，然後去新增更好一些，感覺比較清晰吧= =#。
習慣上，NOT NULL 、unique、 check 約束直接在建立表的時候定義，然後單獨去定義主外來鍵約束。
還是看看oracle是怎麼儲存的吧
例3：
create table demo1
(col1 varchar2(4) constraint DO1_PK primary key
,col2 varchar2(10) unique
,col3 number not null
)
;
create table demo2
(col1 varchar2(4)
,col2 varchar2(10)
,col3 number not null
);
alter table demo2 add constraint DO2_PK primary key(col1);
alter table demo2 add constraint DO2_UI unique(col2);
用PL/SQL檢視建表語句
-- Create table
create table DEMO1
(
COL1 VARCHAR2(4) not null,
COL2 VARCHAR2(10),
COL3 NUMBER not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255;
-- Create/Recreate primary, unique and foreign key constraints
alter table DEMO1
add constraint DO1_PK primary key (COL1)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255;
alter table DEMO1
add unique (COL2)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255;
==================================================================
-- Create table
create table DEMO2
(
COL1 VARCHAR2(4) not null,
COL2 VARCHAR2(10),
COL3 NUMBER not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255;
-- Create/Recreate primary, unique and foreign key constraints
alter table DEMO2
add constraint DO2_PK primary key (COL1)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255;
alter table DEMO2
add constraint DO2_UI unique (COL2)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255;
這裡可以看到表demo1在PL/SQL裡面儲存的語句被拆分開了
在檢視下使用者檢視
SQL> select x.table_name,x.constraint_name,x.constraint_type,x.index_name from user_constraints x where x.table_name in ('DEMO1','DEMO2');
TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE INDEX_NAME
------------------------------ ------------------------------ --------------- ------------------------------
DEMO1 SYS_C003893 C
DEMO1 DO1_PK P DO1_PK
DEMO1 SYS_C003895 U SYS_C003895
DEMO2 SYS_C003891 C
DEMO2 DO2_PK P DO2_PK
DEMO2 DO2_UI U DO2_UI
這裡第二列SYS開頭的是系統定義唯一標示名，因為使用者沒有定義約束名。最後一列SYS開頭是索引名唯一標示名。
看到這裡，我的感覺是雖然建立方式不同，但是oracle儲存方式是相同的。
通常先create table，再alter add約束，使用者會給約束起名字(constraint)，需要系統建立唯一索引的約束，索引名與約束名相同。
直接建立的約束，有時候為了建表語句書寫的簡便(或者使用者忽略忘記了)，新增約束名，系統會預設分給約束一個唯一標示名，需要系統建立唯一索引的約束，索引名與約束名相同。
以上是個人觀點，有不對的地方請指正。
新增約束
例4:
create table employees
(emp_id char(10)
,ename varchar2(20)
,job_id number(6)
,job_lv integer
,pub_id char(4)
,hiredate date
);
create table jobs
(jobid number(6) primary key
,jobname varchar2(20) not null
,minlvl integer
,maxlvl integer
);
--為employee的emp_id新增一個主鍵約束
alter table employees add constraint E_PK primary key(emp_id);
--為employee的job_id新增外來鍵，參照表為jobs的job_id
alter table employees add constraint E_FK foreign key(job_id) references jobs(jobid) on delete cascade;
--為employee的ename新增非空約束
alter table employees modify ename constraint E_NN not null;
注意:當為表新增非空約束時，必須使用modify子句，而不是add子句。
刪除約束
--刪除employees的主鍵約束
alter table employees drop constraint E_PK; --指定約束名刪除約束
--也可以用
alter table employees drop primary key; --指定約束內容刪除約束
注意,刪除主鍵約束、唯一約束的同時系統為自動刪除對應的唯一性索引。如果想保留唯一性索引,可以使用keep index子句。
alter table employees drop constraint E_PK keep index;
如果主鍵被其他外來鍵參照，可以使用cascade關鍵字指出刪除主鍵約束的同時把參照該主鍵的外來鍵一起刪除。
alter table employees drop constraint E_PK cascade;
下面來驗證
例5:
接著例4，先檢視EMPLOYEES，JOBS的約束檢視
SQL> select x.table_name,x.constraint_name,x.constraint_type,x.index_name from user_constraints x where x.table_name in ('EMPLOYEES','JOBS');
TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE INDEX_NAME
------------------------------ ------------------------------ --------------- ------------------------------
EMPLOYEES E_PK P E_PK
EMPLOYEES E_FK R
EMPLOYEES E_NN C
JOBS SYS_C003897 C
JOBS SYS_C003898 P SYS_C003898
SQL> select z.table_name,z.index_type,z.table_name,z.uniqueness from user_indexes z where z.table_name in ('EMPLOYEES','JOBS');
TABLE_NAME INDEX_TYPE TABLE_NAME UNIQUENESS
------------------------------ --------------------------- ------------------------------ ----------
JOBS NORMAL JOBS UNIQUE
EMPLOYEES NORMAL EMPLOYEES UNIQUE
然後刪除EMPLOYEES的主鍵
alter table employees drop constraint E_PK;
SQL> select x.table_name,x.constraint_name,x.constraint_type,x.index_name from user_constraints x where x.table_name in ('EMPLOYEES','JOBS');
TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE INDEX_NAME
------------------------------ ------------------------------ --------------- ------------------------------
EMPLOYEES E_FK R
EMPLOYEES E_NN C
JOBS SYS_C003897 C
JOBS SYS_C003898 P SYS_C003898
SQL> select z.table_name,z.index_type,z.table_name,z.uniqueness from user_indexes z where z.table_name in ('EMPLOYEES','JOBS');
TABLE_NAME INDEX_TYPE TABLE_NAME UNIQUENESS
------------------------------ --------------------------- ------------------------------ ----------
JOBS NORMAL JOBS UNIQUE
結果可以看到constraint E_PK 對應的唯一索引被刪除了。
下面刪除jobs的主鍵jobid，表employees的外來鍵參照該列
SQL> alter table jobs drop primary key cascade;
Table altered
SQL> select x.table_name,x.constraint_name,x.constraint_type,x.index_name from user_constraints x where x.table_name in ('EMPLOYEES','JOBS');
TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE INDEX_NAME
------------------------------ ------------------------------ --------------- ------------------------------
EMPLOYEES E_NN C
JOBS SYS_C003897 C
SQL> select z.table_name,z.index_type,z.table_name,z.uniqueness from user_indexes z where z.table_name in ('EMPLOYEES','JOBS');
TABLE_NAME INDEX_TYPE TABLE_NAME UNIQUENESS
------------------------------ --------------------------- ------------------------------ ----------
結果驗證了，如果主鍵被其他外來鍵參照，使用cascade關鍵字指出刪除主鍵約束的同時把參照該主鍵的外來鍵一起刪除。
約束的狀態和延遲檢查
(1)約束的狀態
Oracle資料庫表中的約束出於啟用狀態，即約束對錶的插入或更新操作進行檢驗，不符合約束的操作被回退。但是，實際工作中，我們有時候
需要將約束的狀態置為禁用，這樣可以提高效率。
使用 alter table ... disable ...語句
語法: alter table [schema.]table_name
modify constraint constraint_name {enable|disable};
需要注意的幾點:
1.禁用主鍵使用 alter table ... disable ... cascade;後面的cascade是為了防止主鍵被其他表引用為外來鍵，而導致該主鍵約束無法禁用。
2.禁用primary key 和unique約束時，系統會將它們對應的唯一性索引刪除，並且，在重新啟用時，系統重新建立相應的唯一性索引。
如果希望保留唯一性索引，可以在禁用時候，使用keep index子句。
(2)約束的檢查狀態
約束的啟用和禁用狀態只對設定狀態後的資料操作起作用，要想對錶中已存在的資料也進行約束檢查，則必須結合"檢查狀態"——validate(檢查)
和novaildate(非檢查)。
語法:alter table [schema.]table_name
modify constraint constraint_name validate|novalidate;
結合，啟用 和 禁用:
組合一下
enable validate :啟用檢查狀態，Oracle資料庫預設的檢查狀態。對錶所有都進行約束條件檢查。
enable novalidate : 啟用非檢查狀態，只對更新或新插入的資料進行約束檢查，不對已存在資料進行約束檢查。
disable validate :禁用檢查狀態，在該狀態下Oralce資料庫不允許使用者進行更新或者插入操作。(因為約束已禁用卻又要求檢查，無法檢查)
disable novalidate : 禁用非檢查狀態，不對資料進行約束檢查。
(3)約束的延遲檢查
立即約束檢查 initially immediate: 預設為該選項，執行外一個DML語句後立即檢驗
延遲約束檢查 initially deferred: 當 事務提交或呼叫set constraint immediate語句時才檢驗。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28929558/viewspace-1144931/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 資料庫系統之實體完整性約束資料庫
- 資料庫常用約束資料庫
- mysql資料庫約束MySql資料庫
- mysql~資料完整性考慮~外來鍵約束MySql
- 表的完整性約束
- 資料庫 資料庫的完整性資料庫
- 資料庫中欄位資料型別以及約束資料庫資料型別
- SQL Server 資料表程式碼建立約束SQLServer
- SQL Server實戰三：資料庫表完整性約束及索引、檢視的建立、編輯與刪除SQLServer資料庫索引
- 資料庫第五章資料庫完整性資料庫
- Oracle資料庫表碎片整理Oracle資料庫
- 【資料庫資料恢復】如何恢復Oracle資料庫truncate表的資料資料庫資料恢復Oracle
- 完整性約束
- 暑期自學 Day 11 | 資料庫 （四）- 約束資料庫
- 資料型別與約束資料型別
- Oracle資料庫管理——表資料庫高水位及shrink操作Oracle資料庫
- [資料庫]資料庫中為什麼不推薦使用外來鍵約束資料庫
- javaWeb-day03-約束、資料庫設計、正規化、資料庫備份JavaWeb資料庫
- Oracle 資料庫整理表碎片Oracle資料庫
- 資料庫的集合，分頁及約束條件資料庫
- MySQL資料庫：6、約束的概述及語法MySql資料庫
- 原創 oracle 資料完整性總結Oracle
- 資料庫建表及索引規約資料庫索引
- MySQL——表的約束，資料型別，增刪查改MySql資料型別
- 資料庫完整性+T-SQL資料庫SQL
- Agile PLM資料庫表結構(Oracle)資料庫Oracle
- 【SQL】Oracle資料庫通過job定期重建同步表資料SQLOracle資料庫
- MySQL之完整性約束MySql
- 資料庫應用系統中的資料庫完整性（上）KP資料庫
- DataX將MySql資料庫資料同步到Oracle資料庫MySql資料庫Oracle
- 資料完整性
- Oracle資料庫開啟NUMA支援Oracle資料庫
- 資料庫資料恢復-oracle資料庫報錯無法開啟的如何恢復資料？資料庫資料恢復Oracle
- Oracle資料庫-----資料庫的基本概念Oracle資料庫
- oracle 備份資料庫，匯出資料庫Oracle資料庫
- oracle資料庫資料字典應用Oracle資料庫
- sqlserver讀取oracle資料庫資料SQLServerOracle資料庫
- Oracle資料庫配置Oracle資料庫
- DataX將Oracle資料庫資料同步到達夢資料庫Oracle資料庫