472,356 Members | 2,087 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,356 software developers and data experts.

Drop Column with Foreign Key Problem


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

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

Nov 23 '05 #1
1 2356
"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?


Nov 23 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Sabrina | last post by:
Hi everybody, I need some help in SQL Server. I am looking for a command that will "Drop all user table" in a user database. Can anyone help me? Thank you very much Sabrina
by: James Knowlton | last post by:
Hello, I'm just returning to MS SQL Server after two years of dealing with Sybase ASE. I need to drop a column, using the alter table command. I keep getting an error indicating that a...
by: adammitchell | last post by:
How can you indicate that a FOREIGN KEY constraint references two columns in two different tables? "SQL Server Books Online" show an example of how to reference two columns in the SAME table:...
by: Markus | last post by:
I tried this: ALTER TABLE Dokumente DROP COLUMN docPrioID but I get this errormessage: DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: COLUMN;ABLE Dokumente DROP;CONSTRAINT
by: am72de | last post by:
Hi all, I've posted this problem some weeks ago, but noone had a solution. Perhaps now someone could help me. I have the following tables: Create Table T1 ( ID1 int Not Null , ID2 int Not...
by: clickon | last post by:
I am using ASP.net 2.0 and trying to take advantage of the updated data editing facilities provided through the SQLDataSource control and the DetailsView control. The data is a record from a...
by: shsandeep | last post by:
What is the quickest way to drop all the foreign keys in a database? Only FKs, not PKs. Cheers, San.
by: apax999 | last post by:
Kinda new to SQL, using SQL Server 2005. I have some foreign keys in a couple of tables. I need to drop these tables, but can't since I'll get the error: Msg 3726, Level 16, State 1, Line...
by: okonita | last post by:
DB2v8.2 LUW. Unable to drop Foreign Key constraints...although DROP constraints returns undefined name. Am I missing somrthing here? Hi all, I have a perplexing DB2 Drop command situation....
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it so the python app could use a http request to get...
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and...
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and credentials and received a successful connection...
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.