SQL PostgreSQL: FOREIGN KEY/ON DELETE CASCADE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14141266/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL: FOREIGN KEY/ON DELETE CASCADE
提问by juk
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从猫中删除，我尝试了上面的方法但不起作用，在我清理表格书籍后，表格类别仍然填充，有什么问题？
回答by Mari
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. :)
因此，在您的情况下，当用户从 CATs 表中删除条目时，将从 books 表中删除行。:)
Hope this helps you :)
希望这对你有帮助:)
回答by Martin
Excerptfrom PostgreSQL documentation:
摘自PostgreSQL文档：
Restricting and cascading deletes are the two most common options. [...]
CASCADEspecifies 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 FROMclans will CASCADEto hobbits by REFERENCES.
DELETE FROM氏族会CASCADE以霍比特人为由REFERENCES。
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!
如果你真的需要相反的（由数据库检查），你将不得不写一个触发器！
回答by Gordon Vidaver
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 信息中的某个地方？很难说。
回答by Ram Pukar
PostgreSQL Forging Key DELETE, UPDATE CASCADE
PostgreSQL Forging Key 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
);
