Re: surrogate vs natural primary keys

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