Postgres foreign key on update cascade
Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters. Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results.
They are not the utmost best solution s. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own. Originally published at joshuaotwell. Growing through Technical Writing and Blogging. Thanks for the article! Laurenz, Thank you so much for your inputs. Yes, you are right. We see few customers are facing this kind of issue, where they are actually updating the non-key columns by acquiring lock on key column.
Save my name, email, and website in this browser for the next time I comment. Please enable JavaScript in your browser to complete this form. Comment or Message. It can be used to insert a single record or multiple records into a table into PostgreSQL. We can see the results for the employee table below:. The result for the process table should look like the following:.
The cascade ensures that related records from the employee table will also be removed. Why did these records get deleted? It happened because the distribution section was removed. As such, they reflect the expected behaviour of entities upon which they act.
Others have provided examples where one or another behaviour would be appropriate from the business rules' point of view. On the one hand it prevents irreversible data loss, while on the other allowing you to defer constraint validation until the end of the transaction.
Like the object-oriented analogy, it isn't a security feature to defend from external attackers so much as a safety feature to mitigate one's own mistakes. It gives a user tools to inform them about unexpected results of an action. For example, consider a university database with courses and students enrolled on them. When changing the ID of an existing course, it's probably appropriate to cascade that change - it's the same course, and any students enrolled on it will still be enrolled.
If, however, you were working with a new course that had been added to the database ready for the next year's intake, but which didn't have any students enrolled on it yet, it would be more appropriate to have the update fail if it would change a foreign key dependency - you know there shouldn't be any entries pointing to this new course as a foreign key, so if the update does fail, it means something's wrong.
Whether it's an erroneous premature enrolment or you're trying to edit the wrong course by mistake, it's worth investigating further to understand exactly what the problem is before forcing the change through. In this case, cascading the changes would likely propagate the error and could make debugging and recovery harder.
If everything's working properly, this safety mechanism is never necessary; but things sometimes don't work properly, and in some of those cases it's best to fail and be warned before continuing. The question on when to cascade delete, when to restrict delete, is an interesting one, and I haven't figured out a definitive answer yet. Informally I'm thinking in terms of how important is the information, and can it be recreated from an external source? If on the other hand, the information is valuable and can not easily be recreated, I tend to restrict it.
In the latter case, an explicit delete has to be made. I have used on cascade delete in case where the parent and child records belong together such as. Sign up to join this community. The best answers are voted up and rise to the top. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Learn more. Why does this mechanism exist at all?
Ask Question. Asked 2 years, 1 month ago. Active 1 month ago. Viewed 6k times. Improve this question.