Обсуждение: Why does Postgres allow duplicate (FK) constraints

Hi,

I was a bit surprised that the following DDL will work:

create table parent (id integer not null primary key);
create table child (id integer not null primary key, pid integer not null);

alter table child
   add constraint fk_child_parent
       foreign key (pid) references parent(id);

alter table child
   add foreign key (pid) references parent(id);


It essentially creates the same foreign key constraint twice.

While I agree that this SQL should not have been written like this in the first place, I wonder why Postgres doesn't
activelyprevent this (like e.g. Oracle). 

Is there a technical reason, or is it simply a matter of "no one cared enough to change this"?

Regards
Thomas

Thomas Kellerer <spam_eater@gmx.net> writes:
> While I agree that this SQL should not have been written like this in the first place, I wonder why Postgres doesn't
activelyprevent this (like e.g. Oracle). 

If Oracle does that, they're violating the SQL standard --- there is
nothing in the standard that supports rejecting an ALTER TABLE ADD
CONSTRAINT on the grounds that it's redundant.  The spec only says
you can't give two constraints the same name.

            regards, tom lane

Tom Lane, 26.03.2013 17:03:
>> While I agree that this SQL should not have been written like this
>> in the first place, I wonder why Postgres doesn't actively prevent
>> this (like e.g. Oracle).
>
> If Oracle does that, they're violating the SQL standard --- there is
> nothing in the standard that supports rejecting an ALTER TABLE ADD
> CONSTRAINT on the grounds that it's redundant.  The spec only says
> you can't give two constraints the same name.

Is there anything in the standard that actively requires that you can create two "identical" constraints?

Because technically it simply doesn't make sense, does it?

Regards
Thomas

Thomas Kellerer <spam_eater@gmx.net> writes:
> Tom Lane, 26.03.2013 17:03:
>> If Oracle does that, they're violating the SQL standard --- there is
>> nothing in the standard that supports rejecting an ALTER TABLE ADD
>> CONSTRAINT on the grounds that it's redundant.  The spec only says
>> you can't give two constraints the same name.

> Is there anything in the standard that actively requires that you can create two "identical" constraints?

The lack of any prohibition to the contrary means there is no way to
argue that the code you showed previously violates the spec; thus,
a database that fails to accept it is rejecting spec-compliant DDL.

> Because technically it simply doesn't make sense, does it?

Well, it's redundant, but that doesn't make it wrong.  In any case,
there are lots of ways that things might be redundant.  Should we
reject a unique constraint on (a,b) if there's already one on (b,a)?
Or if there are separate unique constraints on each of a and b?

            regards, tom lane

Tom Lane, 26.03.2013 17:16:
> The lack of any prohibition to the contrary means there is no way to
> argue that the code you showed previously violates the spec; thus,
> a database that fails to accept it is rejecting spec-compliant DDL.

I'm not claiming that the spec is violated...
(And I'm not complaining either. I'm just curious if there was a technical reason)

> Well, it's redundant, but that doesn't make it wrong.  In any case,
> there are lots of ways that things might be redundant.  Should we
> reject a unique constraint on (a,b) if there's already one on (b,a)?
> Or if there are separate unique constraints on each of a and b?

Hmm, good point.

Although I think a definition that is identical with regards of the columns and their position in the constraint
_could_be considered identical. 

Anyway thanks for the feedback.

Alban Hertroys, 26.03.2013 17:17:
> It can make sense during a maintenance window, if you create a new
> (redundant) FK constraint concurrently to replace the existing one.
> If you'd first remove the existing constraint, you're allowing FK
> violations until the new constraint has finished creating its index.
>
> This happens for example if you want to use a different index
> algorithm, say a gist index instead of a btree index, or if the
> initial index has gotten corrupt somehow and it needs reindexing.

I can understand this for indexes, but a foreign key constraint does not create one.

Regards
Thomas

Thomas Kellerer <spam_eater@gmx.net> wrote:
> Alban Hertroys, 26.03.2013 17:17:
>> It can make sense during a maintenance window, if you create a new
>> (redundant) FK constraint concurrently to replace the existing one.
>> If you'd first remove the existing constraint, you're allowing FK
>> violations until the new constraint has finished creating its index.
>>
>> This happens for example if you want to use a different index
>> algorithm, say a gist index instead of a btree index, or if the
>> initial index has gotten corrupt somehow and it needs reindexing.
>
> I can understand this for indexes, but a foreign key constraint does not create
> one.

I once saw a case where this needed to be done because the
dependency information somehow became inconsistent.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company