详解外键约束（foreign key）

详解外键约束（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.

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26110315/viewspace-715746/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26110315/viewspace-715746/