一、外键约束简介:
外键的主要作用是保证数据的参照完整性,主表中有数据时,才可以在从表上插入相应的数据。外键支持的列,必须是主键列,以便获得唯一记录。
1.1 创建外键约束:
语法:alter table 表名 add constraint 约束名 foreign key (外键列名)references 主表 (主表主键列);
如:
先创建customer表:
SQL> create table customer (
2 customer_id number primary key,
3 customer_name varchar2(20),
4 customer_phone varchar2(20),
5 customer_address varchar2(50)
6 );
Table created
再创建purchase_order表:
SQL> create table purchase_order(
2 order_id number primary key,
3 product_name varchar2(20),
4 product_quantity number,
5 customer_id number
6 );
Table created
添加外键:
SQL> alter table purchase_order
2 add constraint fk_purchase_order
3 foreign key (customer_id)
4 references customer(customer_id);
Table altered
查看外键约束信息:
SQL> select table_name ,constraint_name,constraint_type,r_constraint_name
2 from user_constraints
3 where table_name='PURCHASE_ORDER';
TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE R_CONSTRAINT_NAME
------------------------------ ------------------------------ --------------- ------------------------------
PURCHASE_ORDER SYS_C005501 P
PURCHASE_ORDER FK_PURCHASE_ORDER R SYS_C005500
二、级联更新与级联删除:
为了保护数据的参照完整性提供2种校验类型——即时校验(immediate) 和 延迟校验(deferred),默认为即时校验。延迟校验是当整个事务结束时(commit),才验证数据的完整性。
1.级联更新:
语法:
alter table 表名 add constraint 外键名称 foreign key (外键列) references 主表名(主表主键列)
deferrable initially deferred;
SQL> alter table purchase_order
2 drop constraint fk_purchase_order; --删除外键
SQL> alter table purchase_order
2 add constraint fk_purchase_order
3 foreign key (customer_id)
4 references customer (customer_id)
5 deferrable initially deferred;
Table altered
2.级联删除:
在创建外键时直接指定级联删除,不用在删除时声明要级联删除。
alter table 表名 add constraint 外键名称 foreign key(外键列)references 主表名(主表主键列)
on delete cascade;
SQL> alter table purchase_order
2 drop constraint fk_purchase_order
3 ;
Table altered
SQL> alter table purchase_order
2 add constraint fk_purchase_order
3 foreign key (customer_id)
4 references customer(customer_id)
5 on delete cascade;
Table altered
三、外键约束的相关操作:
1.重命名:
SQL> alter table purchase_order rename constraint fk_purchase_order to fk_po;
2.禁用/启用外键:
在数据库迁移时用到,否则会降低执行效率。
SQL> alter table purchase_order disable constraint fk_po;
Table altered
SQL> alter table purchase_order enable constraint fk_po;
Table altered
3.删除外键:
SQL> alter table purchase_order
2 drop constraint fk_po;