Replicate fails Reload - due to Foreign key constraints on target, even though they are disabled!

[RonFusionHSLLC]

I got the dreaded when trying to do a truncate/load replication.

Handling new table 'dbo'.'Axxxx' failed
Failed to truncate table dbo.Axxxx
RetCode: SQL_ERROR SqlState: 42000 NativeError: 4712 Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot truncate table 'dbo.Axxxx' because it is being referenced by a FOREIGN KEY constraint. Line: 1 Column: -1
Failed (retcode -1) to execute statement: 'TRUNCATE TABLE [dbo].[Axxxx]'

The issue is that the constraints are all disabled on the table and will allow a truncate table command.

Besides manually truncating the target tables, and reload with a 'do nothing', what are my options.

Is there a way to have Replicate check to see if the constraints are enabled/disabled, and act accordingly.

I really don't want to drop all the constraints and then have to rebuild them all.

Ron

[Michael_Litz]

Hi @RonFusionHSLLC 

I see you say the FK are disabled on that table - is it possible there is a FK on another table that references this table?

Thanks,
Michael

[RonFusionHSLLC]

No, all constraints on all tables are disabled.

[Michael_Litz]

Hi @RonFusionHSLLC 

Okay, that is strange cause the task would only report what SQL server reports back to us. I would not think we would have issue with a constraint that is disabled.

Have you been able to truncate the table manually from outside of replicate?

Thanks,
Michael

[Michael_Litz]

Hi @RonFusionHSLLC 

You probably ran something like this on the target table, what did it return?

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME='Axxxx';

[RonFusionHSLLC]

Never mind...it's Sql Server - it doesn't work like my beloved Oracle.

Even if the constraints are disabled, SS will not allow truncate!

[Michael_Litz]

Hi @RonFusionHSLLC ,

Okay, good job to figure it out. I would guess that you need to script  the create and then the drop constraints.

One thing to note is if you are running the task and that target does have foreign keys the task may not adhere to the proper transactional order to comply with them. Especially in batch apply mode, as the incoming transactions are grouped and sent to the target in a different order then they are received.

If you really need the target side FK then you will most likely need to run in transactional apply mode which is going to be slower and may still break a FK constraint.

Your best bet would be to remove all FK and run in batch apply mode.

Thanks,
Michael

[RonFusionHSLLC]

I'll script a drop and add constraint sp. This is a test copy of a dB for transactional system. We refresh it infrequently. We were using backup and restore, onetime replication looked like a great option...still does just need to add two steps. Thanks for advice.

[RonFusionHSLLC · Accepted solution]

Thought I'd share the script, for anyone else having this issue.

This needs to be broken up into the delete part and then the add part .

I had ChatGpt script the basis for this, if you are not using ChatGpt for mondain tasks...look into it, I added a few lines to make it workable.

DECLARE @ForeignKeys TABLE
(
TableName NVARCHAR(255),
ForeignKeyName NVARCHAR(255),
ForeignKeyText NVARCHAR(MAX)
)

-- Insert all foreign key constraints on user tables into the @ForeignKeys table variable
INSERT INTO @ForeignKeys (TableName, ForeignKeyName, ForeignKeyText)
SELECT t.name AS TableName, fk.name AS ForeignKeyName,
' FOREIGN KEY (' + c.name + ') REFERENCES ' + OBJECT_SCHEMA_NAME(fk.referenced_object_id) + '.' + OBJECT_NAME(fk.referenced_object_id) + ' (' + rc.name + ')'
FROM sys.tables t
JOIN sys.foreign_keys fk ON t.object_id = fk.parent_object_id
JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
JOIN sys.columns c ON fkc.parent_column_id = c.column_id AND fkc.parent_object_id = c.object_id
JOIN sys.columns rc ON fkc.referenced_column_id = rc.column_id AND fkc.referenced_object_id = rc.object_id
WHERE t.is_ms_shipped = 0

-- Disable all foreign key constraints in the database
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

-- Drop all foreign key constraints in the database
DECLARE @TableName NVARCHAR(255), @ForeignKeyName NVARCHAR(255), @ForeignKeyText NVARCHAR(MAX)
DECLARE foreignkeys_cursor CURSOR FOR SELECT TableName, ForeignKeyName, ForeignKeyText FROM @ForeignKeys
OPEN foreignkeys_cursor
FETCH NEXT FROM foreignkeys_cursor INTO @TableName, @ForeignKeyName, @ForeignKeyText
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('ALTER TABLE [' + @TableName + '] DROP CONSTRAINT [' + @ForeignKeyName + ']')
FETCH NEXT FROM foreignkeys_cursor INTO @TableName, @ForeignKeyName, @ForeignKeyText
END
CLOSE foreignkeys_cursor
DEALLOCATE foreignkeys_cursor

-- save off the fk's

IF OBJECT_ID('dbo.fk_table') IS NOT NULL DROP TABLE dbo.fk_table;

select * into dbo.fk_table from @ForeignKeys

---- ---------------------------------------------split here into 2 sp's

--- run the replication

--------------------------------------------------now put the FK's back

DECLARE
@TableName NVARCHAR(255),
@ForeignKeyName NVARCHAR(255),
@ForeignKeyText NVARCHAR(MAX)

-- Add back all stored foreign key constraints
DECLARE foreignkeys_cursor CURSOR FOR SELECT TableName, ForeignKeyName, ForeignKeyText FROM dbo.fk_table
OPEN foreignkeys_cursor
FETCH NEXT FROM foreignkeys_cursor INTO @TableName, @ForeignKeyName, @ForeignKeyText
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ForeignKeyText = REPLACE(@ForeignKeyText, @TableName, '[' + @TableName + ']')
EXEC('ALTER TABLE [' + @TableName + '] ADD CONSTRAINT [' + @ForeignKeyName + '] ' + @ForeignKeyText)
FETCH NEXT FROM foreignkeys_cursor INTO @TableName, @ForeignKeyName, @ForeignKeyText
END
CLOSE foreignkeys_cursor
DEALLOCATE foreignkeys_cursor

[Michael_Litz]

Hi @RonFusionHSLLC ,

Thank you for sharing the script. Also the tip on ChatGpt.

Looks like a good solution to your use case with replicate.

Thanks,
Michael