Re: Foreign Key/ALTER TABLE Issue

On Tue, 25 Jun 2002, Rao Kumar wrote:

> I have noticed that unlike indexes/check constrains, "ALTER TABLE ADD
> CONSTRAINT <c_name> FOREIGN KEY ..." statement does NOT prevent a user from
> re-creating an existing constraint more than once. Following this, a pg_dump
> on the table showed multiple entries of the foreign key constraint/trigger
> definitions.

Correct.  The assumption is that the user knows what he or she is doing
(and thus that the constraints are different in some way).  We might want
to change this at some point, but this isn't only foreign keys (you can
do the same with unique indexes or check constraints afaik) and should
probably be dealt with as such.

> My concerns are:
>
> If it ends up creating multiple triggers (doing the same task), do all these
> triggers get executed for each DML operation ?.

Yes.

> Will this cause a performance hit, if so is there a work-around to
> remove duplicate entries from the sys tables ?

You can remove one set of the triggers from pg_trigger which is pretty
much the only way right now to drop a foreign key constraint.