程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle基礎知識筆記（10）約束

Oracle基礎知識筆記（10）約束

編輯：Oracle教程

表雖然建立完成了，但是表中的數據是否合法並不能有所檢查，而如果要想針對於表中的數據做一些過濾的話，則可以通過約束完成，約束的主要功能是保證表中的數據合法性，按照約束的分類，一共有五種約束：非空約束、唯一約束、主鍵約束、檢查約束、外鍵約束。

一、非空約束（NOT NULL）：NK

當數據表中的某個字段上的內容不希望設置為null的話，則可以使用NOT NULL進行指定。

范例：定義一張數據表

DROP TABLE member PURGE;
CREATE TABLE member(
    mid NUMBER,
    name VARCHAR2(50) NOT NULL
);

因為此時存在了“NOT NULL”約束，所以下面插入兩組數據。

范例：正確的數據

INSERT INTO member(mid,name) VALUES(1,'張三');
INSERT INTO member(mid,name) VALUES(null,'李四');
INSERT INTO member(name) VALUES('王五');

范例：插入錯誤的數據

INSERT INTO member(mid,name) VALUES(9,null);
INSERT INTO member(mid) VALUES(10);

此時了出現的錯誤提示：

ORA-01400: 無法將 NULL 插入 ("SCOTT"."MEMBER"."NAME")

本程序之中，直接表示出了“用戶”.“表名稱”.“字段”出現了錯誤。

二、唯一約束（UNIQUE）：UK

唯一約束指的是每一列上的數據是不允許重復的，例如：email地址每個用戶肯定是不重復的，那麼就使用唯一約束完成。

DROP TABLE member PURGE;
CREATE TABLE member(
    mid NUMBER,
    name VARCHAR2(50) NOT NULL,
    email VARCHAR2(50) UNIQUE
);

范例：插入正確的數據

INSERT INTO member(mid,name,email) VALUES(1,'張三','[email protected]');
INSERT INTO member(mid,name,email) VALUES(2,'李四',null);

范例：插入錯誤的數據 —— 重復數據

INSERT INTO member(mid,name,email) VALUES(3,'王五','[email protected]');

此時會出現如下的錯誤提示：

ORA-00001: 違反唯一約束條件 (SCOTT.SYS_C005272)

可是這個時候的錯誤提示與之前的非空約束相比並不完善，因為現在只是給出了一個代號而已，這是因為在定義約束的時候沒有為約束指定一個名字，所以由系統默認分配了，而且約束的名字建議的格式“約束類型_字段”，例如：“UK_email”，指定約束名稱使用CONSTRAINT完成。

DROP TABLE member PURGE;
CREATE TABLE member(
    mid NUMBER,
    name VARCHAR2(50) NOT NULL,
    email VARCHAR2(50),
    CONSTRAINT UK_email UNIQUE(email)
);

以後再次增加錯誤數據時，提示信息如下：

ORA-00001: 違反唯一約束條件 (SCOTT.UK_EMAIL)

已經可以很明確的提示用戶錯誤的位置。

三、主鍵約束（Primary Key）：PK

主鍵約束 = 非空約束 + 唯一約束，在之前設置唯一的約束的時候發現可以設置為null，而如果現在使用了主鍵約束之後則不能為空，而且主鍵一般作為數據的唯一的一個標記出現，例如：人員的ID。

范例：建立主鍵約束

DROP TABLE member PURGE;
CREATE TABLE member(
    mid NUMBER PRIMARY KEY,
    name VARCHAR2(50) NOT NULL
);

范例：增加正確的數據

INSERT INTO member(mid,name) VALUES(1,'張三');

范例：錯誤的數據 —— 主鍵設置為null

INSERT INTO member(mid,name) VALUES(null,'張三');

錯誤信息，與之前的非空約束的錯誤信息提示是一樣的；

ORA-01400: 無法將 NULL 插入 ("SCOTT"."MEMBER"."MID")

范例：錯誤的數據 —— 主鍵重復

INSERT INTO member(mid,name) VALUES(1,'張三');

錯誤信息，這個錯誤信息就是唯一約束的錯誤信息，但是信息不明確，因為沒起名字。

ORA-00001: 違反唯一約束條件 (SCOTT.SYS_C005276)

所以為了約束的使用方便，下面為主鍵約束起一個名字。

DROP TABLE member PURGE;
CREATE TABLE member(
    mid NUMBER,
    name VARCHAR2(50) NOT NULL,
    CONSTRAINT pk_mid PRIMARY KEY(mid)
);

此時，重復插入數據，則錯誤信息如下：

ORA-00001: 違反唯一約束條件 (SCOTT.PK_MID)

從正常的開發角度而言，一張表一般都只設置一個主鍵，但是從SQL語法的規定而言，一張表卻可以設置多個主鍵，而此種做法稱為復合主鍵，例如：參考如下代碼：

DROP TABLE member PURGE;
CREATE TABLE member(
    mid NUMBER,
    name VARCHAR2(50) NOT NULL,
    CONSTRAINT pk_mid PRIMARY KEY(mid,name)
);

在復合主鍵的使用之中，只有兩個字段的內容都一樣的情況下，才被稱為重復數據。

范例：插入正確的數據

INSERT INTO member(mid,name) VALUES(1,'張三');
INSERT INTO member(mid,name) VALUES(1,'李四');
INSERT INTO member(mid,name) VALUES(2,'李四');

范例：插入錯誤的數據

INSERT INTO member(mid,name) VALUES(1,'張三');

錯誤信息：

ORA-00001: 違反唯一約束條件 (SCOTT.PK_MID)

但是從開發的實際角度而言，一般都不使用復合主鍵，所以這個知識只是作為其相關的內容做一個介紹。只要是數據表，永遠都只設置一個主鍵。

四、檢查約束（Check）：CK

檢查約束指的是為表中的數據增加一些過濾條件，例如：

設置年齡的時候范圍是：0~200；
設置性別的時候應該是：男、女；
范例：設置檢查約束
```
DROP TABLE member PURGE;
CREATE TABLE member(
    mid NUMBER,
    name VARCHAR2(50) NOT NULL,
    sex VARCHAR2(10) NOT NULL,
    age NUMBER(3),
    CONSTRAINT pk_mid PRIMARY KEY(mid),
    CONSTRAINT ck_sex CHECK(sex IN('男','女')),
    CONSTRAINT ck_age CHECK(age BETWEEN 0 AND 200)
);
```
范例：增加正確的數據
```
INSERT INTO member(mid,name,sex,age) VALUES(1,'張三','男','26');
```
范例：增加錯誤的性別 —— ORA-02290: 違反檢查約束條件 (SCOTT.CK_SEX)
```
INSERT INTO member(mid,name,sex,age) VALUES(2,'李四','非','26');
```
范例：增加錯誤的年齡 —— ORA-02290: 違反檢查約束條件 (SCOTT.CK_AGE)
```
INSERT INTO member(mid,name,sex,age) VALUES(2,'李四','女','260');
```
檢查的操作就是對輸入的數據進行一個過濾。

五、主-外鍵約束

之前的四種約束都是在單張表中進行的，而主-外鍵約束是在兩張表中進行的，這兩張表是存在父子關系的，即：子表中某個字段的取值范圍由父表所決定。

例如，現在要求表示出一種關系，每一個人有多本書，應該定義兩張數據表：member（主）、book（子）；
```
DROP TABLE member PURGE;
DROP TABLE book PURGE;
CREATE TABLE member(
    mid NUMBER,
    name VARCHAR2(50) NOT NULL,
    CONSTRAINT pk_mid PRIMARY KEY(mid)
);
CREATE TABLE book(
    bid NUMBER,
    title VARCHAR2(50) NOT NULL,
    mid NUMBER,
    CONSTRAINT pk_bid PRIMARY KEY(bid)
);
```
此時只是根據要求建立了兩張獨立的數據表，那麼下面插入幾條數據：
```
INSERT INTO member(mid,name) VALUES(1,'張三');
INSERT INTO member(mid,name) VALUES(2,'李四');
INSERT INTO book(bid,title,mid) VALUES(101,'Java開發',1);
INSERT INTO book(bid,title,mid) VALUES(102,'Java Web開發',2);
INSERT INTO book(bid,title,mid) VALUES(103,'EJB開發',2);
INSERT INTO book(bid,title,mid) VALUES(105,'Android開發',1);
INSERT INTO book(bid,title,mid) VALUES(107,'AJAX開發',1);
```
要想驗證這個數據是否有意義，最簡單的做法，就是寫兩個查詢。

范例：統計每個人員擁有書的數量
```
SELECT m.mid,m.name,COUNT(b.bid)
FROM member m,book b
WHERE m.mid=b.mid
GROUP BY m.mid,m.name;
```
范例：查詢出每個人員的編號，姓名，擁有書的名稱
```
SELECT m.mid,m.name,b.title
FROM member m,book b
WHERE m.mid=b.mid;
```
即，現在的book.mid字段應該是與member.mid字段相關聯的，但是由於本程序沒有設置約束，所以，現在以下的數據也是可以增加的：
```
INSERT INTO book(bid,title,mid) VALUES(108,'PhotoShop使用手冊',3);
INSERT INTO book(bid,title,mid) VALUES(109,'FLEX開發手冊',8);
```
現在增加了兩條新的記錄，而且記錄可以保存在數據表之中，但是這兩條記錄沒有意義，因為member.mid字段的內容沒有3和8，而要想解決這個問題就必須依靠外鍵約束來解決。

讓book.mid的字段的取值由member.mid所決定，如果member.mid的數據真實存在，則表示可以更新。
```
DROP TABLE member PURGE;
DROP TABLE book PURGE;
CREATE TABLE member(
    mid NUMBER,
    name VARCHAR2(50) NOT NULL,
    CONSTRAINT pk_mid PRIMARY KEY(mid)
);
CREATE TABLE book(
    bid NUMBER,
    title VARCHAR2(50) NOT NULL,
    mid NUMBER,
    CONSTRAINT pk_bid PRIMARY KEY(bid),
    CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid)
);
```
此時，只是增加了一個約束，這樣一來如果輸入的數據有錯誤，則會出現如下的提示：
```
ORA-02291: 違反完整約束條件 (SCOTT.FK_MID) - 未找到父項關鍵字
```
因為member.mid沒有指定的數據，所以book.mid如果數據有錯誤，則無法執行更新操作。

使用外鍵的最大好處是控制了子表中某些數據的取值范圍，但是同樣帶來了不少的問題；

1、刪除數據的時候，如果主表中的數據有對應的子表數據，則無法刪除；

范例：刪除member表中mid為1的數據
```
DELETE FROM member WHERE mid=1;
```
錯誤提示信息：“ORA-02292: 違反完整約束條件 (SCOTT.FK_MID) - 已找到子記錄”。

此時，只能先刪除子表記錄，之後再刪除父表記錄：
```
DELETE FROM book WHERE mid=1;
DELETE FROM member WHERE mid=1;
```
但是這種操作明顯不方便，如果說現在希望主表數據刪除之後，子表中對應的數據也可以刪除的話，則可以在建立外鍵約束的時候指定一個級聯刪除的功能，修改數據庫創建腳本：
```
DROP TABLE member PURGE;
DROP TABLE book PURGE;
CREATE TABLE member(
    mid NUMBER,
    name VARCHAR2(50) NOT NULL,
    CONSTRAINT pk_mid PRIMARY KEY(mid)
);
CREATE TABLE book(
    bid NUMBER,
    title VARCHAR2(50) NOT NULL,
    mid NUMBER,
    CONSTRAINT pk_bid PRIMARY KEY(bid),
    CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE CASCADE
);
```
此時由於存在級聯刪除的操作，所以主表中的數據刪除之後，對應的子表中的數據也都會被同時刪除。

2、刪除數據的時候，讓子表中對應的數據設置為null

當主表中的數據刪除之後，對應的子表中的數據相關項也希望將其設置為null，而不是刪除，此時，可以繼續修改數據表的創建腳本：
```
DROP TABLE member PURGE;
DROP TABLE book PURGE;
CREATE TABLE member(
    mid NUMBER,
    name VARCHAR2(50) NOT NULL,
    CONSTRAINT pk_mid PRIMARY KEY(mid)
);
CREATE TABLE book(
    bid NUMBER,
    title VARCHAR2(50) NOT NULL,
    mid NUMBER,
    CONSTRAINT pk_bid PRIMARY KEY(bid),
    CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member(mid) ON DELETE SET NULL
);
INSERT INTO member(mid,name) VALUES(1,'張三');
INSERT INTO member(mid,name) VALUES(2,'李四');
INSERT INTO book(bid,title,mid) VALUES(101,'Java開發',1);
INSERT INTO book(bid,title,mid) VALUES(102,'Java Web開發',2);
INSERT INTO book(bid,title,mid) VALUES(103,'EJB開發',2);
INSERT INTO book(bid,title,mid) VALUES(105,'Android開發',1);
INSERT INTO book(bid,title,mid) VALUES(107,'AJAX開發',1);
```
3、刪除父表之前必須首先先刪除對應的子表，否則無法刪除
```
DROP TABLE book PURGE;
DROP TABLE member PURGE;
```
但是這樣做明顯很麻煩，因為對於一個未知的數據庫，如果要按照此類方式進行，則必須首先知道其父子關系，所以在Oracle之中專門提供了一個強制性刪除表的操作，即：不再關心約束，在刪除的時候寫上一句“CASCADE CONSTRAINT”。
```
DROP TABLE member CASCADE CONSTRAINT PURGE;
DROP TABLE book CASCADE CONSTRAINT PURGE;
```
此時，不關心子表是否存在，直接強制性的刪除父表。

合理做法：在以後進行數據表刪除的時候，最好是先刪除子表，之後再刪除父表。

六、修改約束

約束本身也屬於數據庫對象，那麼也肯定可以進行修改操作，而且只要是修改都使用ALTER指令，約束的修改主要指的是以下兩種操作：
- 為表增加約束：
```
ALTER TABLE 表名稱 ADD CONSTRAINT 約束名稱 約束類型(字段);
```
  - 刪除表中的約束：
```
ALTER TABLE 表名稱 DROP CONSTRAINT 約束名稱;
```
    可以發現，如果要維護約束，肯定需要一個正確的名字才可以，可是在這五種約束之中，非空約束作為一個特殊的約束無法操作，現在有如下一張數據表：
```
DROP TABLE member CASCADE CONSTRAINT PURGE;
CREATE TABLE member(
    mid NUMBER,
    name VARCHAR2(50) NOT NULL,
    age NUMBER(3)
);
```
    范例：為表中增加主鍵約束
```
ALTER TABLE member ADD CONSTRAINT pk_mid PRIMARY KEY(mid);
```
    增加數據：
```
INSERT INTO member(mid,name,age) VALUES(1,'張三',30);
INSERT INTO member(mid,name,age) VALUES(2,'李四',300);
```
    現在在member表中已經存在了年齡上的非法數據，所以下面為member表增加檢查約束：
```
ALTER TABLE member ADD CONSTRAINT ck_age CHECK(age BETWEEN 0 AND 250);
```
    這個時候在表中已經存在了違反約束的數據，所以肯定無法增加。
    
    范例：刪除member表中的mid上的主鍵約束
```
ALTER TABLE member DROP CONSTRAINT pk_mid;
```
    可是，跟表結構一樣，約束最好也不要修改，而且記住，表建立的同時一定要將約束定義好，以後的使用之中建議就不要去改變了。
    
    七、查詢約束
    
    在Oracle之中所有的對象都會在數據字典之中保存，而約束也是一樣的，所以如果要想知道有哪些約束，可以直接查詢“user_constraints”數據字典：
```
SELECT owner,constraint_name,table_name FROM user_constraints;
```
    但是這個查詢出來的約束只是告訴了你名字，而並沒有告訴在哪個字段上有此約束，所以此時可以查看另外一張數據字典表“user_cons_columns”；
```
COL owner FOR A15;
COL constraint_name FOR A15;
COL table_name FOR A15;
COL column_name FOR A15;
SELECT owner,constraint_name,table_name,column_name FROM user_cons_columns;
```
    這些維護工作大部分由專門的DBA負責。