資料的完整性用於確保資料庫資料遵從一定的商業和邏輯規則。在oracle中，資料完整性是透過約束來實現的。在oracle中約束包括：not null , unique , primary key , foreign key , check 五種。
1.not null（非空）
如果在列上定義了not null，那麼當插入資料時，必須為列提供資料。
2.unique（唯一）
當定義了唯一約束之後，該列值是不能重複的，但是可以為null。
3.primary key（主鍵）
用於唯一的標誌行的資料，當定義主鍵約束之後，該列不但不能重複而且不能為null。一張表最多隻能有一個主鍵，但是可以有多個unique約束。
4.foreign key（外來鍵）
用於定義主表和從表之間的關係，外來鍵約束定義在從表上，主表則必須有主鍵約束或unique約束。當定義了外來鍵約束後，要求外來鍵列資料必須在主表的主鍵列存在或為null。
5.check
用於強制行資料必須滿足的條件，假定在sal列上定義了check約束，並要求sal列值在1000-2000之間，如果不在1000-2000之間就會提示出錯。
約束定義語法：
約束表級定義是指在定義了所有列之後，在定義約束，這裡需要注意的是，not null約束只能在列級上定義。
create table goods(
goodsid char(8),goodsname varchar2(50),category varchar(30),
constraint PK_goodsid primary key(goodsid));
約束列級定義是在定義列的同時定義約束。
create table goods(
goodsid char(8) constraint PK_goodsid primary key,
goodsname varchar2(50),
unitprice number(10,2) check(unitprice>0),
category varchar(30),
provider varchar(100));

如果在建表時忘記建立必要的約束，則可在建表後使用alter table命令為表增加約束，但是要注意的是，增加not null約束時，需要使用modify選項，而增加其他四種約束使用add選項。
(1)增加商品名不能為空
alter table goods modify goodsid not null;
(2)增加身份證不能重複
alter table customers add constraint uq_cardid unique(cardid);
(3)增加客戶地址只能是a,b,c,d
alter table customers add constraint ck_address check(address in ('a','b','c','d'));

透過檢視動態效能檢視user_constraints可以顯示當前使用者所有的約束資訊：
GOODS@orcl 02-SEP-14>desc user_constraints
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
OWNER VARCHAR2(30)
CONSTRAINT_NAME NOT NULL VARCHAR2(30)
CONSTRAINT_TYPE VARCHAR2(1)
TABLE_NAME NOT NULL VARCHAR2(30)
SEARCH_CONDITION LONG
R_OWNER VARCHAR2(30)
R_CONSTRAINT_NAME VARCHAR2(30)
DELETE_RULE VARCHAR2(9)
STATUS VARCHAR2(8)
DEFERRABLE VARCHAR2(14)
DEFERRED VARCHAR2(9)
VALIDATED VARCHAR2(13)
GENERATED VARCHAR2(14)
BAD VARCHAR2(3)
RELY VARCHAR2(4)
LAST_CHANGE DATE
INDEX_OWNER VARCHAR2(30)
INDEX_NAME VARCHAR2(30)
INVALID VARCHAR2(7)
VIEW_RELATED VARCHAR2(14)

GOODS@orcl 02-SEP-14>col constraint_name for a20
GOODS@orcl 02-SEP-14>col table_name for a20

GOODS@orcl 02-SEP-14>select constraint_name,constraint_type,table_name
2 from user_constraints
3 where table_name in('GOODS','CUSTOMERS','PURCHASES');

CONSTRAINT_NAME C TABLE_NAME
-------------------- - --------------------
SYS_C0011122 C CUSTOMERS
SYS_C0011123 C CUSTOMERS
SYS_C0011124 P CUSTOMERS
SYS_C0011125 U CUSTOMERS
UQ_CARDID U CUSTOMERS
CK_ADDRESS C CUSTOMERS
SYS_C0011120 C GOODS
SYS_C0011121 P GOODS
SYS_C0011129 C GOODS
SYS_C0011126 C PURCHASES
SYS_C0011127 R PURCHASES

CONSTRAINT_NAME C TABLE_NAME
-------------------- - --------------------
FK001 R PURCHASES

可以透過user_cons_columns檢視約束的列表資訊：

GOODS@orcl 02-SEP-14>select column_name,constraint_name,table_name
2 from user_cons_columns
3 where constraint_name='CK_ADDRESS';

COLUMN_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CONSTRAINT_NAME TABLE_NAME
-------------------- --------------------
ADDRESS
CK_ADDRESS CUSTOMERS

如果不需要某個約束的話可以刪除：
GOODS@orcl 02-SEP-14>alter table customers drop constraint ck_address;

Table altered.

在刪除主鍵約束時，可能有如下錯誤：
GOODS@orcl 02-SEP-14>alter table goods drop primary key;
alter table goods drop primary key
*
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys

這是因為如果在兩表存在主從關係，那麼在刪除主表主鍵約束的同時，必須加上cascade選項：
GOODS@orcl 02-SEP-14>alter table goods drop primary key cascade;

Table altered.

oracle中的約束

相關文章