From: Daryl Richter <daryl@brandywine.com>
To: pgsql-sql@postgresql.org
Subject: Re: Need help with 'unique parents' constraint
Date: Wed, 14 Sep 2005 09:00:54 -0400
Message-ID: <43281F06.6070607@brandywine.com> (raw)
In-Reply-To: <200509122002.34864.leif@solumslekt.org>
References: <7a5b3f3e2ddebaeda6d9df07ebca943a@biglumber.com>
<200509122002.34864.leif@solumslekt.org>
Leif B. Kristensen wrote:
> On Sunday 11 September 2005 16:04, Greg Sabino Mullane wrote:
>
>
>>Not just old-fashioned, it's the biological law! (among homo sapiens
>>anyway). I'd approach this with a trigger, as you can do complex
>>checks and get back nice customized error messages. A sample script
>>follows. Hard to tell without seeing your whole schema, but I see no
>>need for a relation_id primary key if you already have a unique
>>constraint on child_fk and parent_fk, so I made those into the
>>primary key for the relations table:
>
>
> Thank you for an excellent answer. I think I will have to study your
> code for a while. But is it such a bad idea to have a separate column
> for the primary key here? I see that there are two schools on this,
> with diametrically opposed views. For my own part, I feel that it at
> least doesn't hurt to have a surrogate key. Secondly, a single key
> value is easier to reference from another table than a composite key.
Both are true and as another responder has noted, there are times when
surrogate keys are appropriate.
Be aware, though, that the real danger is data integrity. Should the
alternate key on your composite key get "accidentally" dropped, invalid
data (logical duplicates) can now be inserted.
--
Daryl
"We want great men who, when fortune frowns, will not be discouraged."
-- Colonel Henry Knox, 1776
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: daryl@brandywine.com
Subject: Re: Need help with 'unique parents' constraint
In-Reply-To: <43281F06.6070607@brandywine.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox