Reputation: 46613
There's a healthy debate out there between surrogate and natural keys:
My opinion, which seems to be in line with the majority (it's a slim majority), is that you should use surrogate keys unless a natural key is completely obvious and guaranteed not to change. Then you should enforce uniqueness on the natural key. Which means surrogate keys almost all of the time.
Example of the two approaches, starting with a Company table:
1: Surrogate key: Table has an ID field which is the PK (and an identity). Company names are required to be unique by state, so there's a unique constraint there.
2: Natural key: Table uses CompanyName and State as the PK -- satisfies both the PK and uniqueness.
Let's say that the Company PK is used in 10 other tables. My hypothesis, with no numbers to back it up, is that the surrogate key approach would be much faster here.
The only convincing argument I've seen for natural key is for a many to many table that uses the two foreign keys as a natural key. I think in that case it makes sense. But you can get into trouble if you need to refactor; that's out of scope of this post I think.
Has anyone seen an article that compares performance differences on a set of tables that use surrogate keys vs. the same set of tables using natural keys? Looking around on SO and Google hasn't yielded anything worthwhile, just a lot of theorycrafting.
Important Update: I've started building a set of test tables that answer this question. It looks like this:
Every part is joined to a plant and every instance of a part at a plant is joined to an engineer. If anyone has an issue with this testbed, now's the time.
Upvotes: 7
Views: 6646
Reputation: 146603
Use both! Natural Keys prevent database corruption (inconsistency might be a better word). When the "right" natural key, (to eliminate duplicate rows) would perform badly because of length, or number of columns involved, for performance purposes, a surrogate key can be added as well to be used as foreign keys in other tables instead of the natural key... But the natural key should remain as an alternate key or unique index to prevent data corruption and enforce database consistency...
Much of the hoohah (in the "debate" on this issue), may be due to what is a false assumption - that you have to use the Primary Key for joins and Foreign Keys in other tables. THIS IS FALSE. You can use ANY key as the target for foreign keys in other tables. It can be the Primary Key, an alternate Key, or any unique index or unique constraint., as long as it is unique in the target relation (table). And as for joins, you can use anything at all for a join condition, it doesn't even have to be a key, or an index, or even unique !! (although if it is not unique you will get multiple rows in the Cartesian product it creates). You can even create a join using non-specific criterion (like >, <, or "like" as the join condition.
Indeed, you can create a join using any valid SQL expression that evaluate to a boolean.
Upvotes: 9
Reputation: 425863
Any type can be used for a surrogate key, like a VARCHAR
for the system-generated slug
or something else.
However, most used types for surrogate keys are INTEGER
and RAW(16)
(or whatever type your RDBMS
does use for GUID
's),
SSN
) takes exactly same time.Comparing VARCHAR
s make take collation into account and they are generally longer than integers, that making them less efficient.
Comparing a set of two INTEGER
is probably also less efficient than comparing a single INTEGER
.
On datatypes small in size this difference is probably percents of percents of the time required to fetch pages, traverse indexes, acquite database latches etc.
And here are the numbers (in MySQL
):
CREATE TABLE aint (id INT NOT NULL PRIMARY KEY, value VARCHAR(100));
CREATE TABLE adouble (id1 INT NOT NULL, id2 INT NOT NULL, value VARCHAR(100), PRIMARY KEY (id1, id2));
CREATE TABLE bint (id INT NOT NULL PRIMARY KEY, aid INT NOT NULL);
CREATE TABLE bdouble (id INT NOT NULL PRIMARY KEY, aid1 INT NOT NULL, aid2 INT NOT NULL);
INSERT
INTO aint
SELECT id, RPAD('', FLOOR(RAND(20090804) * 100), '*')
FROM t_source;
INSERT
INTO bint
SELECT id, id
FROM aint;
INSERT
INTO adouble
SELECT id, id, value
FROM aint;
INSERT
INTO bdouble
SELECT id, id, id
FROM aint;
SELECT SUM(LENGTH(value))
FROM bint b
JOIN aint a
ON a.id = b.aid;
SELECT SUM(LENGTH(value))
FROM bdouble b
JOIN adouble a
ON (a.id1, a.id2) = (b.aid1, b.aid2);
t_source
is just a dummy table with 1,000,000
rows.
aint
and adouble
, bint
and bdouble
contain exactly same data, except that aint
has an integer as a PRIMARY KEY
, while adouble
has a pair of two identical integers.
Performance difference, if any, is within the fluctuations range.
Upvotes: 3