postgresql - 为什么 drop table cascade 不删除 postgresql 中的子表？

Question

我有以下两个表，其架构如下所示:-

postgres=# \d products1;
                               Table "public.products1"
       Column       |  Type   |                       Modifiers
--------------------+---------+--------------------------------------------------------
 id                 | integer | not null default nextval('products1_id_seq'::regclass)
 name               | text    | not null
 default_picture_id | integer |
Indexes:
    "products1_pkey" PRIMARY KEY, btree (id)
    "unique_id_default_pic_id" UNIQUE CONSTRAINT, btree (id, default_picture_id)
Foreign-key constraints:
    "fk_products_1" FOREIGN KEY (id, default_picture_id) REFERENCES product_pictures1(product_id, id) ON UPDATE RESTRICT ON DELETE RESTRICT
Referenced by:
    TABLE "product_pictures1" CONSTRAINT "fk_id_product_id" FOREIGN KEY (id, product_id) REFERENCES products1(default_picture_id, id)


postgres=# \d product_pictures1;
                           Table "public.product_pictures1"
   Column   |  Type   |                           Modifiers
------------+---------+----------------------------------------------------------------
 id         | integer | not null default nextval('product_pictures1_id_seq'::regclass)
 img_path   | text    | not null
 product_id | integer |
Indexes:
    "product_pictures1_pkey" PRIMARY KEY, btree (id)
    "unique_id_productid" UNIQUE CONSTRAINT, btree (id, product_id)
Foreign-key constraints:
    "fk_id_product_id" FOREIGN KEY (id, product_id) REFERENCES products1(default_picture_id, id)
Referenced by:
    TABLE "products1" CONSTRAINT "fk_products_1" FOREIGN KEY (id, default_picture_id) REFERENCES product_pictures1(product_id, id) ON UPDATE RESTRICT ON DELETE RESTRICT

以下两个表相互引用:-

当我尝试删除任何一张表时，它会出现以下错误:-

postgres=# drop table products1;
ERROR:  cannot drop table products1 because other objects depend on it
DETAIL:  constraint fk_id_product_id on table product_pictures1 depends on table products1
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

但是当我用级联选项删除时，表被删除了，但它并没有删除另一个表或其在该表中的外键列，它只删除了外键约束。

postgres=# drop table products1 cascade;
NOTICE:  drop cascades to constraint fk_id_product_id on table product_pictures1
DROP TABLE

postgres=# \d product_pictures1;
                           Table "public.product_pictures1"
   Column   |  Type   |                           Modifiers
------------+---------+----------------------------------------------------------------
 id         | integer | not null default nextval('product_pictures1_id_seq'::regclass)
 img_path   | text    | not null
 product_id | integer |
Indexes:
    "product_pictures1_pkey" PRIMARY KEY, btree (id)
    "unique_id_productid" UNIQUE CONSTRAINT, btree (id, product_id)

这是预期的行为吗？在 删除级联 的情况下，删除父表中的行，删除子表中的行，但删除表不会发生同样的事情吗？

我错过了什么吗？这种行为是 postgres 特有的吗？

提前致谢。

Answer 1

因为这就是 DROP ... CASCADE 的设计方式。

Quote from the manual

but in the foreign-key case it will only remove the foreign-key constraint, not the other table entirely

^{(强调我的)}

这不是 Postgres 特有的。删除表时，Oracle 和 DB2 的工作方式相同。