Skip site navigation (1) Skip section navigation (2)

Re: surrogate vs natural primary keys

From: "Richard Broersma" <richard(dot)broersma(at)gmail(dot)com>
To: "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>
Cc: Seb <spluque(at)gmail(dot)com>, pgsql-sql(at)postgresql(dot)org
Subject: Re: surrogate vs natural primary keys
Date: 2008-09-17 16:20:44
Message-ID: 396486430809170920o12782b5dy3837b4aa8611a0c1@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-sql
On Wed, Sep 17, 2008 at 7:45 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:

>> CREATE TABLE t2 (
>>    d1 varchar(200),
>>    d2 int8,
>>    d3 varchar(1000),
>>    PRIMARY KEY (d1, d2)
>>    FOREIGN KEY (d1, d2) REFERENCES t1(c1, c2) );
>>
>> thereby avoiding repeating multiple pieces of
>> information?
>
> Yes and no.  If you're gonna hit table t2 a lot by itself, then it's
> more efficient to have the data there in t2 and not have to join to t1
> to get it.  There are always use cases that go either way in this kind
> of situation.

Also, doing this allows you to apply more constraints to T2 (if you
ever wanted to add them).  For example, what if you only wanted to
allow a sub-set of T1(c1,c2) in T2(d1,d2), you could use a check
constraint to enforce this more restrictive relationship.  This
wouldn't be possible (without adding custom triggers) if you only used
a surrogate key.  My experience is that many more validation
constraints are possible if use natural keys are used.  Whether this
feature is a good thing or not is up to you.

My opinion is that the database constraints are the last line of
defense to ensure business rules and data integrity are not violated.
Since I highly value the ability to enforce business rules using
ordinary table DDL, I try to use natural keys as often as I can.


-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

In response to

pgsql-sql by date

Next:From: Raphael BauduinDate: 2008-09-17 17:09:47
Subject: Re: Trigger Procedure Error: NEW used in query that is not in a rule
Previous:From: Raphael BauduinDate: 2008-09-17 15:08:39
Subject: Re: Trigger Procedure Error: NEW used in query that is not in a rule

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group