詳解外來鍵約束（foreign key）

外來鍵約束（foreign key）是比較複雜的約束.下面演示外來鍵約束的相關特性。

顧客表：

CREATE TABLE Customers

(Customer# NUMBER(4),

LastName VARCHAR2(10) NOT NULL,

FirstName VARCHAR2(10) NOT NULL,

Address VARCHAR2(20),

City VARCHAR2(12),

State VARCHAR2(2),

Zip VARCHAR2(5),

Referred NUMBER(4),

Region CHAR(2),

Email VARCHAR2(30),

CONSTRAINT customers_customer#_pk PRIMARY KEY(customer#),

CONSTRAINT customers_region_ck

CHECK (region IN ('N', 'NW', 'NE', 'S', 'SE', 'SW', 'W', 'E')) );

訂單表：

CREATE TABLE Orders

(Order# NUMBER(4),

Customer# NUMBER(4),

OrderDate DATE NOT NULL,

ShipDate DATE,

ShipStreet VARCHAR2(18),

ShipCity VARCHAR2(15),

ShipState VARCHAR2(2),

ShipZip VARCHAR2(5),

ShipCost NUMBER(4,2),

CONSTRAINT orders_order#_pk PRIMARY KEY(order#));

客戶與訂單之間的關係是一對多的關係，一個客戶可以下多個訂單。

我們應該把外來鍵約束新增到多的那一端。被參照的表（客戶表）為父表，相應的訂單表為子表。

SQL> alter table orders add constraint orders_customer#_fk foreign key(customer#)

2 references customers(customer#);

Table altered.

往訂單表新增一條記錄：

SQL> insert into orders

2 values (1000,1005,'31-MAR-09','02-APR-09','1201 ORANGE AVE', 'SEATTLE', 'WA', '98114' , 2.00);

insert into orders

*

ERROR at line 1:

ORA-02291: integrity constraint (HR.ORDERS_CUSTOMER#_FK) violated - parent key not found

違反了參照約束，父鍵不存在。因為訂單表的customer#列參照了客戶表的customer#列。客戶表中還沒有資料客戶1005的記錄。

SQL> insert into customers

2 values (1005, 'GIRARD', 'CINDY', 'P.O. BOX 851', 'SEATTLE', 'WA', '98115', NULL, 'NW', 'cing101@zep.net');

1 row created.

SQL> insert into orders

2 values (1000,1005,'31-MAR-09','02-APR-09','1201 ORANGE AVE', 'SEATTLE', 'WA', '98114' , 2.00);

1 row created.

先往父表中插入一條記錄（customer#=1005），然後在新增客戶1005的訂單記錄。

下面刪除客戶表中customer#=1005的記錄。

SQL> delete from customers

2 where customer#='1005';

delete from customers

*

ERROR at line 1:

ORA-02292: integrity constraint (HR.ORDERS_CUSTOMER#_FK) violated - child record found

違反了參照約束，父鍵存在相應的子鍵。

此時如果要刪除被參照的記錄，可以先刪除子表中相應的記錄。然後在刪除父表中相應的記錄。

或者建立外來鍵約束的時候應該新增 on delete cascade子句。這時候選中的父表中的記錄會被刪除，

同時相應的參照父表中的子表記錄也會被刪除。

下面演示外來鍵約束帶有on delete cascade子句的時候的情形。

SQL> delete from orders

2 where order#='1000';

1 row deleted. //先刪除子表中的相應記錄。

SQL> delete from customers

2 where customer#='1005';

1 row deleted. //在刪除父表中的相應記錄。

先刪除訂單表中的外來鍵約束重新建立，帶on delete cascade子句的外來鍵約束。

SQL> alter table orders drop constraint orders_customer#_fk;

Table altered.

SQL> alter table orders add constraint orders_customer#_fk foreign key(customer#)

2 references customers(customer#) on delete cascade;

Table altered.

SQL> insert into customers

2 values (1005, 'GIRARD', 'CINDY', 'P.O. BOX 851', 'SEATTLE', 'WA', '98115', NULL, 'NW', 'cing101@zep.net');

1 row created.

SQL> insert into orders

2 values (1000,1005,'31-MAR-09','02-APR-09','1201 ORANGE AVE', 'SEATTLE', 'WA', '98114' , 2.00);

1 row created.

SQL> delete from customers

2 where customer#='1005'; //因為orders表中建立外來鍵約束的時候已經使用了on delete cascade子句所以可以直接刪除。

1 row deleted.

SQL> select * from customers;

no rows selected

SQL> select * from orders;

no rows selected

SQL> drop table customers;

drop table customers

*

ERROR at line 1:

ORA-02449: unique/primary keys in table referenced by foreign keys

被外來鍵參照的表（父表）不能透過普通的drop table 命令刪除。可以透過帶cascade constraint子句的drop table 命令刪除該表。

SQL> drop table customers cascade constraint;

Table dropped.

或者先刪除子表，再刪除父表。

詳解外來鍵約束（foreign key）

相關文章