none
The UPDATE statement conflicted with the FOREIGN KEY SAME TABLE constraint

    Question

  • Hello team,
    I have two tables one is Temp, the structure of both is shape of  EmployeeID and ManagerID with a FK relation on the same table, the temp table does not have this FK at all.

    I have two problems:
    1) I'm almost sure that every EmployeeID has a ManagerID except for the most parent has a Null parent, so the relation is fine; I need to make sure that this is the case with a sql statement ( I have created some, but need a verification from experts like you)

    2) When I update the orginal table with the temp table I get the error "The UPDATE statement conflicted with the FOREIGN KEY SAME TABLE constraint"

    I'm using ADO to execute this update in a transaction using a stored procedure (SQL server 2005) ...I need to put my hand on the problem.
    Is there something like NOCHECK can handle this situatin?

    Your quick response is very much appreciated.

    TheScorpion
    Thursday, November 19, 2009 7:11 PM

All replies

  • Why don't you post the table structure?
    Abdallah, PMP, ITIL, MCTS
    Thursday, November 19, 2009 8:16 PM
  • This is the alter of the main table...the Temp table is the same but has only a Primary key

    CREATE TABLE [dbo].[lkOrganization](
    [Organization_ID] [varchar](30) NOT NULL,
    [Parent_Organization_ID] [varchar](30) NULL,
    [Organization_Desc] [dbo].[typLongDesc] NOT NULL,

     CONSTRAINT [PK__lkOrganization__1312E04B] PRIMARY KEY CLUSTERED 
    (
    [Organization_ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF
    GO

    ALTER TABLE [dbo].[lkOrganization]  WITH NOCHECK ADD  CONSTRAINT [FK__lkOrganiz__Paren__15EF4CF6] FOREIGN KEY([Parent_Organization_ID])
    REFERENCES [dbo].[lkOrganization] ([Organization_ID])
    GO

    ALTER TABLE [dbo].[lkOrganization] CHECK CONSTRAINT [FK__lkOrganiz__Paren__15EF4CF6]
    GO


    I hope this helps.

    Thank you for quick response.



    TheScorpion
    Thursday, November 19, 2009 8:33 PM
  • On you foreign key constraint you need to add ON UPDATE CASCADE so your update can succeed and you don't get the conflict issue. Check out this link
    http://msdn.microsoft.com/en-us/library/ms174979.aspx

    As for finding out about the parent-child relation, a query like this would help


    DECLARE @t TABLE (FName VARCHAR(20),Manager INT,Employee INT)
    INSERT INTO @T
    SELECT 'John',NULL,1 UNION ALL
    SELECT 'Mike',1,2    UNION ALL
    SELECT 'Sam',1,3     UNION ALL
    SELECT 'Ben',2,4     UNION ALL
    SELECT 'Julie',1,5   UNION ALL
    SELECT 'Tom',4,6     UNION ALL
    SELECT 'Kali',2,7
    
    ;WITH CTE AS
    (
       SELECT FName,Manager,Employee
        FROM @t WHERE Manager IS NULL
    	UNION ALL
    	SELECT t.FName,t.Manager,t.Employee 
    	FROM CTE C
    		JOIN @t t ON C.Employee = t.Manager
    )
    SELECT * FROM CTE

    Abdallah, PMP, ITIL, MCTS
    Thursday, November 19, 2009 8:47 PM
  • Thank you..
    I verified that my data is fine.

    Is there any other way to make this update without changing table structure (without altering it) ?

    like adding something in the stored procedure..
    I don't want to change table structure.

    Any suggestions ?

    Thank you

    TheScorpion
    Friday, November 20, 2009 3:28 PM
  • Adding the CASCADE is not really changing the structure. No indexes will be dropped or created, or any keys will be affected. You are basically redefining the behaviour of the PK-FK relationship. I'm not aware of any resolution around the CASCADE
    Abdallah, PMP, ITIL, MCTS
    Friday, November 20, 2009 3:36 PM
  • The database that contains this table is being replicated and related to two other databases, altering will need me to stop replication, which is not business needed. I used a very silly solution for the time being by looping through records and run an appropriate update statement for each record, it consumes more time but is doing the job.
    TheScorpion
    Saturday, November 21, 2009 1:44 AM
  • Hello again,
    I tried to create ON CASCADE UPDATE on the FK, and it was rejected by SQL engine saying:
    --------------------------------------------------------------------------------------------------------------
    Introducing FOREIGN KEY constraint 'fk_xyz' on table 'tbl' may
    cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON
    UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
    --------------------------------------------------------------------------------------------------------------

    How this would be solved? 

    Note: The above loop solution is getting much slower due to increasing number rows to be updated.

    Thank you.
      

    TheScorpion
    Monday, November 30, 2009 2:32 PM
  • The issue is that you are either changing the Organization_Id and there are rows in the table where the old value exists in the Parent_Organization_Id; or you are trying to change the Parent_Organization_id to an Organization_Id that doesn’t exisit.

     

    For the first case, the easiest solution, would be to insert a new record with the new Organization_Id, and then update the existing records from the old Organization_Id to the new Organization_Id, and then delete the old record….

    CREATE TABLE Test1
    (
    	Parent INT PRIMARY KEY
    	,Child INT 
    )
    ALTER TABLE Test1 ADD CONSTRAINT FK1 FOREIGN KEY (Child) 
    	REFERENCES Test1(Parent)
    GO
    
    INSERT INTO Test1
    SELECT 1,NULL
    UNION SELECT 2,1
    UNION SELECT 3,2
    UNION SELECT 4,2
    UNION SELECT 5,3
    GO
    
    SELECT * FROM Test1
    -- This statment generates the error...
    --UPDATE Test1
    --SET Parent = 6
    --WHERE Parent = 2
    
    -- This gets around the error
    INSERT INTO Test1 VALUES (6,1)
    
    UPDATE Test1 
    SET Child = 6
    WHERE Child = 2
    
    DELETE FROM Test1 WHERE Parent = 2
    
    SELECT * FROM Test1
    
    
    DROP TABLE Test1
    

    Monday, November 30, 2009 3:37 PM
  • Thanks Jay for your reply, but the issue is not when using an update statement to update a single row (of course I could solve such situation), if you read the thread from the beginning you'll see that  data is consistent and I'm using a bulk update statement to update a table from another table with the help of inner join, this bulk update fails. This is the main issue.
    TheScorpion
    Tuesday, December 01, 2009 12:58 AM
  • Ok, I understand that you are were attempting to update the table in a bulk update, I was just trying to demonstrate what would throw the error and one approach around it.  Can you provide a bit more information about the update statement, or better yet post the statement here. Are you updating both the Organization_Id and the Parent_Organization_Id, or just one of the two?

    Is it possible to provide, the insert scripts to populate both the actual table and the temp table, with values are actually failing?
    Tuesday, December 01, 2009 2:16 AM
  • Thanks Jay for the quick reply, actually I can not send sample data that would cause the bulk update to fail. When data was around 14,000 rows, update worked fine, but after some business changes and rows became around 700,000 it is not working any more. 
    I have assured that data is consistent, even same data with the error message using the bulk update has been updated successfully but using a loop and update row by row, which proves the correctness of data. 

    The update statement is very simple and executed in a stored procedure: 

     update 
    lkOrganization 
     set 
    lkOrganization.Parent_Organization_ID = lkOrganization_Temp.Parent_Organization_ID, 
     lkOrganization.Organization_Desc = lkOrganization_Temp.Organization_Desc

     from lkOrganization, lkOrganization_Temp 
    where 
    lkOrganization.Organization_ID = lkOrganization_Temp.Organization_ID 


     I hope this helps.
    TheScorpion
    • Edited by InTheMirror Tuesday, December 01, 2009 4:08 PM formating
    Tuesday, December 01, 2009 4:06 PM
  • From the update statement, we can tell that only the organizations in the main table are being updated where they match in the temp table.  So, that leaves me to believe that 1 or more of the Parent Organization Ids in the temp table does not exist as an Organization Id in the main table.

     

    Can you verify that all the Parent Organization Ids exist as Organization Ids in the main table….

    SELECT	lkOrganization_Temp.Parent_Organization_ID
    		,lkOrganization.Organization_ID
    FROM	lkOrganization_Temp
    		LEFT OUTER JOIN lkOrganization
    				ON lkOrganization_Temp.Parent_Organization_Id = lkOrganization.Organization_ID
    WHERE	lkOrganization.Organization_ID IS NULL

    Tuesday, December 01, 2009 5:03 PM
  • This is the alter of the main table...the Temp table is the same but has only a Primary key

    CREATE TABLE [dbo].[lkOrganization](
    [Organization_ID] [varchar](30) NOT NULL,
    [Parent_Organization_ID] [varchar](30) NULL,
    [Organization_Desc] [dbo].[typLongDesc] NOT NULL,

    TheScorpion
    How about using INT IDENTITY(1,1) for Primary Key?

    CREATE TABLE [dbo].[lkOrganization](
     [Organization_ID] INT IDENTITY(1,1) PRIMARY KEY,
     [Parent_Organization_ID] INT NULL REFERENCES lkOrganization(Organization_ID),
     [Organization_Desc] [dbo].[typLongDesc] NOT NULL)


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Wednesday, December 09, 2009 2:30 AM
  • Hello team,
    Jay I ran your query against my tables and it returned one row with a null value which is expected (the most parent Organization)..data is perfectly populated, no doubt with data. Also I proved to myself that by running a .Net application that takes data from the temp table and update the original table by going row by row and this succeeds, but takes around 5 hours to run and locks many tables during this operation.

    SQLUSA, this is not applicable because the Organization_ID is a varchar data type and has a special formation and could not be numeric.

    Also, I'm not going to change table structure to solve an issue with the bulk update statement.
    I wonder how the update occurs behind the scenes. What type of sorting mechanism it performs to perform this update.

    Thank you.


    TheScorpion
    Wednesday, December 09, 2009 11:58 PM

  • SQLUSA, this is not applicable because the Organization_ID is a varchar data type and has a special formation and could not be numeric.


    TheScorpion


    You can keep Organization_ID as a KEY (unique constraint) and introduce:

    Org_ID INT IDENTITY (1,1) PRIMARY KEY

    You can shift over to the new PK gradually until you won't need Organization_ID as a KEY any longer, then you can downgrade it to unique index.

    To All: Contrary to public belief an FK can point to (reference) UNIQUE KEY instead of a PRIMARY KEY.

    Once you do that the entire issue will vanish into Computer Science history.


    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Thursday, December 10, 2009 2:01 AM