Drop Column with Foreign Key Problem

From: "Thomas Chille (spoon)" <t(dot)chille(at)spoon(dot)de>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Drop Column with Foreign Key Problem
Date: 2004-07-01 18:47:19
Message-ID: 0a0f01c45f9b$d6c12bd0$500a0a0a@spoon.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello!

I have a Problem.

A table with a ForeignKeyRef exits. The Name of the field with this FK-Constraint is 'id_employee'
because it is referencing the field 'id_employee' in 'public.sd_employee'. I drop this column of the
table with cascade in a script:

ALTER TABLE public.sd_messaging_participant DROP COLUMN id_employee CASCADE;

In one script later i try to make an update on the referenced Table:

UPDATE sd_employee SET leave = 1.5;

But it doesent works. I get always this Error:

ERROR: constraint participant_employee: table sd_messaging_participant does not have an attribute
id_employee

The constraint 'participant_employee' should be droped too, due the use of CASCADE, but it seems
that he is alive.

Also explizit dropping the constraint 'participant_employee' before dropping the field will not
solve the problem:

ALTER TABLE public.sd_messaging_participant DROP CONSTRAINT participant_employee CASCADE;

If i try to drop the constraint after dropping the field, postgres means the constraint is not
existing anymore. But if i try to do the update it produces still this error.

If i dump the DB i can not found the constraint 'participant_employee' anymore but 3 triggers
belonging to this constraint are still in the DB:

...
CREATE CONSTRAINT TRIGGER participant_employee
AFTER INSERT OR UPDATE ON sd_messaging_participant
FROM sd_employee
NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_check_ins" ('participant_employee', 'sd_messaging_participant',
'sd_employee', 'UNSPECIFIED', 'id_employee', 'id_employee');
...

That is for Insert and there are one for Update and one for Deleting too. I have absolutly no idea
how can this happens and i think i could solve this problem by dropping these 3 Triggers. But i dont
know how is the syntax to drop such triggers?

And know anybody out there how it could happen?

Thanks for your help,
Thomas!

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sheeraz Fazal 2004-07-01 19:25:25 Full Text Index Info
Previous Message Jason E. Stewart 2004-07-01 18:36:31 Re: pgFoundry Open For Business