Date: Sun, 25 Sep 2005 00:46:52 -0400
From: Ferindo Middleton Jr <fmiddleton@verizon.net>
Subject: Re: redundancy in CHECK CONSTRAINTs
In-reply-to: <26530.1127620701@sss.pgh.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Reply-To: fmiddleton@verizon.net
Message-id: <43362BBC.1050301@verizon.net>
Organization: Sleek Collar
MIME-version: 1.0
Content-type: text/plain; charset=ISO-8859-1; format=flowed
Content-transfer-encoding: 7bit
References: <43361AB7.2020701@verizon.net> <26530.1127620701@sss.pgh.pa.us>
User-Agent: Thunderbird 1.4 (Windows/20050908)

Thank you for your advice, Tom. I've re-done the table in my db using 
the schema you describe below. The is a need for the id field. Other 
tables in my applications use it to refer to any one intsystem/extsystem 
relationship and be able to provide users with one simple number to use 
to refer to them. Thank you.

Ferindo

Tom Lane wrote:
> Ferindo Middleton Jr <fmiddleton@verizon.net> writes:
>   
>> I have the following table:
>>     
>
>   
>> CREATE TABLE gyuktnine (
>>      id               SERIAL,
>>     intsystem      INTEGER NOT NULL REFERENCES yuksystems(id) CONSTRAINT 
>> int_cannot_equal_ext
>>                        CHECK (intsystem != extsystem),
>>     extsystem     INTEGER NOT NULL REFERENCES yuksystems(id) CONSTRAINT 
>> ext_cannot_equal_int
>>                         CHECK (extsystem != intsystem), 
>>     PRIMARY KEY (intsystem, extsystem)
>> );
>>     
>
>   
>> Is this redundant?
>>     
>
> Yes.  I think it's poor style too: a constraint referencing multiple
> columns should be written as a table constraint not a column constraint.
> That is, you ought to write
>
> CREATE TABLE gyuktnine (
>     id            SERIAL,
>     intsystem     INTEGER NOT NULL REFERENCES yuksystems(id),
>     extsystem     INTEGER NOT NULL REFERENCES yuksystems(id),
>     PRIMARY KEY (intsystem, extsystem),
>     CONSTRAINT int_cannot_equal_ext CHECK (intsystem != extsystem)
> );
>
> At least in the earlier versions of the SQL standard, it was actually
> illegal for a column constraint to reference any other columns.  I'm not
> sure if that's still true in the latest spec.  Postgres treats column
> constraints and table constraints alike, but other SQL databases are
> likely to be pickier.
>
> BTW, is there any actual need for the "id" column here, seeing that
> you have a natural primary key?
>
> 			regards, tom lane
>
>