DELETE statement conflicted with COLUMN REFERENCE constraint

Question

DELETE statement conflicted with COLUMN REFERENCE constraint

JMI

SSChampion

Points: 11831
More actions
May 12, 2004 at 8:52 am

#152138

On delete I get an error
"DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_User_Organization_User'. The conflict occurred in database 'SLP_Mar13', table 'User_Organization', column 'UserID'."
---------------------------------------------------------------------
CREATE TABLE [dbo].[User] (
[User_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Username] [varchar] (10) NOT NULL ,
[FirstName] [varchar] (64) NOT NULL ,
[LastName] [varchar] (64) NOT NULL ,
) ON [PRIMARY]
ALTER TABLE [dbo].[User] WITH NOCHECK ADD
CONSTRAINT [DF_User_Created] DEFAULT (getdate()) FOR [Created],
CONSTRAINT [DF_User_Modified] DEFAULT (getdate()) FOR [Modified],
CONSTRAINT [DF_User_TimeZone_ID] DEFAULT (3) FOR [TimeZone_ID],
CONSTRAINT [DF_User_Language] DEFAULT ('E') FOR [Language],
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
  [User_ID]
&nbsp ON [PRIMARY] ,
CONSTRAINT [IX_User] UNIQUE NONCLUSTERED
(
  [Username]
&nbsp ON [PRIMARY]
---------------------------------------------------------------------
CREATE TABLE [dbo].[User_Organization] (
[UserID] [int] NOT NULL ,
[Org_ID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[User_Organization] WITH NOCHECK ADD
CONSTRAINT [PK_User_Organizations] PRIMARY KEY CLUSTERED
(
  [UserID],
  [Org_ID]
&nbsp WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[User_Organization] ADD
CONSTRAINT [FK_User_Organization_User] FOREIGN KEY
(
  [UserID]
&nbsp REFERENCES [dbo].[User] (
  [User_ID]
&nbsp ON UPDATE CASCADE NOT FOR REPLICATION ,
CONSTRAINT [FK_User_Organizations_Organization] FOREIGN KEY
(
  [Org_ID]
&nbsp REFERENCES [dbo].[Organization] (
  [Org_ID]
&nbsp NOT FOR REPLICATION
----------------------------------------------
Any ideas why I get this error?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

aiyerganapas Mr or Mrs. 500 Points: 582 More actions · Answer 1

See there are child records available in the User_Organization table. and u have the relationship with the User table. So naturally when you delete a row in the User table which is having the reference value in the User_Organization table it won't allow.

that is why u get this error.

Rgrd,

JMI SSChampion Points: 11831 More actions · Answer 2

Hi iyerganapas,

Why it won't allow me to delete?

Correct me if I'm wrong.

I have UserID=133 in [User].

In [User_Organization] I have UserID=133 OrgID=234.

I have ON DELETE CASCADE to [User_Organization].

Why it cannot delete UserID 133 in both tables?

Peter P SSCrazy Points: 2777 More actions · Answer 3

i SEE WHERE YOU HAVE ON UPDATE CASCADE. BUT YOUR SCRIPT DOES NOT INCLUDE ON DELETE CASCADE.

aiyerganapas Mr or Mrs. 500 Points: 582 More actions · Answer 4

look your organisation table does not have the cluase on delete cascade. that is why u get the error when u try to delete the master record.

Davy_Davido SSC Enthusiast Points: 110 More actions · Answer 5

Hi, deleting a row in User Table will leave any row using that UserID in organization_User Orphan ,you are violating Referential Integrity.The only way to avoid the error is to delete the row in Organization_User first and then the the one in User table.

Regards.

JMI SSChampion Points: 11831 More actions · Answer 6

Thank you all!

It was my mistake.

Instead of having "ON DELETE CASCADE"

I selected "ON UPDATE CASCADE"

and didn't take care of the children in [User_Organization].

Sorry!

But you helped me to realize what I'm doing wrong.

Thank you very much for your help, guys!