详解外键约束(foreign key) 顾客表 TABLE Customers 订单表 TABLE Orders 客户与订单之间的关系是一对多的关系,一个客户可有下多个订单。 我们应该把外键约束添加到多的那一端。被参照的表(客户表)为父表,相应的订单表为子表。 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,to_date('2012-02-15',,'yyyy-mm-dd hh24:mi:ss'),to_date('2012-02-15',,'yyyy-mm-dd hh24:mi:ss'),'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,to_date('2012-02-15',,'yyyy-mm-dd hh24:mi:ss'),to_date('2012-02-15',,'yyyy-mm-dd hh24:mi:ss'),'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,to_date('2012-02-15',,'yyyy-mm-dd hh24:mi:ss'),to_date('2012-02-15',,'yyyy-mm-dd hh24:mi:ss'),'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. 或者先删除子表,再删除父表。 * 备注: 添加一般外键 SQL> alter table orders add constraint orders_customer#_fk foreign key(customer#) 2 references customers(customer#); 添加on delete cascade外键 SQL> alter table orders add constraint orders_customer#_fk foreign key(customer#) 2 references customers(customer#) on delete cascade; 一般外键,执行删除操作时,应先删除子表的数据,在删除主表数据,否则报错 on delete cascade外键,执行删除操作时可以直接删除主表的数据,同时子表数据自动删除。 |
|