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 actively prevent 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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 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.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 26 March 2013 17:07, Thomas Kellerer <spam_eater@gmx.net> wrote:
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general