数据库外键依赖场景下表创建顺序对级联删除执行顺序的影响解析

-- 创建主表
CREATE TABLE "TEST1" (
  "DOMAIN_NAME" VARCHAR2(255) NOT NULL ENABLE,
  "DOMAIN_ID" VARCHAR2(26) NOT NULL ENABLE,
  CONSTRAINT "PK_TEST1_DOMAIN_ID" PRIMARY KEY ("DOMAIN_ID"),
  CONSTRAINT "UN_TEST1_DOMAIN_NAME" UNIQUE ("DOMAIN_NAME")
);

-- 创建子表 TEST3，并建立对 TEST1 的级联删除外键
CREATE TABLE "TEST3" (
  "ID" NUMBER(11) NOT NULL ENABLE,
  "DOMAIN_ID" VARCHAR2(26) NOT NULL ENABLE,
  CONSTRAINT "PK_TEST3_ID" PRIMARY KEY ("ID"),
  CONSTRAINT "FK_TEST3_DOMAIN_ID" FOREIGN KEY ("DOMAIN_ID") REFERENCES "TEST1"("DOMAIN_ID") ON DELETE CASCADE
);

-- 创建子表 TEST2，并建立对 TEST1 的级联删除外键
CREATE TABLE "TEST1" (
  "DOMAIN_NAME" VARCHAR2(255) NOT NULL ENABLE,
  "DOMAIN_ID" VARCHAR2(26) NOT NULL ENABLE,
  CONSTRAINT "PK_TEST2_DOMAIN_NAME" PRIMARY KEY ("DOMAIN_NAME"),
  CONSTRAINT "FK_TEST2_DOMAIN_ID" FOREIGN KEY ("DOMAIN_ID") REFERENCES "TEST1"("DOMAIN_ID") ON DELETE CASCADE
);

-- 为 TEST3 表增加字段并建立对 TEST2 的外键约束（非级联删除）
ALTER TABLE "TEST3" ADD "DOMAIN_NAME" VARCHAR2(255) NOT NULL DEFAULT NULL;
ALTER TABLE "TEST3" ADD CONSTRAINT "FK_TEST3_DOMAIN_NAME" FOREIGN KEY ("DOMAIN_NAME") REFERENCES "TEST2" ("DOMAIN_NAME");

-- 插入测试数据
INSERT INTO TEST1 VALUES('domain1', '111');
INSERT INTO TEST2 VALUES('domain1', '111');
INSERT INTO TEST3 VALUES(1, '111', 'domain1');
COMMIT;

-- 执行删除操作，可能触发 ORA-02292 错误
-- DELETE FROM TEST1 WHERE domain_id = '111';