PostgreSQL：外键/删除级联 [英] PostgreSQL: FOREIGN KEY/ON DELETE CASCADE

问题描述

我有两个像这样的表：

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从猫中删除了，我尝试了上述方法，但无法正常工作，在我清理了表格书之后，仍然填充了表格类别，这是怎么回事？

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?

推荐答案

A使用级联删除的外键意味着，如果删除父表中的记录，则子表中的相应记录将被自动删除。这就是所谓的级联删除。

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.

您说的是相反的意思，这不是当您从子表中删除时会从父表中删除记录。 / p>

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选项用于指定在父表中删除相应行时是否要在子表中删除行。如果未指定级联删除，则数据库服务器的默认行为将阻止您删除表中的数据（如果其他表引用了该数据）。

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.

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

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.

因此，当您从CAT表中删除条目时，行将被删除从书表。 ：）

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

希望这对您有帮助：）

这篇关于PostgreSQL：外键/删除级联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！