473,856 Members | 1,568 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_empl oyee'. I drop this column of the
table with cascade in a script:

ALTER TABLE public.sd_messa ging_participan t 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_emp loyee: table sd_messaging_pa rticipant does not have an attribute
id_employee

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

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

ALTER TABLE public.sd_messa ging_participan t DROP CONSTRAINT participant_emp loyee 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_em ployee' anymore but 3 triggers
belonging to this constraint are still in the DB:

....
CREATE CONSTRAINT TRIGGER participant_emp loyee
AFTER INSERT OR UPDATE ON sd_messaging_pa rticipant
FROM sd_employee
NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_check_ ins" ('participant_e mployee', 'sd_messaging_p articipant',
'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 2470
"Thomas Chille (spoon)" <t.******@spoon .de> writes:
If i dump the DB i can not found the constraint 'participant_em ployee' anymore but 3 triggers
belonging to this constraint are still in the DB: CREATE CONSTRAINT TRIGGER participant_emp loyee


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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
25591
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
2
33386
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 constraint is using the column. Here is the create script for the table create table mytable (col1 char(1) not null, col2 char(1) default 'A' not null )
2
27019
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: REFERENCES ref_table ) Here is the error and the 'bad' SQL code: Server: Msg 8148, Level 16, State 1, Line 4
5
51732
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
7
2957
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 Null
2
8640
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 customer complaints table and one of the fields on the DetailsView control is called ComplaintType. The field is a template field and in insert mode and edit mode i have used a DropDownList control bound to a set of keys and values in a ComplaintTypes...
8
11816
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.
1
11014
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 1 Could not drop object 'Client' because it is referenced by a FOREIGN KEY constraint.
0
2163
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. Environment I have tried severally to drop Foreign Key constraints on a set of tables and each time I get a SQL0204N "SQL071030104307060" is an undefined name. ". However, when I go back to look at the table or load data, I get a constraint error...
0
9916
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9762
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11057
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10696
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
7932
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7094
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5958
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4174
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3201
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.