详解外键约束(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/