The other option is to declare our foreign keys as ON DELETE CASCADE. The first option is to execute an explicit DELETE statement for each table that contains referencing rows and then finish by deleting the referenced row (this order is important if we don’t want to violate any foreign key constraints). If we need to delete a row in one of these tables as well as all other rows that reference it, then we have two options. This Postgres blog has explained the usage of the DELETE CASCADE keyword via practical examples.Let’s say we have several tables in an SQL database referencing each other with foreign key constraints. By doing so, Postgres will automatically delete any rows in the referenced table that are related to the row being deleted in the referencing table. To use a delete cascade in Postgres, specify the " ON DELETE CASCADE" option while creating/defining a foreign key constraint. In PostgreSQL, a DELETE CASCADE allows us to delete the records associated with some other tables (via foreign key constraints). This is how the DELETE CASCADE feature works in Postgres. The output snippet proves that Postgres automatically deleted the targeted record from the child table. Now, let’s execute the “SELECT *” command one more time to see if the selected record has been deleted from the child table or not: The output snippet shows that the customer having id 1 has been deleted from the customer_details table. To verify the working of the DELETE CASCADE feature, let’s execute the “SELECT *” command as follows: The output shows that the DELETE command was executed successfully.
Since we set/enabled the DELETE CASCADE feature while creating a foreign key, so deleting a record from the “customer_details” table will also delete the corresponding record from the “order_details” table: DELETE FROM customer_details The output snippet shows the data of both tables. Now execute the “SELECT *” command one more time to fetch the data of the “order_details” table: Now, execute the INSERT INTO command one more time to insert the data into the “order_details” table: INSERT INTO order_details (order_id, customer_id, order_date)įive records have been inserted into the order_details table successfully.Įxecute the “SELECT *” query to check the data of the “customer_details” table: The specified data has been inserted into the “customer_details” table successfully. Let’s insert some records into the “customer_details” table via the “INSERT INTO” command: INSERT INTO customer_details (cust_id, cust_name) Now create one more table named “order_details” with two regular columns and a foreign key: CREATE TABLE order_details(Ĭustomer_id INTEGER REFERENCES customer_details (cust_id) ON DELETE CASCADE,Īn “order_details” table with a foreign key named “customer_id” having the DELETE CASCADE feature enabled has been created. 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? 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.
#POSTGRES ON UPDATE CASCADE HOW TO#
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. 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.
0 kommentar(er)
