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

0
votes
0answers
15 views

Adding a surrogate key when modelling (conceptual) entity types

I have two (conceptual) entity types: DOCTOR and SPECIALISATION, I believe the cardinality ratio of their relationship is many to many (M:N). I need to break the M:N relationship, but I am not sure ...
0
votes
2answers
63 views

What are the disadvantages of using surrogate keys? [closed]

I am using MS SQL Server but in general database design I want to know what problems can arise when every row in a database has its auto generated surrogate key value. I know some advantages, for ...
0
votes
1answer
58 views

What problems are there in not using a primary key column at all?

What purpose would a primary key column serve on a table such as post_versions here? Nothing refers to it, and there are no queries where I will ever want to select a row by post_versions.id. It'll be ...
1
vote
1answer
45 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
37 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 ...
3
votes
1answer
247 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
491 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
411 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
70 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
245 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
367 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
297 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
549 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
196 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
462 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
337 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
240 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
113 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. ...
22
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
120 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
935 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
35k 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 ...
32
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 '...