This project is read-only.
1

Closed

Drop - Recreate Foreign Keys

description

Merge statement in SQL Server 2008, 2008 R2 and 2012 doesn't work for target table if there is foreign key to some other table.

One solution could be to drop all foreign keys before executing MERGE statement and then recreate these foreign keys after successful execution of MERGE statement.
Closed Nov 6, 2015 at 4:38 PM by kamiln
Yes, It's looks like resolved issue.
I tried to find the issue in the network but seems like all important problems with MERGE were resolved.
Here is nice list with MERGE issues and links to Microsoft Connect:
https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/

I close this task.
Anybody who encounters similar problem - please create new issue with detailed reproduce steps.

comments

knyazs wrote Jul 26, 2013 at 7:09 AM

Thank you John Paul for this script (http://sqlblog.com/blogs/john_paul_cook/archive/2009/09/17/script-to-create-all-foreign-keys.aspx)


SCRIPT:

DECLARE @schema_name sysname;
DECLARE @table_name sysname;
DECLARE @constraint_name sysname;
DECLARE @constraint_object_id int;
DECLARE @referenced_object_name sysname;
DECLARE @is_disabled bit;
DECLARE @is_not_for_replication bit;
DECLARE @is_not_trusted bit;
DECLARE @delete_referential_action tinyint;
DECLARE @update_referential_action tinyint;
DECLARE @tsql nvarchar(4000);
DECLARE @tsql2 nvarchar(4000);
DECLARE @fkCol sysname;
DECLARE @pkCol sysname;
DECLARE @col1 bit;
DECLARE @action char(6);

--SET @action = 'DROP';
SET @action = 'CREATE';

DECLARE FKcursor CURSOR FOR
select OBJECT_SCHEMA_NAME(parent_object_id)
     , OBJECT_NAME(parent_object_id), name, OBJECT_NAME(referenced_object_id)
     , object_id
     , is_disabled, is_not_for_replication, is_not_trusted
     , delete_referential_action, update_referential_action
from sys.foreign_keys
order by 1,2;
OPEN FKcursor;

FETCH NEXT FROM FKcursor INTO @schema_name, @table_name, @constraint_name
, @referenced_object_name, @constraint_object_id
, @is_disabled, @is_not_for_replication, @is_not_trusted
, @delete_referential_action, @update_referential_action;
WHILE @@FETCH_STATUS = 0

BEGIN
IF @action <> 'CREATE'
    SET @tsql = 'ALTER TABLE '
              + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name)
              + ' DROP CONSTRAINT ' + QUOTENAME(@constraint_name) + ';';
ELSE
    BEGIN
    SET @tsql = 'ALTER TABLE '
              + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name)
              + CASE @is_not_trusted
                    WHEN 0 THEN ' WITH CHECK '
                    ELSE ' WITH NOCHECK '
                END
              + ' ADD CONSTRAINT ' + QUOTENAME(@constraint_name)
              + ' FOREIGN KEY ('
    SET @tsql2 = '';
    DECLARE ColumnCursor CURSOR FOR
        select COL_NAME(fk.parent_object_id, fkc.parent_column_id)
             , COL_NAME(fk.referenced_object_id, fkc.referenced_column_id)
        from sys.foreign_keys fk
        inner join sys.foreign_key_columns fkc
        on fk.object_id = fkc.constraint_object_id
        where fkc.constraint_object_id = @constraint_object_id
        order by fkc.constraint_column_id;
    OPEN ColumnCursor;

    SET @col1 = 1;

    FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF (@col1 = 1)
            SET @col1 = 0
        ELSE
        BEGIN
            SET @tsql = @tsql + ',';
            SET @tsql2 = @tsql2 + ',';
        END;
        SET @tsql = @tsql + QUOTENAME(@fkCol);
        SET @tsql2 = @tsql2 + QUOTENAME(@pkCol);
        FETCH NEXT FROM ColumnCursor INTO @fkCol, @pkCol;
    END;
    CLOSE ColumnCursor;
    DEALLOCATE ColumnCursor;

    SET @tsql = @tsql + ' ) REFERENCES ' + QUOTENAME(@schema_name) + '.' + QUOTENAME(@referenced_object_name)
              + ' (' + @tsql2 + ')';           

    SET @tsql = @tsql
              + ' ON UPDATE ' + CASE @update_referential_action
                                    WHEN 0 THEN 'NO ACTION '
                                    WHEN 1 THEN 'CASCADE '
                                    WHEN 2 THEN 'SET NULL '
                                    ELSE 'SET DEFAULT '
                                END
              + ' ON DELETE ' + CASE @delete_referential_action
                                    WHEN 0 THEN 'NO ACTION '
                                    WHEN 1 THEN 'CASCADE '
                                    WHEN 2 THEN 'SET NULL '
                                    ELSE 'SET DEFAULT '
                                END
              + CASE @is_not_for_replication
                    WHEN 1 THEN ' NOT FOR REPLICATION '
                    ELSE ''
                END
              + ';';
    END;


PRINT @tsql;
IF @action = 'CREATE'
    BEGIN
    SET @tsql = 'ALTER TABLE '
              + QUOTENAME(@schema_name) + '.' + QUOTENAME(@table_name)
              + CASE @is_disabled
                    WHEN 0 THEN ' CHECK '
                    ELSE ' NOCHECK '
                END
              + 'CONSTRAINT ' + QUOTENAME(@constraint_name)
              + ';';
    PRINT @tsql;
    END;

FETCH NEXT FROM FKcursor INTO @schema_name, @table_name, @constraint_name
    , @referenced_object_name, @constraint_object_id
    , @is_disabled, @is_not_for_replication, @is_not_trusted
    , @delete_referential_action, @update_referential_action;
END;

CLOSE FKcursor;
DEALLOCATE FKcursor;

kamiln wrote Oct 16, 2015 at 11:05 PM

Really? :)
Maybe I didn't understand the scenario very well but IMO merge works with FK in destination table.
Here you are example:
USE [AdventureWorks2012]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [Person].[Address2](
    [AddressID] [int] NOT NULL,
    [AddressLine1] [nvarchar](60) NOT NULL,
    [AddressLine2] [nvarchar](60) NULL,
    [City] [nvarchar](30) NOT NULL,
    [StateProvinceID] [int] NOT NULL,
    [PostalCode] [nvarchar](15) NOT NULL,
    [SpatialLocation] [geography] NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL ,
    [ModifiedDate] [datetime] NOT NULL ,
 CONSTRAINT [PK_Address2_AddressID] PRIMARY KEY CLUSTERED 
(
    [AddressID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [Person].[Address2]  WITH CHECK ADD  CONSTRAINT [FK_Address2_StateProvince_StateProvinceID] FOREIGN KEY([StateProvinceID])
REFERENCES [Person].[StateProvince] ([StateProvinceID])
GO

ALTER TABLE [Person].[Address2] CHECK CONSTRAINT [FK_Address2_StateProvince_StateProvinceID]
GO
And MERGE statement for above table:
MERGE [Person].[Address2] as [target]
USING
(
    SELECT
        [AddressID],
        [AddressLine1],
        [AddressLine2],
        [City],
        [ModifiedDate],
        [PostalCode],
        [rowguid],
        [SpatialLocation],
        [StateProvinceID]
    FROM [Person].[Address]
) as [source]
ON
(
    [source].[AddressID] = [target].[AddressID]
)

WHEN MATCHED THEN UPDATE
SET
    [target].[AddressLine1] = [source].[AddressLine1],
    [target].[AddressLine2] = [source].[AddressLine2],
    [target].[City] = [source].[City],
    [target].[ModifiedDate] = [source].[ModifiedDate],
    [target].[PostalCode] = [source].[PostalCode],
    [target].[rowguid] = [source].[rowguid],
    [target].[SpatialLocation] = [source].[SpatialLocation],
    [target].[StateProvinceID] = [source].[StateProvinceID]

WHEN NOT MATCHED BY TARGET
THEN INSERT
(

    [AddressID],
    [AddressLine1],
    [AddressLine2],
    [City],
    [ModifiedDate],
    [PostalCode],
    [rowguid],
    [SpatialLocation],
    [StateProvinceID]
)
VALUES
(
    [source].[AddressID],
    [source].[AddressLine1],
    [source].[AddressLine2],
    [source].[City],
    [source].[ModifiedDate],
    [source].[PostalCode],
    [source].[rowguid],
    [source].[SpatialLocation],
    [source].[StateProvinceID]
);
Where is the real problem?

knyazs wrote Oct 30, 2015 at 6:26 AM

Kamiln,

At the time, this was an issue as MERGE didn't work as expected - it was ignoring referential integrity. Maybe in later versions Microsoft fixed this?

wrote Nov 6, 2015 at 4:38 PM