--创建基表
CREATE TABLE zyl_test(CODE VARCHAR2(10),NAME VARCHAR2(20));
--创建外键关联表
CREATE TABLE zyl_test_fk(ID VARCHAR2(10),NAME VARCHAR2(20));
--方案一:创建外键时,添加ON DELETE CASCADE,级联删除
ALTER TABLE zyl_test_fk ADD CONSTRAINTS fk_test FOREIGN KEY(ID) REFERENCES zyl_test(CODE) ON DELETE CASCADE;
--方案二:创建触发器
ALTER TABLE zyl_test_fk ADD CONSTRAINTS fk_test FOREIGN KEY(ID) REFERENCES zyl_test(CODE);
--触发器
CREATE OR REPLACE TRIGGER DEL_OTHERS
BEFORE DELETE ON ZYL_TEST
FOR EACH ROW
BEGIN
DELETE FROM ZYL_TEST_FK WHERE ID = :OLD.CODE;
END;
--初始化数据
INSERT INTO zyl_test VALUES('11','name');
INSERT INTO zyl_test_fk VALUES('11','name');
--查询数据
SELECT * from zyl_test;
SELECT * from zyl_test_fk;
--删除外键
ALTER TABLE zyl_test_fk DROP CONSTRAINTS fk_test;
--删除触发器
DROP TRIGGER DEL_OTHERS;
--删除数据
DELETE FROM zyl_test WHERE CODE = '11';