Thread: 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
On 26 March 2013 17:07, Thomas Kellerer <spam_eater@gmx.net> wrote:
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.
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?
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.
-- If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.
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