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(at)verizon(dot)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
>
>