Re: redundancy in CHECK CONSTRAINTs

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
>
>