PostgreSQL：FOREIGN KEY / ON DELETE CASCADE

I have two tables like here:

我有两张桌子在这里：

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)
);

So the logic is simple, after user removes all book under category x, x gets removed from cats, i tried method above but doesn't work, after i clean table book, table category still populated, what's wrong?

所以逻辑很简单，用户删除了类别x下的所有书籍后，x从猫中删除，我尝试了上面的方法但是不起作用，在我清理了表格书之后，表格类别仍然填充，出了什么问题？

4 个解决方案

#1

A foreign key with a cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is called a cascade delete.

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

You are saying in a opposite way, this is not that when you delete from child table then records will be deleted from parent table.

你是以相反的方式说，这不是当你从子表中删除时，那么记录将从父表中删除。

UPDATE 1:

ON DELETE CASCADE option is to specify whether you want rows deleted in a child table when corresponding rows are deleted in the parent table. If you do not specify cascading deletes, the default behaviour of the database server prevents you from deleting data in a table if other tables reference it.

ON DELETE CASCADE选项用于指定在父表中删除相应行时是否要在子表中删除行。如果未指定级联删除，则数据库服务器的默认行为会阻止您删除表中的数据（如果其他表引用它）。

If you specify this option, later when you delete a row in the parent table, the database server also deletes any rows associated with that row (foreign keys) in a child table. The principal advantage to the cascading-deletes feature is that it allows you to reduce the quantity of SQL statements you need to perform delete actions.

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

So it's all about what will happen when you delete rows from Parent table not from child table.

所以当你从父表中删除行而不是从子表中删除行时会发生什么。

So in your case when user removes entries from CATs table then rows will be deleted from books table. :)

因此，在用户从CAT表中删除条目的情况下，将从books表中删除行。 :)

Hope this helps you :)

希望这可以帮到你:)

#2

Exerpt from PostgreSQL documentation:

从PostgreSQL文档中删除：

Restricting and cascading deletes are the two most common options. [...] CASCADE specifies that when a referenced row is deleted, row(s) referencing it should be automatically deleted as well.

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

This means that if you delete a category – referenced by books – the referencing book will also be deleted by ON DELETE CASCADE.

这意味着如果删除书籍引用的类别，ON DELETE CASCADE也将删除引用书籍。

Example:

例：

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 will CASCADE to hobbits by REFERENCES.

DELETE FROM clans将通过REFERENCES将CASCADE转换为hobbits。

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)

If you really need the opposite (checked by the database), you will have to write a trigger!

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

#3

In my humble experience with postgres 9.6, cascade delete doesn't work in practice for tables that grow above a trivial size.

在我对postgres 9.6的简单经验中，级联删除在实践中不适用于增长超过平凡大小的表。

Even worse, while the delete cascade is going on, the tables involved are locked so those tables (and potentially your whole database) is unusable.
更糟糕的是，当删除级联正在进行时，所涉及的表被锁定，因此这些表（可能是您的整个数据库）无法使用。
Still worse, it's hard to get postgres to tell you what it's doing during the delete cascade. If it's taking a long time, which table or tables is making it slow? Perhaps it's somewhere in the pg_stats information? It's hard to tell.
更糟糕的是，很难让postgres告诉你它在删除级联期间做了什么。如果花了很长时间，哪一张桌子让它变慢？也许它在pg_stats信息中的某个地方？这很难说。

#4

-5

PostgreSQL Forging Key DELETE, UPDATE CASCADE

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
);

#1