4

I am still relatively new to psql and I was just finishing an assignment when I decided to try something which was to drop a table which other tables depend on due to a foreign key relationship. My expectation was that any table that had a foreign key reference to the table I am dropping will have (at least) all their rows deleted and the table dropped as well. This was not the case. The tables are still there and even though I specified CASCADE, nothing happened to the rows of those tables.

However using TRUNCATE ... CASCADE on the referenced table does in fact remove the rows in the other tables

EDIT: Ok, I understand it does this because this is the way it has been specified by the documentation, but my question now is, when is this ever expected behaviour?

  • DROPing a domian with CASCADE removes every column in any table that uses that domain.
  • DROPing a table with a view attached to it will delete the view.

Why does it make sense that dropping a table which is referenced by other tables does not delete the tables that reference this one as well? Convienience?

  • "but my question now is, when is this ever expected behaviour" - from my point of view: always – a_horse_with_no_name May 30 '16 at 20:07
2

The idea behind DROP CASCADE is to automatically remove the dependent objects. This is documented on the same manual page that the other answer is referring to:

CASCADE

Automatically drop objects that depend on the table (such as views).

(Emphasis mine.)

When you are dropping a table that is referenced by another table, the object that immediately depends on the table being dropped is not the other table itself but the foreign key constraint defined on it.

So, the behaviour you are observing should be expected as it is consistent with the other cases you have mentioned:

  • DROP TABLE ... CASCADE drops the views that directly depend on the table you are dropping.

  • DROP DOMAIN ... CASCADE drops the columns that directly depend on the domain you are dropping.

  • TRUNCATE ... CASCADE is also consistent with the explanation above because it removes rows, and the objects dependent on rows can only be other rows, including other tables' rows – that is why the referencing tables are truncated as well1.


1My only issue with the other tables' truncation is that their foreign keys may be defined on nullable columns and yet the tables are still truncated completely even if some of their rows do not reference the table(s) specified in the TRUNCATE statement. Still, even if it is an inconsistency, it is how the behaviour is documented:

CASCADE

Automatically truncate all tables that have foreign-key references to any of the named tables, or to any tables added to the group due to CASCADE.

3

This is how it's documented:

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.

Emphasis mine

  • Interesting. It doesn't make any sense though. Why does truncate do what I want, but DROPing a table (as in no longer exists) just removes the foreign key constraint? Is there more to that than what you just posted? – smac89 Oct 7 '15 at 7:49
  • 1
    @Smac89: I do think it makes sense - and this is how Oracle and DB2 (and probably other DBMS) work as well. – a_horse_with_no_name Oct 7 '15 at 7:51
  • 1
    @Smac89 it totally makes sense, but apparently not what you want. It seems easier to change your thinking than the DROP behaviour ;) – dezso Oct 7 '15 at 7:56
  • Ok now that I think of it, there is a possibility it makes sense. So the rows of a table b with a foreign key cannot exist without the other table a they reference, but once a is deleted this does not invalidate the b because the entries are still valid had a still been in existence. Now the question is, can you still insert into b? – smac89 Oct 7 '15 at 7:59
  • @Smac89: sure you can (as long as the new rows comply with all the constraints defined on table b) – a_horse_with_no_name Oct 7 '15 at 8:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.