Re: surrogate vs natural primary keys - Mailing list pgsql-sql

From Richard Broersma
Subject Re: surrogate vs natural primary keys
Date
Msg-id 396486430809170920o12782b5dy3837b4aa8611a0c1@mail.gmail.com
Whole thread Raw
In response to Re: surrogate vs natural primary keys  ("Scott Marlowe" <scott.marlowe@gmail.com>)
List pgsql-sql
On Wed, Sep 17, 2008 at 7:45 AM, Scott Marlowe <scott.marlowe@gmail.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


pgsql-sql by date:

Previous
From: "Raphael Bauduin"
Date:
Subject: Re: Trigger Procedure Error: NEW used in query that is not in a rule
Next
From: "Raphael Bauduin"
Date:
Subject: Re: Trigger Procedure Error: NEW used in query that is not in a rule