0

I have following two tables, whose schema looks like given below :-

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

Following two tables are referring each other:-

When I try to delete any one table, it gives me following error:-

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.

But when I delete with cascade option, table is deleted, but it doesn't delete the other table or its foreign key column in that table, it deletes only the foreign key constraint.

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)

Is this the expected behavious ? In case of on delete cascade, deletion of row in the parent, deletes in the child table, but the same things is not happening with drop table ?

Am I missing something ? Is this behaviour specific to postgres ?

Thanks in advance.

Mangu Singh Rajpurohit
  • 10,806
  • 4
  • 68
  • 97

2 Answers2

4

Because this is how DROP ... CASCADE was designed.

Quote from the manual

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

(Emphasis mine)

This is not specific to Postgres. Oracle and DB2 work the same when dropping tables.

1

(...) to drop a table that is referenced by a view or a foreign-key constraint of another table, CASCADE must be specified. (CASCADE will remove a dependent view entirely, but in the foreign-key case it will only remove the foreign-key constraint, not the other table entirely.)

https://www.postgresql.org/docs/current/static/sql-droptable.html (emphasis mine)

Jiri Tousek
  • 12,211
  • 5
  • 29
  • 43