Oracle级联删除

--创建基表
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';