Re: Foreign key constraint still active after table row removed

> -----Original Message-----
> From: pgsql-bugs-owner(at)postgresql(dot)org
> [mailto:pgsql-bugs-owner(at)postgresql(dot)org] On Behalf Of Tom Lane
> Sent: Wednesday, September 10, 2003 6:42 PM
> To: Christoph Jaeger
> Cc: pgsql-bugs(at)postgresql(dot)org
> Subject: Re: [BUGS] Foreign key constraint still active after
> table row removed
>
>
> "Christoph Jaeger" <christoph(dot)jaeger(at)dhl(dot)com> writes:
> > PostgreSQL version (example: PostgreSQL-7.3.4): postgresql 7.1.3
>
> 7.1.3 is ancient history, and no it doesn't have defenses against you
> changing a column definition that a foreign key linkage refers to.
> I'd recommend updating to 7.3.4.
>

Ok, so I will upgrade.

There are already some invalid constraints in my database due to some
fields I dropped earlier. I only found out about these now, because the
data in these tables is quite static, and I did only INSERTs (which do
not trigger the constraints), no UPDATEs. The problem is, these
constraints get exported in a pg_dump, and, of course, reimported with a
pg_restore. I guess I can solve this by manually editing the dump file
(remove the unneeded CREATE CONSTRAINT statements). Is there a better
way to do this?

Thanks a lot,

Best Regards,

Christoph Jäger

> > The table pg_trigger shows three rows, which seem to point
> to this no
> > longer valid constraint, but I do not think it is a good
> idea to fiddle
> > with this unless one really knows how this all works together.
>
> In 7.1, drop the triggers and you're done. AFAIR this would also be
> necessary in 7.2. In 7.3 you could have just dropped the columns you
> wanted to drop, and not had all these problems.
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>