postgresql 关于删除级联

postgresql on delete cascade

提问by Antony

I have two tables:

我有两个表：

TableY:
    id, name, description

TableX:
    id, name, y_id

I have added a foreign key in TableX(y_id REFERENCING tableY id ON DELETE CASCADE). When I delete from TableX, the TableYidremains (while it theoritically should be deleted). I guess I have misunderstood how the ON DELETE CASCADEoption works. Can anyone tell what I am doing wrong?

我在TableX( y_id REFERENCING tableY id ON DELETE CASCADE) 中添加了一个外键。当我从 中删除时TableX，TableYid剩余部分（理论上应该删除）。我想我误解了该ON DELETE CASCADE选项的工作原理。谁能告诉我我做错了什么？

I saw this ON DELETE CASCADEas well but did not make much sense to me.

我也看到了这个ON DELETE CASCADE但对我来说没有多大意义。

采纳答案by Viktor Stolbin

I guess you got misunderstanding. Try to delete row from TableYand corresponding rows from TableXwill be cascade deleted. This option is indispensable when you have secondary related tables and would like to clean them all by deleting parent row from primary table without getting constraints violated or rubbish left.

估计你误会了 尝试删除行TableY，相应的行将TableX被级联删除。当您有次要相关表并希望通过从主表中删除父行来清理它们而不会违反约束或留下垃圾时，此选项是必不可少的。

回答by vitfo

Short answer

简答

ON DELETE CASCADE specifies constraint option. In your case (suppose you have table_x and table_y that depends on table_x) when you delete row in table_x and this row is referenced from table_y, row from table_x would be deleted and all rows that referenced this row in table_y would be deleted as well.

ON DELETE CASCADE 指定约束选项。在您的情况下（假设您有 table_x 和 table_y 依赖于 table_x），当您删除 table_x 中的行并且该行从 table_y 引用时，table_x 中的行将被删除，并且 table_y 中引用该行的所有行也将被删除。

When you want remove table and on this table depend one or more other tables use DROP TABLE with CASCADE key word.

当您想要删除表并在此表上依赖一个或多个其他表时，请使用带有 CASCADE 关键字的 DROP TABLE。

More about DROP, DELETE and ON DELETE CASCADE constraint option

有关 DROP、DELETE 和 ON DELETE CASCADE 约束选项的更多信息

Drop vs delete

删除与删除

When you want to remove table from database use word drop. When you use word delete it means that you want to remove/delete content from the table (but want the table to stay = to continue exist in database).

当您想从数据库中删除表时，请使用 drop。当您使用单词 delete 时，这意味着您要从表中删除/删除内容（但希望表保持=继续存在于数据库中）。

Deleting row that is referenced from other table

删除从其他表引用的行

When you have row in the table (e.g. table_x and row with id 1) and this row is referenced from other table (in other table there is foreign key linked with this entry) you cannot delete the row as follow. You would get an error.

当您在表中有行（例如 table_x 和 ID 为 1 的行）并且该行是从其他表中引用的（在其他表中有与此条目链接的外键）时，您不能按如下方式删除该行。你会得到一个错误。

DELETE FROM table_x WHERE id = 1;

ERROR:  update or delete on table "table_x" violates foreign key constraint "constraint_name" on table "table_y"
DETAIL:  Key (id)=(1) is still referenced from table "table_y".

The reason is described in the detail part of the error. The entry/entries in other table/tables reference to this entry. As example you can imagine table account and table account_activity. When you delete entry from table account (you delete one account) you should delete all entries from table account_activity that reference to this specific entry in table account. If not you would end up with account activities that do not refer to any account.

原因在错误的详细部分描述。其他表中的条目引用此条目。例如，您可以想象表 account 和表 account_activity。当您从表帐户中删除条目（您删除一个帐户）时，您应该从表 account_activity 中删除所有引用表帐户中此特定条目的条目。否则，您最终会得到不涉及任何帐户的帐户活动。

There are two possibilities depending on what you want to achieve:

根据您要实现的目标，有两种可能性：

1. You want to delete content of whole table (table_x) and content of all tables that have reference (foreign key) to this table.
2. You want to delete one or more entries (rows) specified in WHERE clause from one table and also delete all rows that reference to this entry/entries (like the example with account and account_activity mentioned above).

1. 您要删除整个表 (table_x) 的内容和所有引用（外键）该表的表的内容。
2. 您想从一个表中删除 WHERE 子句中指定的一个或多个条目（行），并删除引用此条目的所有行（如上面提到的带有 account 和 account_activity 的示例）。

1) Use TRUNCATE with CASCADE key word

1) 使用 TRUNCATE 和 CASCADE 关键字

TRUNCATE table_x CASCADE;

2) Change constraint you have in table_y on the column to have option ON DELETE CASCADE.

2) 将 table_y 中列上的约束更改为具有 ON DELETE CASCADE 选项。

ALTER TABLE table_y   
    DROP CONSTRAINT constraint_name,   
    ADD CONSTRAINT constraint_name FOREIGN KEY (column_in_table_y)
          REFERENCES table_x (referenced_column_in_table_x) ON DELETE CASCADE;

Dropping table on which other tables depend

删除其他表所依赖的表

When you have table (e.g. table_x) and other table/tables depend on it you cannot drop this table. Depending means that the other tables reference (have foreign key) to this table (table_x). When you try to drop such table you get error.

当您有表（例如 table_x）并且其他表/表依赖于它时，您不能删除此表。依赖意味着其他表引用（有外键）到这个表（table_x）。当您尝试删除此类表时，您会收到错误消息。

DROP TABLE table_x;

ERROR:  cannot drop table table_x because other objects depend on it
DETAIL:  constraint id_x_fk on table table_y depends on table table_x
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

The error gives hint. When you want drop such a table you have to use drop with a cascade key word. The table will be dropped and also all constraints that reference to this table will be dropped.

错误给出了提示。当你想删除这样一个表时，你必须使用带有级联关键字的 drop。该表将被删除，并且所有引用该表的约束也将被删除。

DROP TABLE table_x CASCADE;

Examples

例子

Creates table_x and table_y.

创建 table_x 和 table_y。

CREATE TABLE table_x
(
  id integer NOT NULL,
  text character varying(255) NOT NULL,
  CONSTRAINT table_x_pk PRIMARY KEY (id)
);

CREATE TABLE table_y
(
  id integer NOT NULL,
  text character varying(255) NOT NULL,
  id_x integer NOT NULL,
  CONSTRAINT table_y_pk PRIMARY KEY (id)
);

Creates constraint with name id_x_fk on column id_x in table_y.

在 table_y 中的列 id_x 上创建名称为 id_x_fk 的约束。

ALTER TABLE table_y
  ADD CONSTRAINT id_x_fk FOREIGN KEY (id_x)
      REFERENCES table_x (id);

Inserts test data to table_x and table_y.

将测试数据插入 table_x 和 table_y。

INSERT INTO table_x VALUES
    (1, 'super x'),
    (2, 'great x');

INSERT INTO table_y VALUES
    (1, 'y one', 2),
    (2, 'y two', 1),
    (3, 'y three', 1);

Deleting from table_x (error).

从 table_x 中删除（错误）。

DELETE FROM table_x WHERE id = 1;

Dropping and adding constraint with the same name.

删除和添加具有相同名称的约束。

ALTER TABLE table_y
  DROP CONSTRAINT id_x_fk,
  ADD CONSTRAINT id_x_fk FOREIGN KEY (id_x)
      REFERENCES table_x (id) ON DELETE CASCADE;

Deleting from table_x and also from table_y (correct).

从 table_x 和 table_y 中删除（正确）。

DELETE FROM table_x WHERE id = 1;

Deleting all content from table_x and table_y.

删除 table_x 和 table_y 中的所有内容。

TRUNCATE table_x CASCADE;

Dropping (removing) table_x and dropping id_x_fk constraint in table_y.

删除（移除）table_x 并删除 table_y 中的 id_x_fk 约束。

DROP TABLE table_x CASCADE;

Notice

注意

If you have row in table_x (e.g. with id = 3) and this entry (this id) is not referenced from table_y you can delete the row even though the constraint does not have ON DELETE CASCADE option (because there is no constraint violation).

如果您在 table_x 中有行（例如，id = 3）并且此条目（此 id）未从 table_y 引用，您可以删除该行，即使约束没有 ON DELETE CASCADE 选项（因为没有违反约束）。

The code was tested on "PostgreSQL 9.2.4, compiled by Visual C++ build 1600, 64-bit".

代码在“PostgreSQL 9.2.4，由 Visual C++ build 1600，64 位编译”上进行了测试。

Sources:

资料来源：

http://www.postgresql.org/docs/current/static/sql-droptable.html

http://www.postgresql.org/docs/current/static/sql-droptable.html

http://www.postgresql.org/docs/current/static/sql-delete.html

http://www.postgresql.org/docs/current/static/sql-delete.html

http://www.postgresql.org/docs/current/static/ddl-constraints.html

http://www.postgresql.org/docs/current/static/ddl-constraints.html

http://www.postgresql.org/docs/current/static/sql-altertable.html

http://www.postgresql.org/docs/current/static/sql-altertable.html

PostgreSQL delete all content

PostgreSQL 删除所有内容