一、前言
在Oracle数据库中,外键是用来实现參照完整性的方法之中的一个。打个比喻。外键是指定义外键的表的列的值必须在还有一个表中出现。
被參照的表称之为父表(parent table),创建外键的表称之为子表(child table)。子表中的外键关联了父表中的主键。
外键能够在创建表时定义或者通过ALTER TABLE语句创建。
外键(FK)是用于建立或加强两个表数据之间的链接的一列或多列。
注意:
1、在用外键进行表之间的数据关联的时候,如果外键没有定义级联删除,只能先删除有外键定义的子表,才能能删除有被外键引用的基表。
2、如果外键定义了级联删除,删除了被外键引用的基表之后,对应的子表中的数据也没有了。如果只删除有外键定义的子表,则被外键引用的基表中的数据依旧存在。
二、具体应用
2.1、外键的创建
使用CREATE TABLE语句创建:
CREATE TABLE table_name
(
column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT fk_column
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n)
);
使用ALTER TABLE 语句创建:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n);
2.2、级联删除外键
使用CREAT TABLE语句定义级联删除:
CREATE TABLE table_name
(
column1 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT fk_column
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n)
ON DELETE CASCADE
);
使用ALTER TABLE语句定义级联删除:
ALTER TABLE products
ADD CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier(supplier_id)
ON DELETE CASCADE;
2.3、删除外键
alter table "表名" drop constraint "外键名"
或
alter table "表名" drop primary key
2.4、禁用外键
ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;
三、demo 演示
-- 先创建一个表sm_grade
create table sm_grade(
gradeId number(8) not null primary key,
gradeName nvarchar2(20)
);
-- 再创建有外键的sm_student(和sm_grade的gradeId关联)
create table sm_student(
studentId number(8) not null primary Key,
studentName Nvarchar2(20),
gender Nvarchar2(2),
gradeId Number(8)
);
-- 添加外键,这种外键不会级联删除数据
alter table sm_student add constraint fk_gradeId foreign key(gradeId) references sm_grade(gradeId);
-- 正常插入一条数据
insert Into sm_grade select 1, 'grade1' from dual;
insert Into sm_student select 1001, 'wanglc', 'm', 1 from dual;
select * from sm_grade purge;
select * from sm_student purge;
-- 插入一条无外键引用的数据
insert Into sm_student select 1002, 'baby', 'm', 2 from dual; -- ORA-02291: 违反完整约束条件 (SYSTEM.FK_GRADEID) - 未找到父项关键字
-- 删除有外键的数据,删除的表有外键,可以正常删除
delete from sm_student where studentId = 1001;
-- 恢复有外键的数据
insert Into sm_student select 1001, 'wanglc', 'm', 1 from dual;
-- 删除存在外键引用的数据
delete from sm_grade where gradeId=1; -- ORA-02292: 违反完整约束条件 (SYSTEM.FK_GRADEID) - 已找到子记录
-- 删除的顺序很重要
delete from sm_student purge;
delete from sm_grade purge;
COMMIT;
-- 删除不会级联删除的外键,创建一个会级联删除的外键
ALTER TABLE sm_student DROP CONSTRAINT fk_gradeId;
alter table sm_student add constraint fk_gradeId foreign key(gradeId) references sm_grade(gradeId) ON DELETE CASCADE;
-- 准备数据
insert Into sm_grade select 1, 'grade1' from dual;
insert Into sm_student select 1001, 'wanglc', 'm', 1 from dual;
-- 因为有级联,可以删除基表,同时,引用表中也没有数据了
delete from sm_grade where gradeId=1;
COMMIT;
select * from sm_grade purge;
select * from sm_student purge;
-- 准备数据
insert Into sm_grade select 1, 'grade1' from dual;
insert Into sm_student select 1001, 'wanglc', 'm', 1 from dual;
-- 这种删除方式,基表中的数据依旧存在。
delete from sm_student where studentId = 1001;
COMMIT;
select * from sm_grade purge;
select * from sm_student purge;