2 ms·
While I overall agree, there have been cases where I have found it handy. In one case, we had a table of invoices and a table of goods items, and each goods it
by magicalhippo 2y ago
While I overall agree, there have been cases where I have found it handy.
In one case, we had a table of invoices and a table of goods items, and each goods item should point to an invoice.
If one wants to use the natural key, invoice number, and have a foreign key to ensure the goods items can't point to an invoice that doesn't exist, then ON UPDATE CASCADE was needed in case the user had to change the invoice number (due to mistyping it or similar).
Of course, if one does not use a natural key then this isn't such an issue.
Another case was where we've had to merge databases after two companies merged. If they had overlapping primary key ranges, then the easiest was to switch to ON UPDATE CASCADE and then renumber the existing rows before inserting the other rows.
We'd change back after the merge though.
- marcosdumay 2y ago> If one wants to use the natural key Yeah, that's why you shouldn't use natural keys as primary ones.
- magicalhippo 2y agoLearned that lesson a bit too late for that. But yea, these days I never use natural keys.
- marcosdumay 2y agoI really hate that the educational literature says it's an option. Yet everybody knows it's something you should never do, no exceptions.
- mycall 2y agoMaintaining old databases is one reason to use natural keys. There are a lot of them in the wild.
- marcosdumay 2y agoIt's not a valid reason for adding more of them. It's only a reason to be stuck inheriting some.