DELETE statement conflicted with COLUMN REFERENCE

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

DELETE statement conflicted with COLUMN REFERENCE

Author

Topic

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-11-08 : 15:13:34

Hi, all,

I tried to run this next DELETE and got the error below.

delete from dbo.account where left(account_number, 1) = 'P'

DELETE statement conflicted with COLUMN REFERENCE constraint 'fk_user_order'.
The conflict occurred in database 'QWright', table 'UserOrder', column 'UserId'.
The statement has been terminated.

My problem is that I could not find this 'fk_user_order' either on account or UserOrder. I was thinking of running a drop constraint.

I have this trigger on the table, but fk_user_order is no where to be found on the UserIDMaster.

CREATE TRIGGER wt_td_account_instead on dbo.account INSTEAD of DELETE
AS
BEGIN
SET NOCOUNT ON

DECLARE @old_account_number varchar(20),
@old_account_type varchar(2)

SELECT @old_account_number = account_number,
@old_account_type = account_type
FROM deleted

-- Delete ORDERS FIRST
DELETE
FROM order_header
WHERE account_number = @old_account_number

-- Then USERS that are CONTACTS of this ACCOUNT
DELETE
FROM UserIdMaster
FROM UserAccount AS a
WHERE a.account_number = @old_account_number
AND UserIdMaster.userId = a.UserId
AND UserIdMaster.UserTypeCode = @old_account_type

-- Lastly the actual ACCOUNT
delete dbo.account
from dbo.account t2, deleted t1
where t2.account_number = t1.account_number
--end of trigger

BTW, this operation will first apply to test data then run on production data during the night one time only.

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-11-08 : 15:18:18

is DISABLE TRIGGER a good option?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-08 : 16:02:06

If you run this, do you get any rows back?

sp_fkeys @fktable_name = [UserOrder]

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-11-08 : 16:52:46

Yes, it did return two rows. One is related here (I guess).

PK tbl =UserIdMaster
PK col=UserId
FK tbl= UserOrder
FK col= UserId
delete rule=1
FK name=fk_user_order
PK name=PK__UserIdMaster__74AE54BC

The problem is I did not find their connection(s) with the table I am dealing with: account.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-08 : 17:24:10

Foreign keys are defined in the referencing table, not the primary key table. You need to delete the rows from UserOrder before you can delete the rows from Account - or you can drop the constraint (from the UserOrder table) and get corrupt data.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-08 : 18:07:59

This script will show you the referencing tables.

Find Table Reference Levels
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957

CODO ERGO SUM

Hommer
Aged Yak Warrior

808 Posts

Posted - 2006-11-09 : 09:25:07

Thanks!
I will try Michael's script later today.
I think my issue is that from my account table, I did not find any ref to UserOrder. There must be some kind of three-way relationship that I have not found.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-09 : 10:35:11

quote:
Originally posted by Hommer

Thanks!
I will try Michael's script later today.
I think my issue is that from my account table, I did not find any ref to UserOrder. There must be some kind of three-way relationship that I have not found.

No, I already told you - there is nothing on your account table because relationships are established with a foreign key constraint on the child table. You can look all you like, but there is nothing on the account table and certainly no "kind of three-way relationship", you have simply got a foreign key constraint on the UserOrder table, exactly like the error message says.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-11-09 : 14:04:07

You can lead a horse to water...

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources