statement conflicted with the REFERENCE constraint

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

General SQL Server Forums

New to SQL Server Programming

statement conflicted with the REFERENCE constraint

Author

Topic

fabianus76
Posting Yak Master

191 Posts

Posted - 2006-06-21 : 14:10:07

Hello guys,

I would like to delete a row in a table, but I get the following message :

The DELETE statement conflicted with the REFERENCE constraint "FK_cs_Users". The conflict occurred in database "mytable", table "dbo.cs_UserProfile", column 'UserID'.
The statement has been terminated.
(0.701 sec)

But I did this :

alter table cs_Users disable trigger ALL

delete
from cs_Users
where MembershipID = 'xxx'

Thank you very much for any help to get this work !

Regards,
Fabian

my favorit hoster is ASPnix : www.aspnix.com !

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-21 : 14:16:56

Disabling triggers do not disable constraints. You'll need to drop the constraint, run the delete, then add the constraint back in. Make sure that you delete all of the child data too as that's the whole purpose of the constraint is to prevent orphaned data.

Tara Kizer
aka tduggan

fabianus76
Posting Yak Master

191 Posts

Posted - 2006-06-21 : 14:34:19

You're right Tara, is a bad idea.

Thank you for your help.

Regards,
Fabian

my favorit hoster is ASPnix : www.aspnix.com !

kevin.fitzpatrick
Starting Member

5 Posts

Posted - 2007-12-27 : 12:45:32

I realize this post is ancient, but to anyone who finds this. An easy fix for this is to alter the tables that are affected by the deletes.

In my case it was aspnet_membership, and aspnet_usersinroles. From management studio, right click --> edit, and toward the bottom where it sets the constraints, add "on delete casecade" and execute just the alter. also, you will want to run the same thing with "on update cascade". This fixed my problem in no time flat.

**Strange Game. The only winning move is not to play.**

mandrews1234
Starting Member

9 Posts

Posted - 2009-08-27 : 09:47:47

Now this post is even more ancient but the last post helped me so thanks.

Michael Andrews
Web Developer
www.intellicominc.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-27 : 10:41:47

quote:
Originally posted by mandrews1234

Now this post is even more ancient but the last post helped me so thanks.

Michael Andrews
Web Developer
www.intellicominc.com

you can specify this at time you define the foreign key constraint itself

anthonyszm
Starting Member

1 Post

Posted - 2010-05-22 : 03:16:17

here's the quickest way..

Drop the Key constraint

USE [DBNAME]
GO

IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[mytable_columnn_FK]') AND parent_object_id = OBJECT_ID(N'[dbo].[mytable]'))
ALTER TABLE [dbo].[mytable] DROP CONSTRAINT [BookingContact_Booking_FK]
GO

--run your delete command

--recreate constraint

USE [DBNAME]
GO

ALTER TABLE [dbo].[mytable] WITH NOCHECK ADD CONSTRAINT [mytable_column_FK] FOREIGN KEY([TypeCode])
REFERENCES [dbo].[mytable] ([TypeCode])
GO

ALTER TABLE [dbo].[mytable] CHECK CONSTRAINT [mytable_column_FK]
GO

--that should be it..

anthonyszm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources