Skip to main content
Share Your Experience: Take the 2024 Developer Survey
8 votes

Do natural keys provide higher or lower performance in SQL Server than surrogate integer keys?

A key is a logical feature of a database whereas performance is always determined by physical implementation in storage and by physical operations run against that implementation. It's therefore a ...
nvogel's user avatar
  • 3,767
6 votes
Accepted

What are the disadvantages of using surrogate keys?

There are many disadvantages to using surrogate keys, the most important ones IMHO are: Using natural keys significantly reduces the number of joins that your queries need to perform. With a ...
SQLRaptor's user avatar
  • 4,098
6 votes
Accepted

How to get the "best of both worlds" with natural and surrogate keys? And could DBMSs be better?

While ypercube makes a good and logical point for the specific example of Countries, I would otherwise avoid using string-based data types because of the potential unexpected implications that can ...
J.D.'s user avatar
  • 37.8k
5 votes
Accepted

What are the consequences, positive or negative, to having a surrogate primary key for a table which already has a guaranteed unique column?

I agree a lot with comments from @MDCCL: You should analyze each particular case —that is, every particular table within its whole context, that is, the entire database structure— to define if it ...
joanolo's user avatar
  • 13.5k
4 votes

Surrogate key vs Natural key

Use the surrogate key as the primary key for the moment. When natural keys become available, make them non-nullable unique constraints. By the YAGNI principle, you should only code for "real-life" ...
Vérace's user avatar
  • 30k
3 votes

Identity versus Composite values for Primary Keys in Many-To-Many Tables

A Many-to-Many mapping table must have a unique index on the foreign key columns. And should normally have an additional unique index on the foreign key columns in the reverse order. So while adding ...
David Browne - Microsoft's user avatar
3 votes

Does adding a surrogate key get rid of an identifying relationship between two entities?

For reasons of accuracy, it is indispensable to know the informational characteristics of the business environment under consideration thoroughly so as to supply a proper database design answer but, ...
MDCCL's user avatar
  • 8,520
3 votes

M:M Table Design - Use of PK

Here's the canonical design for a Many-to-Many linking table. You generally want an index to support traversal in each direction, and (at least for SQL Server) it doesn't matter whether you use a ...
David Browne - Microsoft's user avatar
2 votes

Surrogate key vs Natural key

I think we should start out clarifying that we are talking about Primary Keys, not alternate keys, right?. And since we are talking about Primary Keys, it is best that they do not change over time. ...
Solomon Rutzky's user avatar
1 vote
Accepted

If all associated attributes of an intended primary key are identical, is it still a true primary key?

The problem with this model is that you did not identify all candidate keys in the relation and did not enforce uniqueness of those keys you did not identify. In reality {SSN} and, possibly, {Name, ...
mustaccio's user avatar
  • 26k
1 vote
Accepted

Should a inherited table have a new surrogate key, or just use parent table's key?

Strategy #2 has a name. It is called Shared Primary Key. It it widely used with is a type relationships. There is a tag over in Stackoverflow that groups question related to this concept. See link
Walter Mitty's user avatar
  • 4,326
1 vote

Natural Keys vs Surrogate Keys part 2

I think your question title, apart from the fact that it is not a question, is a bit misleading. You're not measuring performance of natural/surrogate keys, unlike the question you're referencing. ...
mustaccio's user avatar
  • 26k
1 vote

Common term for tenant-based keys

A natural key (AKA business key or domain key) is a key that is used to identify information in the business domain. A surrogate is a key that is "unseen", internal to the database, meaningless and ...
nvogel's user avatar
  • 3,767
1 vote

Foreign keys - link using surrogate or natural key?

Older question but still very relevant to data architecture. The debate to go surrogate or go natural is ongoing and of course it depends. Very good points made for both sides here and other questions ...
Alexander Kirstein's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible