Sync Database when tables have Foreign Keys

Question

One of our SQL Databases is a sync of somebody else's Database. There are about 100 tables in their database, and about 120 in ours.

In ours, we have a few additional tables that help us do mapping and track other data. As a part of our additional tables, we create Foreign Keys onto some of the DBO Tables that are Sync'd from our provider.

Therefore, a Customers table that is sync'd from our Provider now has Foreign Key constraints on a mySchema.Receipts table, which has a CustomerID field.

The Provider's Sync broke, and they said that they cannot Drop the Customers table in order to Sync the data, because it now says there is a Foriegn key against Customers.

Isn't there a way I can tell them in SQL that SQL can like... ignore Foriegn Key constraints when Sync'ing or something?

You can disable a foreign key constraint and then re-enable it after the synch is complete. But if you have invalid data the constraint will raise an error. — Sean Lange, Feb 23 '17 at 17:22
You can disable constraints and then re-enabled them. http://stackoverflow.com/questions/159038/how-can-foreign-key-constraints-be-temporarily-disabled-using-t-sql — Rick S, Feb 23 '17 at 17:22
Are you asking about SQL Server's replication? It enables/disables/checks the replicated constraints (including foreign keys) automatically. You can also specify that a constraing won't be used during replication. Check [All about “Not for Replication”](https://blogs.msdn.microsoft.com/repltalk/2010/02/22/all-about-not-for-replication/) — Panagiotis Kanavos, Feb 23 '17 at 18:04

SteveD · Accepted Answer · 2017-02-24T15:53:34.973

If the sync job is deleting data from the target database, you can disable constraints, and then enable them again after the sync. If your sync job is truncating tables or drop and rebuilding tables, them the foreign key constraints need to be dropped, then rebuilt again after the sync.

Using the "delete from target" approach:

If target tables are part of the "dbo" schema, your process could look like:

(1) Disable all foreign keys constrains in target database.
Example:

exec sp_MSforeachtable 'IF ''?'' like ''%[dbo]%'' ALTER TABLE ? NOCHECK CONSTRAINT ALL;'

(2) Delete all data from the target tables

(3) Copy all data from the source to the target tables, including identity column data if needed.

(4) Restore all foreign keys constrains in target database.
Example:

exec sp_MSforeachtable 'IF ''?'' like ''%[dbo]%'' ALTER TABLE ? CHECK CONSTRAINT ALL;'

** To help further automate this, take advantage of the INFORMATION_SCHEMA.TABLES view in SQL Server.

SELECT 'dbo.' + TABLE_NAME  as TableName, 'SELECT * FROM dbo.['+ TABLE_NAME +'];' AS Script FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' and TABLE_NAME NOT IN (comma delimited list of tables to exclude)

If needed, the below script will query all of the foreign key constraints:

IF OBJECT_ID('tempdb..#TEMPCONS') IS NOT NULL DROP TABLE #TEMPCONS;

SELECT KCU1.TABLE_SCHEMA, KCU1.TABLE_NAME, KCU1.CONSTRAINT_NAME, KCU1.COLUMN_NAME, KCU1.ORDINAL_POSITION, RC.UPDATE_RULE, RC.DELETE_RULE,
    KCU2.TABLE_NAME AS REF_TABLE_NAME, KCU2.CONSTRAINT_NAME AS REF_CONSTRAINT_NAME, KCU2.COLUMN_NAME AS REF_COLUMN_NAME, KCU2.ORDINAL_POSITION AS REF_ORDINAL_POSITION,
    KCU2.TABLE_SCHEMA AS REF_TABLE_SCHEMA   INTO #TEMPCONS
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC INNER JOIN
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1 ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG AND 
        KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME INNER JOIN
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG AND 
        KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME AND 
        KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION
ORDER BY TABLE_NAME, CONSTRAINT_NAME, ORDINAL_POSITION;

ALTER TABLE #TEMPCONS ADD COLUMN_LIST VARCHAR(MAX), REF_COLUMN_LIST VARCHAR(MAX);

-- Rows to column concatenation
DECLARE @COLUMN_NAME varchar(MAX), @Columns VARCHAR(MAX);
UPDATE #TEMPCONS SET @Columns = COLUMN_LIST = COALESCE(CASE COALESCE(@COLUMN_NAME, '') 
      WHEN CONSTRAINT_NAME THEN @Columns + ', ' + '['+ COLUMN_NAME +']' ELSE '['+ COLUMN_NAME +']' END, ''), @COLUMN_NAME = CONSTRAINT_NAME;

UPDATE #TEMPCONS SET @Columns = REF_COLUMN_LIST = COALESCE( CASE COALESCE(@COLUMN_NAME, '') 
      WHEN CONSTRAINT_NAME THEN @Columns + ', ' + '['+REF_COLUMN_NAME +']' ELSE '['+REF_COLUMN_NAME +']' END, ''), @COLUMN_NAME = CONSTRAINT_NAME;

SELECT TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME, UPDATE_RULE, DELETE_RULE, MAX(COLUMN_LIST) AS COLUMN_LIST, 
REF_TABLE_SCHEMA, REF_TABLE_NAME, REF_CONSTRAINT_NAME, MAX(REF_COLUMN_LIST) AS REF_COLUMN_LIST
FROM #TEMPCONS
GROUP BY REF_TABLE_SCHEMA, TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME, UPDATE_RULE, DELETE_RULE, REF_TABLE_NAME, REF_CONSTRAINT_NAME;

Setting the keys to NOCHECK doesn't allow me to `DROP TABLE Customers`. Is there a way I can do this where I can save the Foreign Keys dynamically, delete the real ones, Drop the table, Sync the table, then restore the saved Foreign Keys? — Suamere, Feb 23 '17 at 19:36
You would need to script the foreign key constraints, run the sync, then build them from the script. I edited my answer to provide a query that will list all of foreign key data. These values can be used to construct the "constraint" scripts. — SteveD, Feb 23 '17 at 19:50
That's awesome. Can you tell me what `REF_CONSTRAINT_NAME` might be used for? I created a script to format a Foreign Key creator based on those variables and I use them all except `REF_CONSTRAINT_NAME`. That data seems to be a reference to the Primary Key Constraint that is on the `REFERENCES` Column. But I don't know when you'd use that data to help create a Foreign Key. — Suamere, Feb 23 '17 at 22:34
REF_CONSTRAINT_NAME is not needed to script out your RI, it could be useful if you were comparing the database schemas before running a sync. — SteveD, Feb 24 '17 at 16:13

Sync Database when tables have Foreign Keys

1 Answers1