Menu
How can we use the cascade in PostgreSQL while deleting the one record from the parent table that is being referred in other child tables. Currently it is giving the syntax error.
ERROR: syntax error at or near "cascade"
LINE 1: DELETE FROM fs_item where itemid = 700001803 cascade;
542
A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is called a cascade delete in SQL Server.
First, specify the table from which you want to delete data in the DELETE FROM clause. Second, specify which rows to delete by using the condition in the WHERE clause. The WHERE clause is optional. However, if you omit it, the DELETE statement will delete all rows in the table.
Cascading deletes are needed when a dependent/child entity can no longer be associated with its current principal/parent. This can happen because the principal/parent is deleted, or it can happen when the principal/parent still exists but the dependent/child is no longer associated with it.
How can we use the cascade in PostgreSQL while deleting the one record from the parent table that is being referred in other child tables. Currently it is giving the syntax error. You have to add ON DELETE CASCADE constraint in following way: There is no CASCADE for delete statements.
As shown above, the DELETE CASCADE can be used in PostgreSQL to delete all the child records whenever the referenced parent record is deleted automatically which helps in maintaining integrity. This is a guide to Postgres Delete Cascade.
However, MySQL provides a more effective way called ON DELETE CASCADE referential action for a foreign key that allows you to delete data from child tables automatically when you delete the data from the parent table. MySQL ON DELETE CASCADE example. Let’s take a look at an example of using MySQL ON DELETE CASCADE.
Before running a delete query, it is important to ensure that the table does not leave any incomplete relationships between tables. In PostgreSQL, we can use the CASCADE DELETE feature to accomplish this. This feature allows a delete operation to remove the specified records and any foreign keys that reference them.
You have to add ON DELETE CASCADE constraint in following way:
ALTER TABLE table1 ADD CONSTRAINT "tbl1_tbl2_fkey" FOREIGN KEY(reference_key) REFERENCES table2 ON DELETE CASCADE;
Then, you can simply execute the DELETE query
DELETE FROM fs_item where itemid = 700001803
There is no CASCADE for delete statements. You set the foreign key to CASCADE deletes and then it happens for you automatically.
43
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
