You really shouldn’t be updating primary key values though.

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 7 months ago | parent [-]

> If one wants to use the natural key

Yeah, that's why you shouldn't use natural keys as primary ones.

▲

magicalhippo 7 months ago | parent [-]

Learned that lesson a bit too late for that.

But yea, these days I never use natural keys.

▲

marcosdumay 7 months ago | parent [-]

I really hate that the educational literature says it's an option. Yet everybody knows it's something you should never do, no exceptions.

▲

mycall 7 months ago | parent [-]

Maintaining old databases is one reason to use natural keys. There are a lot of them in the wild.

	▲	marcosdumay 7 months ago \| parent [-]
		It's not a valid reason for adding more of them. It's only a reason to be stuck inheriting some.