ORACLE表间关系

最新推荐文章于 2022-10-23 14:27:28 发布

iteye_20954

最新推荐文章于 2022-10-23 14:27:28 发布

阅读量132

点赞数

文章标签： Oracle SQL

簡介

<term key="constraint">限制</term>主要有以下３種功能：

為資料庫內的表格建立實在的關係，例如 DEPT 和 EMP 表格的父子關係。
避免一些您不想要的資料進入資料庫，例如您不想資料庫儲存一個沒有名字的僱員紀錄。
避免刪除一些有關連性的資料，例如 EMP 與 DEPT 是有關連的，那就要避免您刪除 DEPT 表格，否則 EMP 的 DEPTNO 欄就不能對應到 DEPT 了。

總括來說，限制能夠維繫您的資料庫，和避免錯誤的資料進入資料庫。

Oracle 有以下５種基本的限制：

Not Null - 非空
Unique - 唯一的
Check - 檢查
Primary Key - 主要鍵
- 組合 Primary Key
Foreigh Key - 外來鍵

Not Null

您可以指定某個欄不可儲存 NULL 值，當您嘗試把該欄設為 NULL 值時，Oracle 會顯示錯誤訊息。以下示範如何把 EMPLOYEE_ID 一欄定義 NOT NULL 限制，並嘗試把它設為 NULL，引致 ORA-01400 錯誤：

SQL> CREATE TABLE EMPLOYEES (
  2      EMPLOYEE_ID  NUMBER(5)  NOT NULL
  3  );

Table created.

SQL> INSERT INTO EMPLOYEES VALUES(NULL);
INSERT INTO EMPLOYEES VALUES(NULL)
            *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."EMPLOYEES"."EMPLOYEE_ID")

Unique

如果您想某欄的所有資料都必須是唯一的，即是不可出現重覆的值，那就可以把該欄定義 UNIQUE 限制，例如僱員識別碼一欄，因為它一般會用來識別不同的僱員，所以把它定義 UNIQUE 限制，Oracle 就會防止它含有重覆的值。不過，UNIQUE 限制只會檢查那些不是 NULL 的值是否重覆，而不會檢查 NULL 值。

SQL> CREATE TABLE EMPLOYEES (
  2      EMPLOYEE_ID  NUMBER(5)  UNIQUE
  3  );

Table created.

SQL> INSERT INTO EMPLOYEES VALUES(1);

1 row created.

SQL> INSERT INTO EMPLOYEES VALUES(1);
INSERT INTO EMPLOYEES VALUES(1)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C001265) violated

留意錯誤訊息出現的 SCOTT.SYS_C001265 ，其中的 SYS_C001265 是個限制名稱，其實您可以在定義一個限制時同時定義它的名稱，那麼當您違反了該限制時，Oracle 就會顯示該限制名稱，那就容易知道違反了什麼限制了。如果您沒有定義限制名稱，那麼 Oracle 會自動定義 SYS_Cxxxxxx 這樣子的名稱。

Check

Check 解作「檢查」，例如您可以檢查某個欄位的值是否符合某個條件，以下示範如何檢查 SALARY 一欄是否符合「少於 10000」這個條件：

SQL> CREATE TABLE EMPLOYEES (
  2      SALARY  NUMBER(6)  CHECK (SALARY <= 10000)
  3  );

Table created.

SQL> INSERT INTO EMPLOYEES VALUES(9000);

1 row created.

SQL> INSERT INTO EMPLOYEES VALUES(11000);
INSERT INTO EMPLOYEES VALUES(11000)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C001267) violated

Check 限制只能檢查一些簡單的條件，例如把 SALARY 與 10000 這個固定的值比較，它不能與其它欄的值或變數比較。

Primary Key

Primary Key 限制的特性就是 Not Null 和 Unique 兩者的結合，即是說，定義為 Primary Key 的欄會同時受到 Not Null 和 Unique 的限制。以下示範 EMPLOYEE_ID 欄不能設為 NULL 值和不可含有重覆的值：

SQL> CREATE TABLE EMPLOYEES (
  2      EMPLOYEE_ID  NUMBER(5)  PRIMARY KEY
  3  );

Table created.

SQL> INSERT INTO EMPLOYEES VALUES(NULL);
INSERT INTO EMPLOYEES VALUES(NULL)
            *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."EMPLOYEES"."EMPLOYEE_ID")


SQL> INSERT INTO EMPLOYEES VALUES(1);

1 row created.

SQL> INSERT INTO EMPLOYEES VALUES(1);
INSERT INTO EMPLOYEES VALUES(1)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C001268) violated

還有一點，一個表格只可有一個 Primary Key。

組合 Primary Key

之前的例子示範如何把１個欄定義為 Primary Key，還一有種叫組合 Primary Key，讓您把２個或以上的欄定義為 Primary Key，即是說，這些欄值的組合不可以重覆，而每個欄也不可有 NULL。例如一個僱員的姓氏部份或名字部份也可能和其他人相同，如果您不想有兩個人的姓氏和名字完全一樣，可以把 FIRST_NAME 和 LAST_NAME 定義為組合 PRIMARY KEY。

以下示範定義組合 Primary Key 限制，同時定義這個限制的名稱為 PK_EMPLOYEES，當您違反這個限制時，Oracle 就會在錯誤訊息顯示這個名稱。

SQL> CREATE TABLE EMPLOYEES (
  2      FIRST_NAME  VARCHAR2(20),
  3      LAST_NAME   VARCHAR2(20),
  4      CONSTRAINT PK_EMPLOYEES PRIMARY KEY (FIRST_NAME, LAST_NAME)
  5  );

Table created.

SQL> INSERT INTO EMPLOYEES VALUES('TOM', 'LEE');

1 row created.

SQL> INSERT INTO EMPLOYEES VALUES('LEE', 'TOM');

1 row created.

SQL> INSERT INTO EMPLOYEES VALUES('LEE', 'JOHN');

1 row created.

SQL> INSERT INTO EMPLOYEES VALUES('TOM', 'LEE');
INSERT INTO EMPLOYEES VALUES('TOM', 'LEE')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_EMPLOYEES) violated

留意組合 Primary Key 的語法比較特別，其它限制的語法都是把限制寫在欄位之後，成為該欄的一部份，是因為這些限制都是定義在一個欄，但組合 Primary Key 是定義在多個欄的，所以不會寫在任何一個欄之後。

Foreign Key

Foreign Key 限制是用來表示兩個表格的父子關係，即好像 DEPT 和 EMP 表格，在這個父子關係有以下特性：

兩個表格會有一個<term key="shared_column">「共通的欄」 (Shared Column)</term>，就是 DEPTNO （欄名不一定要相同，但資料型態就必須相同）
DEPTNO 在父表格 DEPT 是個 Primary Key（受 Primary Key 限制）
DEPTNO 在子表格 EMP 是個 Foreign Key（受 Foreign Key 限制）
在子表格 EMP 的 DEPTNO 欄內所有的值（除了 NULL 之外），都必須在在父表格的 DEPTNO 找到。（但父表格的值不必在子表格的 DEPTNO 欄找到）

以下示範在子表格分享欄插入一個在父表格分享欄找不到的值，造成錯誤：

SQL> CREATE TABLE DEPARTMENTS (
  2      DEPARTMENT_NUM   NUMBER(5)  PRIMARY KEY,
  3      DEPARTMENT_NAME  VARCHAR2(20)
  4  );

Table created.

SQL> CREATE TABLE EMPLOYEES (
  2      EMPLOYEE_NAME   VARCHAR2(20),
  3      DEPARTMENT_NUM  NUMBER(5)
  4          REFERENCES DEPARTMENTS (DEPARTMENT_NUM)
  5  );

Table created.

SQL> INSERT INTO DEPARTMENTS VALUES(10, 'IT');

1 row created.

SQL> INSERT INTO DEPARTMENTS VALUES(20, 'SALES');

1 row created.

SQL> INSERT INTO EMPLOYEES VALUES('TOM', 10);

1 row created.

SQL> INSERT INTO EMPLOYEES VALUES('SAM', 20);

1 row created.

SQL> INSERT INTO EMPLOYEES VALUES('JOHN', 30);
INSERT INTO EMPLOYEES VALUES('JOHN', 30)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.SYS_C001272) violated - parent key not found

DEPARTMENT_NUM 是分享欄，因為在父表格 DEPARTMENTS 的分享欄找不到 30，所以把子表格的分享欄設為 30 會造成錯誤。

當父表格的值被刪除

如果您嘗試刪除父表格 DEPARTMENTS 的 DEPARTMENT_NUM = 10 那一行，那麼子表格 EMPLOYEES 的 DEPARTMENT_NUM = 10 就不能在 DEPARTMENTS 找到了，違反了 Foreign Key 的限制，所以 Oracle 會防止您這樣做的：

SQL> DELETE FROM DEPARTMENTS WHERE DEPARTMENT_NUM = 10;
DELETE FROM DEPARTMENTS WHERE DEPARTMENT_NUM = 10
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.SYS_C001306) violated - child record found

child record found 的意思是「找到子記錄」，而子記錄就是指子表格 EMPLOYEES 裡 DEPARTMENT_NUM = 10 的記錄。

Oracle 讓您設定２種處理這個情況的方法：

ON DELETE SET NULL - 當父表格的行被刪除時，Oracle 會把子表格所有對應的值設為 NULL。
ON DELETE CASCADE - 當父表格的行被刪除時，Oracle 會把子表格所有對應的行刪除。

ON DELETE SET NULL

SQL> CREATE TABLE DEPARTMENTS (
  2      DEPARTMENT_NUM   NUMBER(5)  PRIMARY KEY,
  3      DEPARTMENT_NAME  VARCHAR2(20)
  4  );

Table created.

SQL> CREATE TABLE EMPLOYEES (
  2      EMPLOYEE_NAME   VARCHAR2(20),
  3      DEPARTMENT_NUM  NUMBER(5)
  4          REFERENCES DEPARTMENTS (DEPARTMENT_NUM) ON DELETE SET NULL
  5  );

Table created.

SQL> INSERT INTO DEPARTMENTS VALUES(10, 'IT');

1 row created.

SQL> INSERT INTO DEPARTMENTS VALUES(20, 'SALES');

1 row created.

SQL> INSERT INTO EMPLOYEES VALUES('TOM', 10);

1 row created.

SQL> INSERT INTO EMPLOYEES VALUES('SAM', 20);

1 row created.

SQL> DELETE FROM DEPARTMENTS WHERE DEPARTMENT_NUM = 10;

1 row deleted.

SQL> SELECT * FROM EMPLOYEES;

EMPLOYEE_NAME        DEPARTMENT_NUM
-------------------- --------------
TOM
SAM                              20

Oracle 自動把 EMPLOYEES 的 DEPARTMENT_NUM = 10 設為 NULL。

ON DELETE CASCADE

SQL> CREATE TABLE DEPARTMENTS (
  2      DEPARTMENT_NUM   NUMBER(5)  PRIMARY KEY,
  3      DEPARTMENT_NAME  VARCHAR2(20)
  4  );

Table created.

SQL> CREATE TABLE EMPLOYEES (
  2      EMPLOYEE_NAME   VARCHAR2(20),
  3      DEPARTMENT_NUM  NUMBER(5)
  4          REFERENCES DEPARTMENTS (DEPARTMENT_NUM) ON DELETE CASCADE
  5  );

Table created.

SQL> INSERT INTO DEPARTMENTS VALUES(10, 'IT');

1 row created.

SQL> INSERT INTO DEPARTMENTS VALUES(20, 'SALES');

1 row created.

SQL> INSERT INTO EMPLOYEES VALUES('TOM', 10);

1 row created.

SQL> INSERT INTO EMPLOYEES VALUES('SAM', 20);

1 row created.

SQL> DELETE FROM DEPARTMENTS WHERE DEPARTMENT_NUM = 10;

1 row deleted.

SQL> SELECT * FROM EMPLOYEES;

EMPLOYEE_NAME        DEPARTMENT_NUM
-------------------- --------------
SAM                              20

Oracle 自動刪除 EMPLOYEES 的 DEPARTMENT_NUM = 10 那行。

欄限制與表格限制

以上介紹了５種限制，它們的語法都差不多，除了組合 Primary Key 比較特別之外，其餘的限制都是寫在 CREATE TABLE 內的欄名後面。

其實，限制的語法有２種：<term key="column_constraint">「欄限制」(Column Constraint)</term> 和 <term key="table_constraint">「表格限制」(Table Constraint)</term> 。

限制／說明	欄限制語法例子	表格限語法例子
語法不同之處	限制是寫在欄名之後，成為該欄的一部份。	限制是寫在所有欄之後的，而且含有 CONSTRAINT 子句。
NOT NULL	CREATE TABLE TABLE_NAME ( COLUMN_NAME NUMBER NOT NULL );	不適用
UNIQUE	CREATE TABLE TABLE_NAME ( COLUMN_NAME NUMBER UNIQUE );	CREATE TABLE TABLE_NAME ( COLUMN_NAME NUMBER, CONSTRAINT CONS_NAME UNIQUE );
CHECK	CREATE TABLE TABLE_NAME ( COLUMN_NAME NUMBER CHECK (COLUMN_NAME < 10000) );	CREATE TABLE TABLE_NAME ( COLUMN_NAME NUMBER, CONSTRAINT CONS_NAME CHECK (COLUMN_NAME < 10000) );
PRIMARY KEY	CREATE TABLE TABLE_NAME ( COLUMN_NAME NUMBER PRIMARY KEY );	CREATE TABLE TABLE_NAME ( COLUMN_NAME NUMBER, CONSTRAINT CONS_NAME PRIMARY KEY (COLUMN_NAME) );
組合 PRIMARY KEY	不適用	CREATE TABLE TABLE_NAME ( COLUMN_NAME NUMBER, COLUMN2_NAME NUMBER, CONSTRAINT CONS_NAME PRIMARY KEY (COLUMN_NAME, COLUMN2_NAME) );
FOREIGN KEY	CREATE TABLE TABLE_NAME ( COLUMN_NAME NUMBER REFERENCES TABLE2_NAME (COLUMN2_NAME) );	CREATE TABLE TABLE_NAME ( COLUMN_NAME NUMBER, CONSTRAINT CONS_NAME REFERENCES TABLE2_NAME (COLUMN2_NAME) );

限制與索引

請參考「索引」一章

加入限制

您除了可以在 CREATE TABLE 時定義限制，也可以在表格建立了之後才加入限制。加入限制的語法是 ALTER TABLE ADD CONSTRAINT，而 NOT NULL 限制則要用 ALTER TABLE MODIFIY ：

SQL> CREATE TABLE DEPARTMENTS (
  2      DEPARTMENT_NUM   NUMBER(5)  PRIMARY KEY,
  3      DEPARTMENT_NAME  VARCHAR2(20)
  4  );

Table created.

SQL> CREATE TABLE EMPLOYEES (
  2      EMPLOYEE_ID  NUMBER(5),
  3      LAST_NAME    VARCHAR2(20),
  4      FIRST_NAME   VARCHAR2(20),
  5      SALARY       NUMBER(6),
  6      DEPARTMENT_NUM    NUMBER(5),
  7      GOVERNMENT_ID     NUMBER(10)
  8  );

Table created.

SQL> ALTER TABLE EMPLOYEES MODIFY 
  2  (LAST_NAME  NOT NULL)
  3  ;

Table altered.

SQL> ALTER TABLE EMPLOYEES ADD
  2  CONSTRAINT UK_EMPLOYEES_01 UNIQUE (GOVERNMENT_ID)
  3  ;

Table altered.

SQL> ALTER TABLE EMPLOYEES ADD
  2  CONSTRAINT CK_EMPLOYEES_01 CHECK (SALARY <= 10000)
  3  ;

Table altered.

SQL> ALTER TABLE EMPLOYEES ADD
  2  CONSTRAINT PK_EMPLOYEES PRIMARY KEY (EMPLOYEE_ID)
  3  ;

Table altered.

SQL> ALTER TABLE EMPLOYEES ADD
  2  CONSTRAINT FK_EMPLOYEES_01 FOREIGN KEY (DEPARTMENT_NUM)
  3  REFERENCES  DEPARTMENTS (DEPARTMENT_NUM)
  4  ;

Table altered.

加入限制注意

如果某個表格內已經含有資料，那就要小心一點，因為這些資料可能違反了您想加入的規則，所以當您加入新的限制時必須符合以下條件：

Not Null - 加入限制的那一個欄不可含有 NULL。如果您想加入一個新的欄，而這欄是定義了 NOT NULL 限制，那就要用 DEFAULT 子句來指定預設值，Oracle 會為表格內的所有行的該欄都設為預設值。
Unique - 該欄的值不可重覆。（除了 NULL 值之外）
Check - 這個比較特別，因為 Oracle 不會檢查現有的值是否違反了限制，只會檢查之後新增或修改的值。
Primary Key - 必須同時符合 NOT NULL 和 UNIQUE。
Foreigh Key - 該欄的值必須在父表格的分享欄找到。（除了 NULL 值之外）

禁止限制

您可以啟動或禁止一個限制，當限制被禁止時，就變成好像沒有了該限制一樣。禁止限制的語法是 ALTER TABLE DISABLE：

SQL> ALTER TABLE EMPLOYEES DISABLE PRIMARY KEY;

Table altered.

SQL> ALTER TABLE EMPLOYEES DISABLE CONSTRAINT UK_EMPLOYEES_01;

Table altered.

禁止父表格的 Primary Key 限制

當您禁止一個父表格的 PRIMARY KEY，那就會認響到子表格的 FOREIGN KEY 關連性了，所以 Oracle 會防止您這樣做：

SQL> ALTER TABLE DEPARTMENTS DISABLE PRIMARY KEY;
ALTER TABLE DEPARTMENTS DISABLE PRIMARY KEY
*
ERROR at line 1:
ORA-02297: cannot disable constraint (SCOTT.SYS_C001286) - dependencies exist

如果您真的希望這樣做，就必須在 ALTER TABLE DISABLE 內加入 CASCADE 一詞。

SQL> ALTER TABLE DEPARTMENTS DISABLE PRIMARY KEY CASCADE;

Table altered.

啟動被禁止了的限制

您可以啟動一個被禁止了的限制，語法是 ALTER TABLE ENABLE：

SQL> ALTER TABLE DEPARTMENTS ENABLE PRIMARY KEY;

Table altered.

SQL> ALTER TABLE EMPLOYEES ENABLE CONSTRAINT UK_EMPLOYEES_01;

Table altered.

當現在的資料違反被禁止了的限制

有時，您會希望暫時禁止一個限制來做某些工作，例如當您要執行大量的 INSERT 句子，您可能會暫時禁止一個 PRIMARY KEY 來提高執行速度。不過，一旦您插入的資料違反了 PRIMARY KEY 限制，您就不能在之後啟動它了。

一旦出現這種情況，您可以使用一個特殊的表格，名叫 EXCEPTIONS，這個表格可以透過用 SQL*Plus 來執行「[ORACLE HOME 目錄]rdbmsadminutlexcpt.sql」這個檔案來產生：

SQL> @@C:ORACLEORA81RDBMSADMINUTLEXCPT

Table created.

SQL> DESC EXCEPTIONS

 Name       Null? Type
 ---------- ----- ----------------
 ROW_ID           ROWID
 OWNER            VARCHAR2(30)
 TABLE_NAME       VARCHAR2(30)
 CONSTRAINT       VARCHAR2(30)

建立了 EXCEPTIONS 表格，以下嘗試產生上述的情況：

SQL> CREATE TABLE EMPLOYEES (
  2      EMPLOYEE_ID  NUMBER(5)
  3  );

Table created.

SQL> INSERT INTO EMPLOYEES VALUES(1);

1 row created.

SQL> INSERT INTO EMPLOYEES VALUES(2);

1 row created.

SQL> ALTER TABLE EMPLOYEES ADD CONSTRAINT
  2  PK_EMPLOYEES PRIMARY KEY (EMPLOYEE_ID);

Table altered.

SQL> SELECT * FROM EMPLOYEES;

EMPLOYEE_ID
-----------
          1
          2

SQL> ALTER TABLE EMPLOYEES DISABLE CONSTRAINT PK_EMPLOYEES;

Table altered.

SQL> INSERT INTO EMPLOYEES VALUES(1);

1 row created.

SQL> ALTER TABLE EMPLOYEES ENABLE CONSTRAINT PK_EMPLOYEES
  2  EXCEPTIONS INTO EXCEPTIONS;
ALTER TABLE EMPLOYEES ENABLE CONSTRAINT PK_EMPLOYEES
*
ERROR at line 1:
ORA-02437: cannot validate (SCOTT.PK_EMPLOYEES) -
primary key violated

SQL> SELECT  E.TABLE_NAME, E.ROW_ID, A.EMPLOYEE_ID
  2  FROM  EXCEPTIONS E, EMPLOYEES A
  3  WHERE  E.ROW_ID = A.ROWID
  4  ;

TABLE_NAME ROW_ID             EMPLOYEE_ID
---------- ------------------ -----------
EMPLOYEES  AAAGJRAABAAAJLcAAA           1
EMPLOYEES  AAAGJRAABAAAJLcAAC           1

透過 EXCEPTIONS 表格，就可以找到有問題的行，然後修正它們了。

刪除限制

刪除限制的語法是 ALTER TABLE DROP：

SQL> ALTER TABLE EMPLOYEES DROP CONSTRAINT UK_EMPLOYEES_01;

Table altered.

SQL> ALTER TABLE EMPLOYEES DROP PRIMARY KEY CASCADE;

Table altered.

SQL> ALTER TABLE EMPLOYEES DROP CONSTRAINT CK_EMPLOYEES_01;

Table altered.

刪除 NOT NULL 限制的語法比較特別，必須用 ALTER TABLE MODIFY：

SQL> ALTER TABLE EMPLOYEES MODIFY (LAST_NAME NULL);

Table altered.

刪除父表格

正如禁止父表格的 PRIMARY KEY一樣，刪除父表格也會產生同樣的問題，就是子表格不能參考父表格，所以 Oracle 會防止您這樣做：

SQL> DROP TABLE DEPARTMENTS;
DROP TABLE DEPARTMENTS
           *
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

如果您真的希望這樣做，就必須在 DROP TABLE 內加入 CASCADE CONSTRAINT 一詞，那麼子表格的 FOREIGN KEY 限制也會同時被刪除。

SQL> DROP TABLE DEPARTMENTS CASCADE CONSTRAINTS;

Table dropped.

除了這種方法外，您也可以禁止或刪除子表格的 FOREIGN KEY。

限制可遲性

<term key="deferability">「可遲性」(Deferability)</term>

iteye_20954

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
ORACLE表间关系

簡介&lt;term key="constraint"&gt;限制&lt;/term&gt;主要有以下３種功能：為資料庫內的表格建立實在的關係，例如 DEPT 和 EMP 表格的父子關係。避免一些您不想要的資料進入資料庫，例如您不想資料庫儲存一個沒有名字的僱員紀錄。避免刪除一些有關連性的資料，例如 EMP 與 DEPT 是有關連的，那就要避免您刪除 DEPT 表格，否則 EMP 的 ...
复制链接

扫一扫