469,898 Members | 1,576 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,898 developers. It's quick & easy.

Drop Column with Foreign Key Problem

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!
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #1
1 2225
"Thomas Chille (spoon)" <t.******@spoon.de> writes:
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


It looks to me like you created this database by loading a dump from an
older (pre-7.3) Postgres? If so, there are not any dependency links
from those triggers to the columns involved, and so dropping the columns
doesn't cause the triggers to go away. You'll need to drop the triggers
by hand.

There is a contrib script (contrib/adddepend) that tries to add the
missing dependencies in an upgraded database. I've never used it and
am not sure how reliable it is, but it probably beats trying to identify
the trouble spots by hand.

See the 7.3 release notes for more about this issue.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Sabrina | last post: by
2 posts views Thread by James Knowlton | last post: by
5 posts views Thread by Markus | last post: by
2 posts views Thread by clickon | last post: by
8 posts views Thread by shsandeep | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.