i hope you can help me with a general question about referential integrity. If i am deleting data in a table that is referenced to another table as a foreign key. How can i delete the data without violating the referential integrity? Do i have to erase the foreign key or do i replace the reference with some sort of placeholder.

Kindest David

Research in web but could not find any useful data

CodePudding user response：

Typically you'd remove the dependent rows first and then delete the parent row.

Say you have a database where users post messages and others comment on those posts. So you have a posts table and a comments table. Now you want to delete all posts of 2018. This means you must first delete the comments to those posts and then the posts:

delete from comments where post_id in
  (select post_id from posts where extract(year from post_date) = 2018);

delete from posts where extract(year from post_date) = 2018;

commit;

In many DBMS you can create the foreign keys with an ON DELETE CASCADE clause. That means you'd just delete the posts and the DBMS would go down the whole tree to delete from bottom up. But beware: with many dependent tables and maybe many levels (parent -> children -> grandchildren -> ...) this can be a lot of work. You seem to delete one row, but behind the scenes it may be billions of rows from many tables, and the statement may even time out. So use this clause in small databases, if you like, but be careful with bigger databases, where you may want to delete manually as shown above.