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 http://dba.stackexchange.com/tags/surrogate-key/info for more info.
Questions tagged [natural-key]
10 questions
29
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 http://stackoverflow.com use natural keys instead of surrogate keys based on IDENTITY() values.
My background in computer…
Hannah Vernon
- 70,928
- 22
- 177
- 323
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 Douglas' answer in this question, and his reasoning…
Callie J
- 492
- 3
- 15
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 have serial number and part number assigned to them.…
DBK
- 378
- 1
- 8
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 script that will create the tables and constraints…
Mathieu Guindon
- 914
- 2
- 7
- 16
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 table containing cities that I want to make a child…
Stonecraft
- 125
- 5
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 and a text field for the address, allowing free…
Mr Mikkél
- 123
- 4
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 question is an attempt to "upgrade" the prior question,…
Hannah Vernon
- 70,928
- 22
- 177
- 323
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, each value will range from 5 characters to 40 in…
cecilli0n
- 305
- 2
- 4
- 9
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 Orders table, because when you add another orders…
Jon of All Trades
- 5,987
- 7
- 48
- 63
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. In my novice experience it seems that is…
Skaros Ilias
- 131
- 1
- 1
- 6