Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [surrogate-key]

Surrogate Keys are used to abstract the link between related tables using a value that has no direct bearing on the actual data stored in the rows using the surrogate key. The advantages of this style of key include the ability to edit any part of a row without having to update key values in related tables. Natural Keys are the complement of Surrogate Keys; more info can be found at http://dba.stackexchange.com/tags/natural-key/info

Surrogate Keys are used to abstract the link between related tables using a value that has no direct bearing on the actual data stored in the rows using the surrogate key. The advantages of this style of key include the ability to edit any part of a row without having to update key values in related tables. Natural Keys are the complement of Surrogate Keys; more info can be found at .

33 questions
57
votes
7 answers

Why should I create an ID column when I can use others as key fields?

Possible Duplicate: Why use an int as a lookup table's primary key? So far, I'm accustomed to creating an ID column for every table and it is practical in a way that it makes me not think about decision making about primary key theories. The…
Uğur Gümüşhan
  • 717
  • 2
  • 6
  • 12
40
votes
3 answers

Should every table have a single-field surrogate/artificial primary key?

I understand one benefit of surrogate/artificial keys in general - they do not change and that can be very convenient. This is true whether they are single or multiple field - as long as they are 'artificial'. However, it sometimes seems to be a…
Jack Douglas
  • 40,517
  • 16
  • 106
  • 178
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
8
votes
5 answers

What normal form does a surrogate key violate?

I have following question: "What normal form does a surrogate key violate?" My thought was the 3rd normal form, but I'm not quite sure it's just an assumption i am making. Could someone explain that to me?
Marco
  • 91
  • 1
  • 3
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
2 answers

Should I set document version field to be part of a primary key

Should I set document version as a primary key I am working on a document management system, and I have these two tables representing part of the whole tables. On the DocumentVersion table I have set the primary key to be (version + documentID) .…
john Gu
  • 1
  • 2
  • 4
  • 8
4
votes
1 answer

Surrogate vs composite key in hierarchical data structure

I'm currently evaluating a schema for a hierarchical data structure. My main problem is how I should design the schema to prevent inconsistent data (reference of foreign key in another hierarchy). The two variants I discovered are either to use a…
Dave
  • 173
  • 4
3
votes
1 answer

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

In the following example, we have a Comments table with a composite primary key (PK) CREATE TABLE Comments ( video_id INT NOT NULL, user_id INT NOT NULL, comment_dt DATETIME NOT NULL, PRIMARY KEY (video_id, user_id, comment_dt), FOREIGN…
brennvo
  • 91
  • 1
  • 4
2
votes
2 answers

Composite primary key plus a separate (surrogate) id column for foreign key references

I am trying to build a database about locations like so: Country → Region → Town Countries will be populated with a list of pre-defined worldwide countries, regions & towns are input by users. Region may not apply to every location and so a blank…
the_peacock
  • 123
  • 3
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

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

I am modeling some things with an "is a" relationship (aka inheritance), and there were two ways to go about it in regards to the inherited table's primary key and foreign key pointing back to the parent table: Strategy #1 was to always create a…
Anssssss
  • 248
  • 1
  • 11
2
votes
2 answers

Replacing composite key with surrogate

I have the following table that has a couple of million rows in it and is 99% fragmented virtually all of the time. My plan was to insert a IDENTITY field as a surrogate key to replace the current composite 6 field primary, then make the current key…
DamagedGoods
  • 2,591
  • 6
  • 34
  • 48
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
2
votes
1 answer

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

I am in the middle of an interesting "Data Architecture" discussion at a place I am contracting & need some input. As a habit, I always create a Surrogate Key as the Primary Key in my tables - be they Guid (UUID) or Identity values. IMHO the…
Prisoner ZERO
  • 129
  • 1
  • 5
1
2 3