![]() ![]() This example will present the stepwise instructions to use the DELETE CASCADE option in Postgres:įirstly, let’s create a “cutomer_details” table with two columns: “cust_id” and “cust_name”: CREATE TABLE customer_details ( This tells Postgres to automatically delete any rows in the referenced table that are related to the row being deleted in the referencing table.Įxample: How Does the DELETE CASCADE Work in Postgres? To use a delete cascade in Postgres, specify the " ON DELETE CASCADE" option while creating/defining a foreign key constraint. This is where Postgres’ DELETE CASCADE feature is extremely useful! Then to keep the database clean and organized, he might also want to delete all of the orders associated with that customer from the "order_details" table. Suppose a user wants to delete a customer from the "customer_details" table. The "order_details" table is linked with the "customer_details" table through a foreign key constraint. So, let’s start!įor instance, consider a database with a "customer_details" and an "order_details" table. This Postgres blog will present a step-by-step guide on how to use the DELETE CASCADE option in Postgres. When a DELETE CASCADE feature is enabled, deleting a record from the referenced/parent table will also delete the referencing records from the child table. Still, even if it is an inconsistency, it is how the behaviour is documented:Īutomatically 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.In PostgreSQL, a DELETE CASCADE is a powerful feature that is useful when you have multiple tables linked with each other via foreign key constraints. 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 well 1.ġMy 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. CASCADE drops the columns that directly depend on the domain you are dropping. ![]() ![]() CASCADE drops the views that directly depend on the table you are dropping.ĭROP DOMAIN. So, the behaviour you are observing should be expected as it is consistent with the other cases you have mentioned:ĭROP TABLE. 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. ![]() This is documented on the same manual page that the other answer is referring to:Īutomatically drop objects that depend on the table (such as views). The idea behind DROP CASCADE is to automatically remove the dependent objects. 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? DROPing a table with a view attached to it will delete the view.DROPing a domian with CASCADE removes every column in any table that uses that domain.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? CASCADE on the referenced table does in fact remove the rows in the other tables The tables are still there and even though I specified CASCADE, nothing happened to the rows of those tables. 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. I am still relatively new to postgres 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. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |