2

Disclaimer: This is not the same question as the other topics.

One of the cons we face when using Natural Keys, is that, if the business logic changes and we need to change one key, we need to propagate this change throughout all linked tables.

However, using a ON UPDATE CASCADE declaration we can make the DBMS to propagate the change for us when we change a key value.

What I don't understand is: What are the cons of this approach? Is there any situation where using ON UPDATE CASCADE can be risky to the database?

Because if not, then if in a situation, the single reason to use Surrogate keys is the fact that it is easier to change business Natural keys, then ON UPDATE CASCADE should be used instead of converting all tables to Surrogate keys.

Community
  • 1
  • 1
PedroD
  • 5,670
  • 12
  • 46
  • 84

1 Answers1

1

Advantages of surrogates..

  1. If you're using REST then it requires the concept of a resource identifier. Using part of the resource as the resource identifier can be cumbersome. A surrogate key isn't really a surrogate in this case since it becomes a real-world resource identifier, but that is one reason to use a surrogate key pattern.

  2. A lot of ORM tools are just easier to manage with a surrogate. For better or worse, OOP requires the concept of identity remain separate from value. In a lot of ways this is similar to point 1.

  3. If you change the key attributes you can just alter one table. On the other hand, surrogates cannot turn a naturally composite key into a simple one if you wish to maintain referential integrity. For example, Document (UserId, FolderId) references Folder (UserId, Id) keeps a user from putting his documents in someone else's folder where simply Document (FolderId) references Folder (Id) does not. But using a surrogate at least limits mandatory sweeping changes to structural decisions, rather than just someone choosing among several candidate keys.

I won't list the advantages of natural keys because I think they're obvious. Less is more. Surrogates are the concept in need of justification.

Samuel Danielson
  • 5,231
  • 3
  • 35
  • 37