Oracle筆記（十） 約束

標籤：針對   oid   否則   需要   範圍   enc   而且   表的操作   表結構   

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;

但是這樣做明顯很麻煩，因為對於一個未知的資料庫，如果要按照此類方式進行，則必須首Crowdsourced Security Testing道其父子關係，所以在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負責。

Oracle筆記（十） 約束