izuoyan

浏览: 9420393 次
性别:
来自: 上海

最近访客更多访客>>

GDGZWQZ

morelily

eternal1025

devcang

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

2012-06 ( 32)
2012-05 ( 145)
2012-04 ( 50)
更多存档...

ORACLE表间关系

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>

分享到：

Node Based ShaderGenerator Features list | JVM配置参数中文说明

2008-10-08 17:09
浏览 698
评论(0)
查看更多

发表评论

您还没有登录,请您登录后再发表评论

最近访客更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

ORACLE表间关系

簡介

Not Null

Unique

Check

Primary Key

組合 Primary Key

Foreign Key

當父表格的值被刪除

ON DELETE SET NULL

ON DELETE CASCADE

欄限制與表格限制

限制與索引

加入限制

加入限制注意

禁止限制

禁止父表格的 Primary Key 限制

啟動被禁止了的限制

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

刪除限制

刪除父表格

限制可遲性

评论

发表评论

相关推荐

最近访客 更多访客>>

博主相关

文章分类

社区版块

存档分类

最新评论

ORACLE表间关系

簡介

Not Null

Unique

Check

Primary Key

組合 Primary Key

Foreign Key

當父表格的值被刪除

ON DELETE SET NULL

ON DELETE CASCADE

欄限制與表格限制

限制與索引

加入限制

加入限制注意

禁止限制

禁止父表格的 Primary Key 限制

啟動被禁止了的限制

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

刪除限制

刪除父表格

限制可遲性

评论

发表评论

相关推荐

最近访客更多访客>>