none
Cannot Update 3 tables with FK dependencies, How to solve ? RRS feed

  • Question


  • Hi,

    I need to know how to resolve a problem, take the following example.
    There're 3 tables to update; table1, table2, table3

    FIELDS ------------------------------------
    table1: CatId(FK), threadid(PK)
    table2: CatId, threadid, lang, cult,
    table3: CatId, threadid, lang, cult,  post

    (Table1 have a FK CatdId that points to a table called categories ..)
    I add a FK from table2 (CatId, threadid) to point to table1 (CatId, threadid)
        (sql adds same FK to table1)
    I add a IX to table2 (threadid, lang, cult) as unique
    I add a FK from table3 (CatId, threadid, lang, cult) to point to table2 (CatId, threadid, lang, cult)
        (sql adds same FK to table2)
    -------------------------------------------
    Up to here OK


    Now if run the following query it GIVES ME ERRORS
    ==================================================
    DECLARE @CategoryId Int
    SET @CategoryId = 1 --or whatever

    UPDATE dbo.[table3] SET CatId = @CategoryId WHERE ThreadId = @ThreadId           
    UPDATE dbo.[table2] SET CatId = @CategoryId WHERE ThreadId = @ThreadId
    UPDATE dbo.[table1] SET CatId = @CategoryId WHERE ThreadId = @ThreadId

    ==================================================

    Mens 547, Nivel 16, Estado 0, Procedimiento Forums.Moderation.ChangeThreadCategory, Línea 36
    Instrucción UPDATE CONFLICTS WITH RESTRICTION FOREIGN KEY "FK_Forums.Categories.Threads.Languages.Posts_Forums.Categories.Threads.Languages". CONFLICT IN table2
    Se terminó la instrucción.

    Mens 547, Nivel 16, Estado 0, Procedimiento Forums.Moderation.ChangeThreadCategory, Línea 41
    Instrucción UPDATE CONFLICTS WITH RESTRICTION REFERENCE "FK_Forums.Categories.Threads.Languages.Posts_Forums.Categories.Threads.Languages". CONFLICT IN table3
    Se terminó la instrucción.

    Mens 547, Nivel 16, Estado 0, Procedimiento Forums.Moderation.ChangeThreadCategory, Línea 45
    Instrucción UPDATE CONFLICTS WITH RESTRICTION REFERENCE "FK_Forums.Categories.Threads.Languages_Forums.Categories.Threads". CONFLICT IN table2
    Se terminó la instrucción.


    WHAT CAN I DO ?

    (without set UPDATE as CASCADE for that FKs)

     

    ANY IDEA ¿?

     

     

    Tuesday, December 21, 2010 5:08 AM

Answers

  • Alejandro,

    Your key should be stable; it should never change. So I would advice against updating the Primary Key. I don't even think that cascading constraints will help you in this case, because SQL Server is very strict about potential circular references (even if the references are not at all circular).

    One way to achieve these 3 UPDATEs is to replace it with 3 INSERTs and 3 DELETEs (or 2 INSERTs, 1 UPDATE and 2 DELETEs). Something like this:

    INSERT INTO dbo.table1 (CatId, othercolumn1, othercolumn2)
    SELECT @CategoryId, othercolumn1, othercolumn2
    FROM dbo.table3
    WHERE ThreadId = @ThreadId
    
    INSERT INTO dbo.table2 (CatId, othercolumn1, othercolumn2)
    SELECT @CategoryId, othercolumn1, othercolumn2
    FROM dbo.table3
    WHERE ThreadId = @ThreadId
    
    UPDATE dbo.[table3] SET CatId = @CategoryId WHERE ThreadId = @ThreadId
    
    DELETE FROM dbo.table2
    WHERE ThreadId = @ThreadId
    AND CatId <> @CategoryId
    
    DELETE FROM dbo.table1
    WHERE ThreadId = @ThreadId
    AND CatId <> @CategoryId
    

    Another option would be to only update one of the three tables, and code all this stuff in a INSTEAD OF trigger on that table.

     


    Gert-Jan
    • Marked as answer by KJian_ Tuesday, December 28, 2010 7:01 AM
    Tuesday, December 21, 2010 8:41 PM

All replies

  • Please post the actual DDL of your tables with questions everytime. This makes easy for answerers to understand your problem more quickly.

     

    According to me you would need disable the FK constraints to update the table, then again enable them, like:

     

    ALTER TABLE table1 NOCHECK CONSTRAINT <fk constraint_name>
    
    <your update stmts>
    
    ALTER TABLE Table1 WITH CHECK CHECK CONSTRAINT <fk constraint_name>
    
    
    

    ... group please let me know if there any other way.



    ~Manu
    http://sqlwithmanoj.wordpress.com
    Tuesday, December 21, 2010 7:13 AM
  • Hello, I don't want to disable FK

    Here' the DDL


    TABLE1=====================================
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[Forums.Categories.Threads](
        [DomainId] [int] NOT NULL,
        [ForumId] [int] NOT NULL,
        [CategoryId] [int] NOT NULL,
        [CreatorId] [int] NULL,
        [ThreadId] [int] IDENTITY(1,1) NOT NULL,
        [Deleted] [datetime] NULL,
     CONSTRAINT [PK_Forums.Categories.Threads] PRIMARY KEY CLUSTERED
    (
        [ThreadId] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
     CONSTRAINT [IX_Forums.Categories.Threads] UNIQUE NONCLUSTERED
    (
        [DomainId] ASC,
        [ForumId] ASC,
        [CategoryId] ASC,
        [ThreadId] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[Forums.Categories.Threads]  WITH CHECK ADD  CONSTRAINT [FK_Forums.Categories.Threads_Domains.Users] FOREIGN KEY([DomainId], [CreatorId])
    REFERENCES [dbo].[Domains.Users] ([DomainId], [UserId])
    GO

    ALTER TABLE [dbo].[Forums.Categories.Threads] CHECK CONSTRAINT [FK_Forums.Categories.Threads_Domains.Users]
    GO

    ALTER TABLE [dbo].[Forums.Categories.Threads]  WITH CHECK ADD  CONSTRAINT [FK_Forums.Categories.Threads_Forums.Categories] FOREIGN KEY([CategoryId])
    REFERENCES [dbo].[Forums.Categories] ([CategoryId])
    GO

    ALTER TABLE [dbo].[Forums.Categories.Threads] CHECK CONSTRAINT [FK_Forums.Categories.Threads_Forums.Categories]
    GO

    ALTER TABLE [dbo].[Forums.Categories.Threads]  WITH CHECK ADD  CONSTRAINT [FK_Forums.Categories.Threads_Forums.Categories1] FOREIGN KEY([DomainId], [ForumId], [CategoryId])
    REFERENCES [dbo].[Forums.Categories] ([DomainId], [ForumId], [CategoryId])
    GO

    ALTER TABLE [dbo].[Forums.Categories.Threads] CHECK CONSTRAINT [FK_Forums.Categories.Threads_Forums.Categories1]
    GO




    TABLE2=====================================
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[Forums.Categories.Threads.Languages](
        [DomainId] [int] NOT NULL,
        [ForumId] [int] NOT NULL,
        [CategoryId] [int] NOT NULL,
        [ThreadId] [int] NOT NULL,
        [LanguageId] [nchar](2) NOT NULL,
        [CultureId] [nvarchar](16) NOT NULL,
        [Thread] [nvarchar](64) NOT NULL,
        [DateCreated] [datetime] NOT NULL,
        [DateClosed] [datetime] NULL,
        [Views] [int] NULL,
        [IsAnnouncement] [bit] NULL,
        [Preference] [bit] NULL,
     CONSTRAINT [IX_Forums.Categories.Threads.Languages] UNIQUE NONCLUSTERED
    (
        [ThreadId] ASC,
        [LanguageId] ASC,
        [CultureId] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
     CONSTRAINT [IX_Forums.Categories.Threads.Languages_1] UNIQUE NONCLUSTERED
    (
        [DomainId] ASC,
        [ForumId] ASC,
        [CategoryId] ASC,
        [ThreadId] ASC,
        [LanguageId] ASC,
        [CultureId] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'If True then the Thread will be on top. No replies can be made' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Forums.Categories.Threads.Languages', @level2type=N'COLUMN',@level2name=N'IsAnnouncement'
    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Only Moderators can be set to True, if True then the Thread will be put on top (below any announcements if they exists)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Forums.Categories.Threads.Languages', @level2type=N'COLUMN',@level2name=N'Preference'
    GO

    ALTER TABLE [dbo].[Forums.Categories.Threads.Languages]  WITH CHECK ADD  CONSTRAINT [FK_Forums.Categories.Threads.Languages_Forums.Categories.Threads] FOREIGN KEY([DomainId], [ForumId], [CategoryId], [ThreadId])
    REFERENCES [dbo].[Forums.Categories.Threads] ([DomainId], [ForumId], [CategoryId], [ThreadId])
    GO

    ALTER TABLE [dbo].[Forums.Categories.Threads.Languages] CHECK CONSTRAINT [FK_Forums.Categories.Threads.Languages_Forums.Categories.Threads]
    GO

    ALTER TABLE [dbo].[Forums.Categories.Threads.Languages] ADD  CONSTRAINT [DF_Forums.Categories.Threads.Languages_DateCreated]  DEFAULT (getdate()) FOR [DateCreated]
    GO





    TABLE3=====================================
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TABLE [dbo].[Forums.Categories.Threads.Languages.Posts](
        [DomainId] [int] NOT NULL,
        [ForumId] [int] NOT NULL,
        [CategoryId] [int] NOT NULL,
        [ThreadId] [int] NOT NULL,
        [LanguageId] [nchar](2) NOT NULL,
        [CultureId] [nvarchar](16) NOT NULL,
        [CreatorId] [int] NULL,
        [PostId] [int] IDENTITY(1,1) NOT NULL,
        [Post] [nvarchar](max) NOT NULL,
        [DateCreated] [datetime] NOT NULL,
        [DateLastUpdate] [datetime] NULL,
        [NotifyRepliesAll] [bit] NULL,
        [NotifyRepliesQuoted] [bit] NULL,
        [PostIdToReply] [int] NULL,
        [IP] [nvarchar](15) NULL,
        [MetaKeywords] [nvarchar](128) NULL,
        [Deleted] [datetime] NULL,
     CONSTRAINT [PK_Forums.Categories.Threads.Languages.Posts] PRIMARY KEY CLUSTERED
    (
        [PostId] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    GO

    ALTER TABLE [dbo].[Forums.Categories.Threads.Languages.Posts]  WITH CHECK ADD  CONSTRAINT [FK_Forums.Categories.Threads.Languages.Posts_Domains.Users] FOREIGN KEY([DomainId], [CreatorId])
    REFERENCES [dbo].[Domains.Users] ([DomainId], [UserId])
    GO

    ALTER TABLE [dbo].[Forums.Categories.Threads.Languages.Posts] CHECK CONSTRAINT [FK_Forums.Categories.Threads.Languages.Posts_Domains.Users]
    GO

    ALTER TABLE [dbo].[Forums.Categories.Threads.Languages.Posts]  WITH CHECK ADD  CONSTRAINT [FK_Forums.Categories.Threads.Languages.Posts_Forums.Categories.Threads.Languages] FOREIGN KEY([DomainId], [ForumId], [CategoryId], [ThreadId], [LanguageId], [CultureId])
    REFERENCES [dbo].[Forums.Categories.Threads.Languages] ([DomainId], [ForumId], [CategoryId], [ThreadId], [LanguageId], [CultureId])
    GO

    ALTER TABLE [dbo].[Forums.Categories.Threads.Languages.Posts] CHECK CONSTRAINT [FK_Forums.Categories.Threads.Languages.Posts_Forums.Categories.Threads.Languages]
    GO

    ALTER TABLE [dbo].[Forums.Categories.Threads.Languages.Posts] ADD  CONSTRAINT [DF_Forums.Categories.Threads.Languages.Posts_DateCreated]  DEFAULT (getdate()) FOR [DateCreated]
    GO




    Any wrong ?
    ANy ideas ?


    Tuesday, December 21, 2010 5:41 PM
  • Alejandro,

    Your key should be stable; it should never change. So I would advice against updating the Primary Key. I don't even think that cascading constraints will help you in this case, because SQL Server is very strict about potential circular references (even if the references are not at all circular).

    One way to achieve these 3 UPDATEs is to replace it with 3 INSERTs and 3 DELETEs (or 2 INSERTs, 1 UPDATE and 2 DELETEs). Something like this:

    INSERT INTO dbo.table1 (CatId, othercolumn1, othercolumn2)
    SELECT @CategoryId, othercolumn1, othercolumn2
    FROM dbo.table3
    WHERE ThreadId = @ThreadId
    
    INSERT INTO dbo.table2 (CatId, othercolumn1, othercolumn2)
    SELECT @CategoryId, othercolumn1, othercolumn2
    FROM dbo.table3
    WHERE ThreadId = @ThreadId
    
    UPDATE dbo.[table3] SET CatId = @CategoryId WHERE ThreadId = @ThreadId
    
    DELETE FROM dbo.table2
    WHERE ThreadId = @ThreadId
    AND CatId <> @CategoryId
    
    DELETE FROM dbo.table1
    WHERE ThreadId = @ThreadId
    AND CatId <> @CategoryId
    

    Another option would be to only update one of the three tables, and code all this stuff in a INSTEAD OF trigger on that table.

     


    Gert-Jan
    • Marked as answer by KJian_ Tuesday, December 28, 2010 7:01 AM
    Tuesday, December 21, 2010 8:41 PM