oracle 約束詳解
Oracle 約束詳解
一 約束的定義
約束是強加在表上的規則或條件。確保資料庫滿足業務規則。保證資料的完整性。當對錶進行DML或DDL操作時，如果此操作會造成表中的資料違反約束條件或規則的話，系統就會拒絕執行這個操作。約束可以是列一級別的 也可以是表級別的。定義約束時沒有給出約束的名字，ORACE系統將為該約束自動生成一個名字，其格式為SYS_Cn，其中n為自然數(強烈建議各位在建立表或增加約束時，給約束定義名稱。)
在Oracle中，資料完整性可以使用約束、觸發器、應用程式（過程、函式）三種方法來實現，在這三種方法中，因為約束易於維護，並且具有最好的效能，所以作為維護資料完整性的首選。
列級約束：
列級定義是在定義列的同時定義約束；
column [CONSTRAINT constraint_name] constraint_type
表級約束：
表級定義是指在定義了所有列後，再定義約束，這裡需要注意，not null約束只能在列級上定義；
column ,...,
[CONSTRAINT constraint_name] constraint_type (column,...)
二 約束功能
約束的功能：實現一些業務規則，防止無效的垃圾資料進入資料庫，維護資料庫的完整性(完整性指正確性與一致性)。從而使資料庫的開發和維護都更加容易。
三 約束的分類
1 not null （非空）
如果在列上定義了not null，那麼當插入資料時，必須為列提供，資料不能為NULL。約束只能在列級定義，不能在表級定義。
2 unique （唯一）
當定義了唯一約束後，該列值是不能重複的，但是可以為null。
3 primary key （主鍵）
用於唯一的標識錶行的資料，當定義主鍵約束後，該列不但不能重複而且不能為NULL。一張表最多隻能有一個主鍵，但是可以由多個unique約束。
建立主鍵或唯一約束後，ORACLE會自動建立一個與約束同名的索引（UNIQUENES為UNIQUE唯一索引）。需要注意的是：每個表只能有且有一個主鍵約束。
4 foreign key （外來鍵）
用於定義主表和從表之間的關係，外來鍵約束要定義在從表上，主要則必須具有主鍵約束或是unique約束，當定義外來鍵約束後，要求外來鍵列資料必須在主表的主鍵列存在或是為NULL。
用來維護從表（Child Table）和主表（Parent Table）之間的引用完整性. 外來鍵約束是個有爭議性的約束，它一方面能夠維護資料庫的資料一致性，資料的完整性。防止錯誤的垃圾資料入庫； 另外一方面它會增加表插入、更新等SQL效能的額外開銷，不少系統裡面透過業務邏輯控制來取消外來鍵約束。例如在資料倉儲中，就推薦禁用外來鍵約束。
5 check
用於強制行資料必須滿足的條件，假定在sal列上定義了check約束，並要求sal列值在1000～2000之間，如果不在1000～2000之間就會提示出錯。
四 約束命令規範
約束名稱建議自己定義一套命名規則，否則使用系統生成的約束名，很難能把它和對應的表、欄位聯絡起來。
非空約束 NN_表名_列名
唯一約束 UK_表名_列名
主鍵約束 PK_表名
外來鍵約束 FK_表名_列名
條件約束 CK_表名_列名
預設約束 DF_表名_列名
如果約束名稱超過32位長度，建議應該縮寫表名，而不應用NN_表名_數字。不過具體視情況而定，很多時候 DF_表名_列名 這樣命名，往往超出了32字元。所以有時候需要縮寫表面或是採用其它規則。
五 建立約束
1 not null （非空）
方法一：
SQL> create table t1(id number,name varchar2(20) constraint nn_t1_id not null);
SQL> select constraint_name,constraint_type,owner from user_constraints;
CONSTRAINT_NAME C OWNER
------------------------------ - ----------
NN_T1_ID C SCOTT
方法二：
SQL> drop table t1 purge;
SQL> create table t1(id number,name varchar2(20));
SQL> alter table t1 modify id constraint nn_t1_id not null;
SQL> select constraint_name,table_name,owner from user_constraints;
CONSTRAINT_NAME TABLE_NAME OWNER
------------------------------ ------------------------------ ----------
NN_T1_ID T1 SCOTT
2 unique （唯一）
方法一：
SQL> create table t1(id number,qq number,constraint un_t1_qq unique(qq));
方法二：
SQL> alter table t1 add constraint un_t1_qq unique(qq);
3 primary key （主鍵）
方法一：
SQL> create table t1(id number,qq number,constraint pk_t1_id primary key(id));
方法二：
SQL> alter table t1 add constraint pk_t1_id primary key(id);
SQL> select constraint_name,table_name,owner from user_constraints;
CONSTRAINT_NAME TABLE_NAME OWNER
------------------------------ ------------------------------ ----------
PK_T1_ID T1 SCOTT
4 foreign key （外來鍵）
方法一：
SQL> create table t2(id number,cc number,constraint fk_t2_id foreign key(id) references t1(id));
方法二：
SQL> alter table t1 add constraint pk_t2_id foreign key(id) references t1(id);
SQL> select constraint_name,table_name,owner from user_constraints;
CONSTRAINT_NAME TABLE_NAME OWNER
------------------------------ ------------------------------ ----------
PK_T1_ID T1 SCOTT
PK_T2_ID T1 SCOTT
當定義了外部鍵約束之後，要求外部鍵列的資料必須在主表的主鍵列（或惟一列）中存在，或者為NULL,FOREING KEY約束既可以在列級定義，也可以在表級定義。
關鍵字說明：
(1) FOREING KEY:該選項用於指定在表級定義外部鍵約束。當在表級定義外部鍵約束時必須指定該選項，在列級定義外部鍵約束不需要指定該選項
(2) REFERENCES:該選項用於指定主表名及其主鍵列。當定義外部鍵約束時，該選項必須指定。
(3) ON DELETE CASCAED:該選項用於指定級聯刪除選項。如果在定義外部鍵約束時指定了該選項，那麼當刪除主表資料時會級聯刪除從表的相關資料。
(4) ON DELECT SET NULL:該選項用於指定轉換相關的外部鍵值為NULL，如果在定義外部鍵約束時指定了該選項，那麼當刪除主表資料時會將從表外部鍵列的資料設定為NULL。
SQL> create table t1(id number,qq number,constraint pk_t1_id primary key(id));
SQL> create table t2(id number,sal number,constraint fk_t2_id foreign key(id) references t1(id));
SQL> delete t1; ----由於主外來鍵約束，無法刪除主表
delete t1
*
ERROR at line 1:
ORA-02292: integrity constraint (SYS.FK_T2_ID) violated - child record found
SQL> insert into t2 values(2,2); ---由於主外來鍵的約束，無法在外來鍵表插入主鍵中id列沒有的值
insert into t2 values(2,2)
*
ERROR at line 1:
ORA-02291: integrity constraint (SYS.FK_T2_ID) violated - parent key not found
SQL> delete t2;
1 row deleted.
SQL> rollback;
SQL> drop table t2 purge;
SQL> create table t2(id number,sal number,constraint fk_t2_id foreign key(id) references t1(id) on delete cascade); -----外來鍵表新增級聯刪除引數
SQL> delete t1; ----刪除主鍵表上的資料，級聯刪除外來鍵表上的資料
1 row deleted.
SQL> select * from t1;
no rows selected
SQL> select * from t2;
no rows selected
5 check （檢查性約束）
方法一：
SQL> create table t3(id number,sal number,constraint ck_t3_sal check(sal between 5000 and 50000));
SQL> select constraint_name,table_name,owner from user_constraints;
CONSTRAINT_NAME TABLE_NAME OWNER
------------------------------ ------------------------------ ----------
CK_T3_SAL T3 SCOTT
方法二：
SQL> create table t3(id number,sal number);
SQL> alter table t3 add constraint ck_t3_sal check(sal>5000);
六 維護約束
1 增加約束
（1） 如果增加UNIQUE、PRIMARY KEY、FOREIGN KEY 和CKECK 必須使用ALTER TABLE語句的ADD子句;
（2） 如果增加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;
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;
SQL> alter table t1 rename constraint PK_T1_ID to new pk01_t1_id;
alter table t1 rename constraint PK_T1_ID to new pk01_t1_id
*
ERROR at line 1: --------主鍵無法更改名字
ORA-23290: This operation may not be combined with any other operation
SQL> alter table t2 rename constraint fk_t2_id to fk01_t2_id;
SQL> select constraint_name,table_name from user_constraints where table_name='T2';
CONSTRAINT_NAME TABLE_NAME
------------------------------ ------------------------------
FK01_T2_ID T2 -------------外來鍵可以更改名字
SQL> alter table t1 add constraint un_t1_qq unique(qq);
SQL> select constraint_name,table_name from user_constraints where table_name='T1';
CONSTRAINT_NAME TABLE_NAME
------------------------------ ------------------------------
PK_T1_ID T1
UN_T1_QQ T1
SQL> alter table t1 rename constraint un_t1_qq to un01_t1_qq;
SQL> select constraint_name,table_name from user_constraints where table_name='T1';
CONSTRAINT_NAME TABLE_NAME
------------------------------ ------------------------------
PK_T1_ID T1
UN01_T1_QQ T1
3 禁止約束
禁止約束指使約束臨時失效。當禁止了約束之後，約束規則將不再生效。在使用SQL*LOADER或INSERT裝載資料之前，為了加快資料裝載速度，應該首先禁止約束，然後裝載資料。
語法：
ALTER TABLE table_name
DISABLE CONSTRAINT constaint_name [CASCAED];--CASCAED用於指定級聯禁止從表的外部鍵
SQL> insert into t2 values(2,2);
insert into t2 values(2,2)
*
ERROR at line 1:
ORA-02291: integrity constraint (SYS.FK01_T2_ID) violated - parent key not
found
SQL> alter table t2 disable constraint fk01_t2_id;
Table altered.
SQL> insert into t2 values(2,2);
1 row created.
4 啟用約束
語法：
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;
例：
SQL> alter table t2 enable constraint fk01_t2_id;
alter table t2 enable constraint fk01_t2_id
*
ERROR at line 1: ---外來鍵啟用失敗，原因是在外來鍵表中含有主鍵表中沒有的資料
ORA-02298: cannot validate (SYS.FK01_T2_ID) - parent keys not found
SQL> delete t2 where id=2; -------刪除資料
SQL> alter table t2 enable constraint fk01_t2_id; ----啟用成功
5 刪除約束
當刪除特定表的主鍵約束時，如果該表具有相關的從表，那麼在刪除主鍵約束時必須帶有CASCAED選項
語法：
ALTER TABLE table_name DROP
CONSTRAINT constraint_name |PRIMARY KEY
例一：（刪除唯一性約束）
SQL> select constraint_name,table_name from user_constraints where table_name='T1';
CONSTRAINT_NAME TABLE_NAME
------------------------------ ------------------------------
PK_T1_ID T1
UN01_T1_QQ T1
SQL> alter table t1 drop constraint un01_t1_qq;
Table altered.
SQL> select constraint_name,table_name from user_constraints where table_name='T1';
CONSTRAINT_NAME TABLE_NAME
------------------------------ ------------------------------
PK_T1_ID T1
例二：（刪除主鍵約束，級聯刪除外來鍵約束）
SQL> alter table t1 drop primary key cascade;
Table altered.
SQL> select constraint_name,table_name from user_constraints where table_name='T2';
no rows selected
SQL> select constraint_name,table_name from user_constraints where table_name='T1';
no rows selected
6 顯示資訊
1.USER_CONSTRAINTS
2.USER_CONS_COLUMNS
SQL> select constraint_name,table_name,column_name from user_cons_columns where table_name='T1';
CONSTRAINT_NAME TABLE_NAME COLUM
------------------------------ ------------------------------ -----
UN_T1_ID T1 ID
歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-1271431/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL——約束(constraint)詳解MySqlAI
- oracle 註釋和約束Oracle
- Android 約束佈局（ConstraintLayout）1.1.0 版詳解AndroidAI
- 10、Oracle中的約 束constraintOracleAI
- 【SQL】15 SQL 約束（Constraints）、NOT NULL 約束、UNIQUE 約束、PRIMARY KEY 約束、FOREIGN KEY 約束、CHECK 約束、DEFAULT約束SQLAINull
- XML Schema 字串資料型別及約束詳解XML字串資料型別
- oracle外來鍵約束的總結Oracle
- Oracle如何管理帶約束的B樹索引Oracle索引
- 差分約束基本講解
- 約束
- Javaweb-約束-外來鍵約束JavaWeb
- 約束CONSTRAINTAI
- 03約束
- MySQL 約束MySql
- SQL約束SQL
- 主鍵約束、唯一約束和唯一索引索引
- （10）邏輯綜合新增約束（環境約束）
- 約束介紹
- 差分約束
- 綜合約束
- MariaDB資料庫的外來鍵約束例項程式碼介紹詳解資料庫
- Oracle SCN詳解Oracle
- oracle rowid詳解Oracle
- ORACLE -詳解SCNOracle
- 完整性約束
- 外來鍵約束
- SQLServer約束介紹SQLServer
- IDELAY約束測試IDE
- MySQL自增約束MySql
- Javaweb-約束案例JavaWeb
- 《學習》6約束
- MySQL 欄位約束MySql
- 聊聊Oracle外來鍵約束（Foreign Key）的幾個操作選項Oracle
- ORACLE的HINT詳解Oracle
- Oracle練習詳解Oracle
- oracle oradebug使用詳解Oracle
- Mysql關於資料庫是否應該使用外來鍵約束詳解說明創磅MySql資料庫
- 泛型的約束理解泛型
- C#泛型約束C#泛型