Questions tagged [natural-key]

Natural Keys consist of a unique value directly related to the purpose of the row(s) related to the key. For instance, a `Clients` table might have a natural key consisting of `EmailAddress`. The opposite of a natural key is a surrogate key. See for more info.

Filter by
Sorted by
Tagged with
2 votes
1 answer

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

I am designing my first database, and I find myself frustrated by the choice between storing an integer or a string for each instance of a categorical variable. My understanding is that if I have a ...
Stonecraft's user avatar
2 votes
1 answer

Good Natural Key For a Physical Mailing Address

I'm trying to figure out what a good natural key for a Physical Mailing (PO Box) address would be. I haven't designed the fields for the table yet, and initially I was going to go with a surrogate key ...
Mr Mikkél's user avatar
0 votes
1 answer

coping with long natural string key

I have inherited a (quite) big database (for my standards at least, since by no means I call myself a DBA or DB dev). So, on this DB they used a unique natural string as the primary key on EVERY table....
Skaros Ilias's user avatar
2 votes
1 answer

Natural Keys vs Surrogate Keys part 2

A while back, I asked if surrogate keys provide better performance than natural keys in SQL Server. @sqlvogel provided an answer to that question yesterday that caused me to revisit it. This ...
Hannah Vernon's user avatar
  • 69.7k
5 votes
2 answers

Surrogate key vs Natural key

I have a table called devices. Most of the devices that will get stored in this table can be uniquely identified by their serial number and part number. But there are some device types that do not ...
DBK's user avatar
  • 368
1 vote
0 answers

Is the source system typically one of the fields used to uniquely identify business key?

One of the key components of designing a data vault is identifying enterprise-wide unique business keys ("business key" AKA "natural key"). It's not enough to use OrderID to identify records in an ...
Jon of All Trades's user avatar
4 votes
1 answer

Normalized Data Store - Confused with prefixes to use

I'm designing a Staging+NDS+DDS Data Warehouse system, where an ETL is going to normalize data from [Staging] and load it into [NDS], which will hold all history. I've pretty much finished the T-SQL ...
Mathieu Guindon's user avatar
1 vote
2 answers

Column suitable for natural key?

I have read many articles now about natural vs surrogate primary keys, and came to the conclusion there is no single best practice. I have a table that will have around 2000 definite unique values, ...
cecilli0n's user avatar
  • 305
28 votes
3 answers

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

I'm a fan of surrogate keys. There is a risk my findings are confirmation biased. Many questions I've seen both here and at use natural keys instead of surrogate keys ...
Hannah Vernon's user avatar
  • 69.7k
15 votes
4 answers

Foreign keys - link using surrogate or natural key?

Is there a best practice for whether a foreign key between tables should link to a natural key or a surrogate key? The only discussion I've really found (unless my google-fu is lacking) is Jack ...
Callie J's user avatar
  • 492