-- 先创建一个表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;