From: Ferindo Middleton Jr <fmiddleton@verizon.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: redundancy in CHECK CONSTRAINTs
Date: Sun, 25 Sep 2005 00:46:52 -0400
Message-ID: <43362BBC.1050301@verizon.net> (raw)
In-Reply-To: <26530.1127620701@sss.pgh.pa.us>
References: <43361AB7.2020701@verizon.net>
<26530.1127620701@sss.pgh.pa.us>
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
>
>
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: fmiddleton@verizon.net, tgl@sss.pgh.pa.us
Subject: Re: redundancy in CHECK CONSTRAINTs
In-Reply-To: <43362BBC.1050301@verizon.net>
* 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