oracle Constraint[相似 constraint使用方法總結 I]

標籤：style   blog   http   color   os   ar   使用   for   strong   

約束簡單介紹
約束用於確保資料庫資料滿足特定的商業邏輯或者企業規則，假設定義了約束，而且資料不符

合約束，那麼DML操作（INSERT、UPDATE、DELETE）將不能成功運行。約束包含NOT NULL、UNIQUE、PRIMARY KEY、FOREING KEY 以及CHECK等五種類型

定義約束
資料行層級條件約束：
column [CONSTRAINT constraint_name] constraint_type
表級約束：
column ,...,
[CONSTRAINT constraint_name] constraint_type (column,...)

1.定義NOT NULL約束
NOT NULL 約束僅僅能在列級定義，不能在表級定義
例：
CREATE TABLE emp01(
eno INT  NOT NULL,
name VARCHAR2(10) CONSTRAINT nn_name2 NOT NULL,
salary NUMBER(6,2)
);
上例中，eno列與name列上定義了NOT NULL約束，當中eno列的NOT NULL約束沒有指定約束名，而

name列則指定了約束名nn_name。
能夠通過查詢user_constraints字典來查看所定義的約束，如：
SQL> select constraint_name,constraint_type from user_constraints
2  where table_name=‘EMP01‘;

CONSTRAINT_NAME                                              CO
------------------------------------------------------------ --
SYS_C0010618                                                 C
NN_NAME                                                      C
可見，沒有給約束名的，系統將會自己主動的定義一個約束名稱，當中約束類型中，P:表示主鍵，R:

表示外鍵，C表示NOT NULL或CHECK，U表示UNIQUE。且系統中同一方案下的的約束名不能反覆

 

2.定義UNIQUE約束
定義了惟一約束之後，惟一約束列的列值不能反覆，但能夠為NULL。UNIQUE約束既能夠在列級

定義，也能夠在表級定義
例：
CREATE TABLE emp02(
eno INT UNIQUE,name VARCHAR2(10) CONSTRAINT u_name UNIQUE,
salary NUMBER(6,2)
);
相同，假設沒有給出約束名，系統會自己主動定義一個名稱，可從查詢結果得出
SQL> select constraint_name,constraint_type from user_constraints
2  where table_name=‘EMP02‘;

CONSTRAINT_NAME                                              CO
------------------------------------------------------------ --
SYS_C0010623                                                 U
U_NAME
U

3.定義PRIMARY KEY 約束
當定義主鍵約束後，主鍵約束列的列值不僅不能反覆，並且也不能為NULL。主鍵約束既能夠在

列級定義，也能夠在表級定義。一張表最多僅僅能具有一個主鍵約束，當一個表中的多個列都要為

主鍵是，能夠在表級定義。
例1：列級定義主鍵
CREATE TABLE depto04(
dno INT PRIMARY KEY,
dname VARCHAR2(10),loc VARCHAR2(20)
);
通過查詢user_constraints可得出
SQL>  select constraint_name ,constraint_type from user_constraints
2  where table_name=‘DEPT04‘;

CONSTRAINT_NAME                                              CO
------------------------------------------------------------ --
SYS_C0010625                                                 P
通過查詢user_cons_columns可得出
SQL> select constraint_name,column_name from user_cons_columns
2  where table_name=‘DEPT04‘;

CONSTRAINT_NAME                                              COLUMN_NAME
------------------------------------------------------------ ------------

SYS_C0010625                                                 DNO
例2：表級定義主鍵
CREATE TABLE dept05(
dno INT,
dname VARCHAR2(10),loc VARCHAR2(20),
PRIMARY KEY(dno,dname)
);
通過查詢user_constraints能夠得出
SQL> select constraint_name,constraint_type from user_constraints
2  where table_name=‘DEPT05‘;

CONSTRAINT_NAME                                              CO
------------------------------------------------------------ --
SYS_C0010626                                                 P
通過查詢user_cons_columns可得出
SQL> select constraint_name,column_name from user_cons_columns
2  where table_name=‘DEPT05‘;

CONSTRAINT_NAME                                              COLUMN_NAME
------------------------------------------------------------ -------------

SYS_C0010626                                                 DNO
SYS_C0010626                                                 DNAME
可見，當在表級定義時，約束名稱將會同樣，而在user_cons_columns查詢是，每一列將相應約束

名稱同樣的兩條記錄

 

4.定義FOREING KEY約束
當定義了外部鍵約束之後，要求外部鍵列的資料必須在主表的主鍵列（或惟一列）中存在，或

者為NULL,FOREING KEY約束既能夠在列級定義，也能夠在表級定義。
keyword說明：
FOREING KEY:該選項用於指定在表級定義外部鍵約束。當在表級定義外部鍵約束時必須指定該選

項，在列級定義外部鍵約束不須要指定該選項
REFERENCES:該選項用於指定主表名及其主鍵列。當定義外部鍵約束時，該選項必須指定。
ON DELETE CASCAED:該選項用於指定串聯刪除選項。假設在定義外部鍵約束時指定了該選項，那

麼當刪除主表資料時會串聯刪除從表的相關資料。
ON DELECT SET NULL:該選項用於指定轉換相關的外部鍵值為NULL，假設在定義外部鍵約束時指定

了該選項，那麼當刪除主表資料時會將從表外部鍵列的資料設定為NULL。
例1：列級定義外鍵約束
CREATE TABLE emp04(
eno INT,name VARCHAR2(10),salary NUMBER(6,2),
dno INT CONSTRAINT fk_dno REFERENCES dept04(dno)
);
SQL> select constraint_name,constraint_type from user_constraints
2  where table_name=‘EMP04‘;

CONSTRAINT_NAME                                              CO
------------------------------------------------------------ --
FK_DNO                                                       R
SQL> select constraint_name,column_name from user_cons_columns
2  where table_name=‘EMP04‘;

CONSTRAINT_NAME                                              COLUMN_NAME
------------------------------------------------------------ -----------

FK_DNO                                                       DNO
例2：表級定義外鍵約束
CREATE TABLE emp05(
eno INT,name VARCHAR2(10),salary NUMBER(6,2),
dno INT,
CONSTRAINT fk_dno_name FOREIGN KEY(dno,name) REFERENCES dept05(dno,dname)
);
SQL> select constraint_name,constraint_type from user_constraints
2  where table_name=‘EMP05‘
3  ;

CONSTRAINT_NAME                                              CO
------------------------------------------------------------ --
FK_DNO_NAME                                                  R
SQL> select constraint_name,column_name from user_cons_columns
2  where table_name=‘EMP05‘;

CONSTRAINT_NAME                                              COLUMN_NAME
------------------------------------------------------------ -----------

FK_DNO_NAME                                                  DNO
FK_DNO_NAME                                                  NAME

5.定義CHECK約束
CHECK約束既能夠在列級定義，也能夠在表級定義。CHECK約束同意列為NULL。
例：
CREATE TABLE emp06(
eno INT,name VARCHAR2(10),salary NUMBER(6,2),
CHECK (salary BETWEEN 1000 AND 5000)
);
SQL>  select constraint_name,constraint_type from user_constraints
2   where table_name=‘EMP06‘;

CONSTRAINT_NAME                                              CO
------------------------------------------------------------ --
SYS_C0010629                                                 C
SQL> select constraint_name,column_name from user_cons_columns
2  where table_name=‘EMP06‘;

CONSTRAINT_NAME                                              COLUMN_NAME
------------------------------------------------------------ -----------

SYS_C0010629                                                 SALARY

6.定義複合約束
複合約束即在表級定義，基於多列的複合約束，如：
CREATE TABLE dept05(
dno INT,
dname VARCHAR2(10),loc VARCHAR2(20),
PRIMARY KEY(dno,dname)
);或
CREATE TABLE emp05(
eno INT,name VARCHAR2(10),salary NUMBER(6,2),
dno INT,
CONSTRAINT fk_dno_name FOREIGN KEY(dno,name) REFERENCES dept05(dno,dname)
);

 

9.3維護約束
9.3.1添加約束
假設添加UNIQUE、PRIMARY KEY、FOREIGN KEY 和CKECK 必須使用ALTER TABLE語句的ADD子句;

假設添加NOT NULL約束，那麼必須使用ALTER TABLE語句的MODIFY子句，如：
ALTER TABLE table_name ADD [CONSTRAINT constraint_name]
constraint_type (column,...)
ALTER TABLE table_name MODIFY column
[CONSTRAINT constraint_name] NOT NULL;
9.3.2改動約束名
在同一個方案中，約束名必須惟一,而且約束名也不能與其它對象同名。當鱫IMPDP工具或者IMP

工具匯入其它對象時，如發現有同名的對象，將會出錯
文法：
ALTER TABLE table_name RENAME CONSTRAINT old_constraint_name
TO new_constraint_name;
例：
ALTER TABLE emp01 RENAME CONSTRAINT SYS_C005028
TO ck_emp01_salary;
9.3.3 刪除約束
當刪除特定表的主鍵約束時，假設該表具有相關的從表，那麼在刪除主鍵約束

時必須帶有CASCAED選項
文法：
ALTER TABLE table_name DROP
CONSTRAINT constraint_name |PRIMARY KEY
例1
ALTER TABLE emp01 DROP CONSTRAINT ck_emp01_salary;
例2
ALTER TABLE dept01 DROP PRIMARY KEY CASCAED
9.3.4禁止約束
禁止約束指使約束暫時失效。當禁止了約束之後，約束規則將不再生效。在使用SQL*LOADER或

INSERT裝載資料之前，為了加快資料裝載速度，應該首先禁止約束，然後裝載資料。
文法：
ALTER TABLE table_name
DISABLE CONSTRAINT constaint_name [CASCAED];--CASCAED用於指定級聯禁止從表的外部鍵

約束
例：
ALTER TABLE emp05 DISABLE CONSTAINT SYS_C00502;
9.3.5 啟用約束
文法：
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;
例：
ALTER TABLE emp05 ENABLE CONSTRAINT SYS_C005022;
9.4 顯示約束資訊
1.USER_CONSTRAINTS
2.USER_CONS_COLUMNS

Zz_cnblogs ：http://www.cnblogs.com/ljcbest/archive/2010/09/02/1816346.html

oracle Constraint[相似 constraint使用方法總結 I]