> Though, personally, I would never use ON UPDATE CASCADE, as it seems kind of funky.

Why?

Personally, I like to be explicit and in control. In the application layer, I may be far away (at least mentally speaking) from the constraints in the database, and if I update/delete something, I don't want it to "magically" cascade through the database. For those reasons, I always prefer RESTRICT, both for ON DELETE and ON UPDATE. This forces me to clean up before I make the actual change I'm interested in, and anyone reading the code later, can uncover that behaviour quickly.

That being said, I can see the arguments for ON DELETE CASCADE, particularly in a codebase, where there is a lot of functionality in the database itself (in the formed of stored procedures, and what have you), since you are always mentally closer to the action. But ON UPDATE CASCADE feels weird, because why are you updating the primary key (which is usually what foreign keys references) of your rows? That feels like something that needs a good explanation.

Though, I do recognise, you need to jump through a lot of hoops to modify your primary key values with ON UPDATE RESTRICT, because you basically need to cover all your bases in a large convoluted common table expression (depending on the number of foreign keys, of course), when an ON UPDATE CASCADE would do that for you. But I'd also rather be blocked from updating primary row values entirely, since it feels like the wrong thing to do. (Yes, I know that foreign keys doesn't have to reference other primary keys, and there may be niche cases for this, but personally, I'd just avoid it altogether.)

Thanks for the explanation.

The first case I was thinking about was a merger of two accounts. Second case would be a db-wide update of one or more keys for some reason. That’s why I tend to leave it ON UPDATE CASCADE.

Although both of these cases are more diffuse muscle memory than rational foresight.

> if I update/delete something, I don't want it to "magically" cascade through the database.

This is the big reason I don't like triggers either. I would use them in only one case: if the database didn't support an auto-incrementing "identity" type, I might use a trigger to simulate that. But just as often I would prefer a stored procedure that got the new ID from a sequence, and then did the insert, especially if there were other things I needed a stored procedure to deal with.

> Though, personally, I would never use ON UPDATE CASCADE, as it seems kind of funky.

As a relative noob in the world of database administration, I'm glad to hear that someone else feels this way.

It does if your key is an auto increment or random unique identifier. But if you had a key that is also data, say a "Genre" colum, it starts to make sense that you'd want to cascade updates

> That's not entirely true. ON UPDATE CASCADE is a thing for foreign keys, at least in PostgreSQL (which this article is talking about), meaning that the foreign row referencing the row gets updated.

For this to work with soft-deletes, wouldn't you have to include the soft-delete column in a compound foreign key? That sounds funky indeed.

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.

> If one wants to use the natural key

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

Learned that lesson a bit too late for that.

But yea, these days I never use natural keys.

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

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

It's not a valid reason for adding more of them. It's only a reason to be stuck inheriting some.