SQL ON DELETE CASCADE 不起作用

Question

我目前正在使用 Oracle SQL Developer 编写数据库，我想对我的外键使用 ON DELETE CASCADE，但它显然不起作用。以下是有关说法。您可能会注意到没有更多的表，只有这两个用于测试。

CREATE TABLE pizza
(   size NUMBER(3),
    price NUMBER(5,2),
    CONSTRAINT pk_pizza PRIMARY KEY (size, price)
);
CREATE TABLE pizzacondiment 
(   condiment VARCHAR(30),
    pizzasize NUMBER(3),
    pizzaprice NUMBER(5,2),
    CONSTRAINT pk_condiments PRIMARY KEY (condiment, pizzasize, pizzaprice)
    CONSTRAINT fk_condiments_pizza FOREIGN KEY (pizzasize, pizzaprice)
    REFERENCES pizza (size, price)
    ON DELETE CASCADE;
);

现在，当我想放下比萨饼时，输出显示:

ORA-02449: unique/primary keys in table referenced by foreign keys
02449. 00000 -  "unique/primary keys in table referenced by foreign keys"


*Cause:    An attempt was made to drop a table with unique or
           primary keys referenced by foreign keys in another table.


*Action:   Before performing the above operations the table, drop the
           foreign key constraints in other tables. You can see what
           constraints are referencing a table by issuing the following
           command:
           SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";

Answer 1

ON DELETE CASCADE 适用于行级 DML 操作，但不会像 DROP 那样影响 DDL。我将添加一个示例，说明如何应用 ON DELETE，然后查看可以对 drop 语句进行的更改，以断开 pizza 和 pizzacondiment< 之间的链接 但留下一个孤立的 pizzacondiment 处于可查询状态。

创建表后:

CREATE TABLE PIZZA
(
  "SIZE" NUMBER(3),
  PRICE  NUMBER(5, 2),
  CONSTRAINT PK_PIZZA PRIMARY KEY ("SIZE", PRICE)
);

CREATE TABLE PIZZACONDIMENT
(
  CONDIMENT  VARCHAR(30),
  PIZZASIZE  NUMBER(3),
  PIZZAPRICE NUMBER(5, 2),
  CONSTRAINT PK_CONDIMENTS PRIMARY KEY (CONDIMENT, PIZZASIZE, PIZZAPRICE),
  CONSTRAINT FK_CONDIMENTS_PIZZA FOREIGN KEY (PIZZASIZE, PIZZAPRICE)
  REFERENCES PIZZA ("SIZE", PRICE)
  ON DELETE CASCADE
);

并添加一些测试数据:

INSERT INTO PIZZA VALUES (11,1.25);
INSERT INTO PIZZA VALUES (219,111.11);
INSERT INTO PIZZA VALUES (50,7.50);


INSERT INTO PIZZACONDIMENT VALUES ('Spinach',50,7.50);
INSERT INTO PIZZACONDIMENT VALUES ('Peppers',50,7.50);
INSERT INTO PIZZACONDIMENT VALUES ('Onions',50,7.50);
INSERT INTO PIZZACONDIMENT VALUES ('Tomatoes',219,111.11);
INSERT INTO PIZZACONDIMENT VALUES ('Mushrooms',11,1.25);
INSERT INTO PIZZACONDIMENT VALUES ('Olives',11,1.25);

当我们删除 size=11 的披萨时，我们可以看到 ON DELETE CASCADE 的作用:

SELECT COUNT(*) AS CONDIMENT_COUNT FROM PIZZACONDIMENT WHERE PIZZASIZE = 11;

CONDIMENT_COUNT  
2                

然后进行删除:

DELETE FROM PIZZA WHERE "SIZE" = 11;
1 row deleted.

再次检查子表:

SELECT COUNT(*) AS CONDIMENT_COUNT FROM PIZZACONDIMENT WHERE PIZZASIZE = 11;

CONDIMENT_COUNT  
0             

正如您提到的，您想要DROP PIZZA 表但留下一个孤儿PIZZACONDIMENT，您可以通过调整来实现到您的 DROP 语法，使用 CASCADE CONSTRAINTS。这将删除任何会阻止 DROP 的外键，留下孤立的子表。

DROP TABLE PIZZA CASCADE CONSTRAINTS;

Table PIZZA dropped.

pizzacondiment 是孤立的但仍然可用:

SELECT * FROM PIZZACONDIMENT WHERE CONDIMENT = 'Spinach'; 

CONDIMENT  PIZZASIZE  PIZZAPRICE  
Spinach    50         7.5