1

I saw this comment:

[applications] with the most data-related problems were those using natural keys.

Source: Surrogate vs. natural/business keys

I want more supporting evidence of this, as the comment left much to imagination.

It suggests that practice of using natural keys creates data-related problems, but does not specify what goes wrong... does data get corrupt? out of sync? becomes erroneous, lost, damaged? hard to query?

What are the data problems that happen when database is designed with natural keys opposed to using surrogate keys? How can those type of problems can be prevented when using surrogate keys?

Community
  • 1
  • 1
Dennis
  • 7,907
  • 11
  • 65
  • 115

1 Answers1

1

The main issue with natural keys is how it affects related tables. If you change the value of the key, then you must correct every row in every table that references the original value.

For example, suppose you have a Zip Code or Postal Code table. Quite often, these are designed where the Postal Code also serves as the natural key. Now suppose the Post Office changes a particular Postal Code (92680 becomes 92780). When you change the key in the Postal Code table, you must then go to every table that references that Postal Code, and update it there as well. So every row in the customer address, vendor address, etc... that has 92680 in the Postal Code has to be changed to 92780.

Obviously, if the related tables are not remediated, you can start to have big problems. Let's say you charge insurance premiums based upon postal code. Imagine the issues you could have if these are not fixed in the premium table.

Using Surrogate keys eliminates this problem altogether. You simply change the postal code in the Postal Code table. The surrogate key is not changed. And since the related tables store the surrogate key, you don't have to change anything else.

TK Bruin
  • 472
  • 4
  • 15
  • If your DBMS supports cascading updates then foreign key references will get updated automatically. And if your natural key isn't referenced anywhere then no updates will be necessary. Same goes for surrogate keys. – nvogel Apr 26 '17 at 20:23
  • You bring up a good point. It's important to point out that just because your DBMS supports cascading updates, it doesn't mean everything is automatically taken care of. I do a lot of work in SQL Server, which supports cascading updates. and I can't remember the last time I saw cascading updates set up with all the other referential integrity required so that things become "automatic". But, yes, if you set up everything correctly, the system should be able to handle your updates. And this is the preferred approach if you use natural keys. – TK Bruin Apr 26 '17 at 20:51