Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit 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

1
vote
1answer
40 views

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 ...
0
votes
1answer
36 views

M:M Table Design - Use of PK

I have a many-to-many table that contains id's from 2 related tables. For creating a primary key on this table, is it better to create another integer column and have that as a surrogate PK? Or ...
2
votes
1answer
228 views

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 ...
5
votes
2answers
458 views

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 ...
1
vote
1answer
350 views

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

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 ...
0
votes
2answers
68 views

Common term for tenant-based keys [closed]

Assume the following schema: ID  | TENANT | TENANT_CUSTOM_ID 346 | 101    | 1 347 | 102    | 1 348 | 101    | 2 349 | 101    | 3 350 | 101    | 4 351 | 102    | 2 352 | 103    | 1 What ...
0
votes
1answer
231 views

Create trigger to ignore user input for Identity Columns

Using the this table definition CREATE TABLE testtable ( id NUMBER GENERATED AS IDENTITY, desc VARCHAR2(30) ); I would like to create a trigger before inserts and updates to ...
0
votes
2answers
334 views

Surrogate keys as Foreign Keys

I have a question about Surrogate keys, say i have two tables(in sql server): Table A: Student_ID, Name, School, Section Table B: Book_ID,Student_ID, IssueDate, BookName If Student_ID is a ...
0
votes
1answer
252 views

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

I recently made a table that looks like the one below CREATE TABLE example( id SERIAL PRIMARY KEY, subindusty_id INT UNIQUE NOT NULL, name TEXT NOT NULL -- other fields... ) I realized that ...
2
votes
1answer
527 views

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 ...
1
vote
2answers
2k views

PostgreSQL surrogate keys: use sequence/bigint or OID?

I'm looking at a need for surrogate keys in tables I'm designing on a PostgreSQL-derived DBMS (Citus). Would OIDs suffice? Is there a downside to using them instead of creating a bigint field and a ...
2
votes
1answer
178 views

Surrogate key resolution, or what am I missing in the Surrogate v Natural argument?

I've read a lot from both camps in the surrogate vs. natural key debate. What I am struggling to understand, and what seems to be glossed over, especially in explaining how surrogate keys are more ...
1
vote
1answer
442 views

How to get a surrogate key value in order to put a foreign key in and create relationship?

I am trying to create two relationships between three tables, using foreign keys with values of primary keys, which are surrogate keys. But I don't understand how to enter the right value in the ...
3
votes
2answers
334 views

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 ...
0
votes
1answer
236 views

Reassigning surrogate keys?

I have a few tables with surrogate keys (SERIAL PRIMARY KEYs unique to each tables). These tables are used by other tables, with references to these keys. My usage of these is quite simple : select ...
0
votes
1answer
210 views

A question regarding the performance of normalization

I am sorry this is such a newb question, but it is one thing I am having a difficult time wrapping my mind around, and after an evening spent reading I finally just decided to ask for some ...
2
votes
2answers
112 views

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. ...
21
votes
3answers
4k views

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 ...
0
votes
2answers
5k views

Can a surrogate key and a primary key be in the same table?

For example we have: Surrogate key: ABC123 Primary key: 1 Name: James Is this legit in a Data Warehouse table?
13
votes
3answers
4k views

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 ...
1
vote
2answers
136 views

Problem with a surrogate key?

I am working with an application for students management. For the student's table I used a surrogate key (auto increment id). The problem I face with the surrogate key is when the user wants to show ...
0
votes
1answer
52 views

Create two tables with identical superkeys?

I have an existing table called Realms. This has the superkey rlmID (just a surrogate key). We are planning to add tables that group realms in some context -- let's just calll them RealmGroups. ...
0
votes
1answer
117 views

Is it acceptable to have a surrogate/primary key of one table be the primary/foreign key of another table?

For my purposes I'm trying to apply this to Microsoft SQL, but would like to know if there are restrictions for other DBMS regarding this. I don't know how to represent UML as ascii characters so if ...
1
vote
2answers
899 views

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 ...
5
votes
5answers
3k views

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 ...
45
votes
7answers
34k views

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

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 ...
31
votes
3answers
5k views

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 '...