CODEWITHSUNDEEP
sqldatabase
erogol
erogol

Reputation: 13614

Deleting rows of a table that has a foreign key to other table

I have folders table and docs table and folders_docs table that maps each doc to one folder. So I try to delete some row from folder table but of course it gives an error like:" Cannot delete or update a parent row: a foreign key constraint fails "

How can cope with that problem?

Thanks in advance...

Upvotes: 0

Views: 213

Answers (3)

Andrea Colleoni
Andrea Colleoni

Reputation: 6021

You can improve the use of REFERENTIAL INTEGRITY in which you define constraints (foreign keys) and propagate the folders deletion to folders_docs and then to docs, but in your case you must play attention at your design: you have a N:M relation between folders and docs, so with a folder deletion you should stop the propagation at the folders_docs table, otherwise you won't be able to find docs in other folders if referenced.

What SQL product are you using?

In many SQL products (SQL Server for example) you can add the ON DELETE CASCADE clause to the ADD CONSTRAINT part of the ALTER TABLE DDL to accomplish this.

Upvotes: 2

gdeignacio
gdeignacio

Reputation: 11

You can add a column folder in the docs table and don't use the folders_docs

You save the management of one table if relationship is 1:n

Upvotes: 0

Alex
Alex

Reputation: 6149

got to the database diagram --> relationship properties --> Insert And Update Specification and put both Delete Rule and Update Rule as Cascade, this will let you delete the related rows in another table automatically.

Upvotes: 0

Related Questions