PostgreSQL：外键/级联删除

Question

我有两个数据表，就像这里展示的一样：

DROP   TABLE  IF EXISTS schemas.book;
DROP   TABLE  IF EXISTS schemas.category;
DROP   SCHEMA IF EXISTS schemas;
CREATE SCHEMA schemas;

CREATE TABLE schemas.category (
  id          BIGSERIAL PRIMARY KEY,
  name        VARCHAR   NOT NULL,
  UNIQUE(name)
);

CREATE TABLE schemas.book (
  id          BIGSERIAL PRIMARY KEY,
  published   DATE      NOT NULL,
  category_id BIGINT    NOT NULL REFERENCES schemas.category
                            ON DELETE CASCADE 
                            ON UPDATE CASCADE,
  author      VARCHAR   NOT NULL,
  name        VARCHAR   NOT NULL,
  UNIQUE(published, author, name),
  FOREIGN KEY(category_id) REFERENCES schemas.category (id)
);

所以逻辑很简单，当用户删除类别x下的所有书籍后，x将从cats中删除，我尝试了上面的方法但没有成功，在我清除book表之后，category表仍然有内容，出了什么问题？

- juk

---

是的，请查看crosspost。 - juk

这个回答解决了你的问题吗？SQL ON DELETE CASCADE，删除发生的方式是什么？ - Martin Thoma

Answer 1

带有级联删除的外键意味着，如果父表中的记录被删除，则相应的子表中的记录将自动被删除。这被称为级联删除。

你说的是相反的，这不是指从子表中删除记录将导致从父表中删除记录。

UPDATE 1:

ON DELETE CASCADE选项用于指定当删除父表中对应行时是否要删除子表中的行。如果您不指定级联删除，则数据库服务器的默认行为会防止您删除引用了其他表的表中的数据。

如果您指定了此选项，则稍后在删除父表中的行时，数据库服务器还会删除与该行（外键）在子表中关联的任何行。级联删除功能的主要优点是它允许您减少需要执行删除操作的 SQL 语句的数量。

因此，它关注的是从父表而不是子表中删除行时会发生什么。

因此，在您的情况下，当用户从categories表中删除条目时，将从books表中删除行。

- Mariappan Subramanian

---

SQL 中没有树形结构。你应该尝试通过引用来重写这个答案，而不是使用"父节点"或"子节点"等术语。 - Martin

在PostgreSQL对象继承方面，确实存在父表，但这更加需要回答避免使用父/子术语并描述引用的原因。 - Joe Atzberger

我相信@Mari理解CASCADE的含义，但从术语上讲，这是一个不正确的答案。如上所示，在继承表中，当从父表中删除（同一）行时，子项始终会被删除。顺便说一下，我猜服务器的默认行为应该是特定于实际服务器的。在Postgres中，默认行为是RESTRICT。 - Martin

可能术语使用不正确（你们中没有人提供更好的替代方案），但它有助于理解意思。 - user12207064

术语模糊不清。层次结构的父类和子类都可能持有FK约束，因此这个术语并不能帮助理解哪个删除操作导致了另一个删除操作。更好的做法是称它们为“定义约束的表”（或关系的“所有者”）和“被引用的表”。 - Daniele Repici

Answer 2

摘自PostgreSQL 文档：

限制和级联删除是最常见的两种选项。[...] CASCADE表示当引用行被删除时，也应自动删除引用它的行。

这意味着如果你删除 schemas.category 中由 category_id 引用的行，在使用 ON DELETE CASCADE 的情况下，任何引用该行的行也将被删除。

示例：

CREATE SCHEMA shire;

CREATE TABLE shire.clans (
    id serial PRIMARY KEY,
    clan varchar
);

CREATE TABLE shire.hobbits (
    id serial PRIMARY KEY,
    hobbit varchar,
    clan_id integer REFERENCES shire.clans (id) ON DELETE CASCADE
);

DELETE FROM clans将会CASCADE到引用。

sauron@mordor> psql
sauron=# SELECT * FROM shire.clans;
 id |    clan    
----+------------
  1 | Baggins
  2 | Gamgi
(2 rows)

sauron=# SELECT * FROM shire.hobbits;
 id |  hobbit  | clan_id 
----+----------+---------
  1 | Bilbo    |       1
  2 | Frodo    |       1
  3 | Samwise  |       2
(3 rows)

sauron=# DELETE FROM shire.clans WHERE id = 1 RETURNING *;
 id |  clan   
----+---------
  1 | Baggins
(1 row)

DELETE 1
sauron=# SELECT * FROM shire.hobbits;
 id |  hobbit  | clan_id 
----+----------+---------
  3 | Samwise  |       2
(1 row)

如果你真的需要相反的结果（由数据库检查），你将不得不编写触发器！

- Martin

---

这个解释最终让我理解了 REFERENCES / CASCADE 语法的含义，非常感谢！ - Clint Eastwood

Answer 3

在我对Postgres 9.6的谦逊经验中，级联删除在实践中对于增长到非常庞大的表并不起作用。

更糟糕的是，在进行级联删除时，涉及的表会被锁定，因此这些表（以及可能整个数据库）将无法使用。

更糟糕的是，很难让Postgres告诉你在进行级联删除时它在做什么。如果花费了很长时间，是哪个表或哪些表导致了速度变慢？也许是在pg_stats信息中的某个地方？很难说清楚。

- Gordon Vidaver

---

这是正常工作的定义，对于具有适当事务的数据库而言。其次，这并不是对问题的回答。 - Martin

如果有人偶然看到这篇文章：可能你在5.3.5的文档结尾处忽略了这个解释： "由于删除参考表中的行或更新一个参考列将需要扫描参照表以查找与旧值匹配的行，因此通常最好也对参照列建立索引。" https://www.postgresql.org/docs/9.5/ddl-constraints.html - Michael Kraxner

Answer 4

PostgreSQL 锻造关键字 DELETE, UPDATE CASCADE

CREATE TABLE apps_user(
  user_id SERIAL PRIMARY KEY,
  username character varying(30),
  userpass character varying(50),
  created_on DATE
);

CREATE TABLE apps_profile(
    pro_id SERIAL PRIMARY KEY,
    user_id INT4 REFERENCES apps_user(user_id) ON DELETE CASCADE ON UPDATE CASCADE,
    firstname VARCHAR(30),
    lastname VARCHAR(50),
    email VARCHAR UNIQUE,
    dob DATE
);

- Ram Pukar

---

这个剪切粘贴的例子与问题无关，最重要的关键词被替换成一个随机但听起来相似的词，这是一个很糟糕的例子。 - Martin

公正地说，“FORGING” 已经暗示了它的含义。 - John Frazer