Drop and recreate foreign key referencing multiple columns

Question

I am trying to refresh a development server with data from production server but this cannot be done by a straightforward restore as the dev environment contains objects not in prod environment that we need to keep. So what I am trying to do is truncate the tables that need refreshing and then insert the data. I have created a procedure just for that and it works till we get to foreign keys.

The DB I inherited is not very well written and has several primary key columns in most tables. I was looking at this question Cannot truncate table because it is being referenced by a FOREIGN KEY constraint? and have incorporated the best solution provided by @peter-szanto and @marc-2377 into the stored procedure that works GREAT as long as there is one unique constraint in the sys.foreign_key_columns inserted into #FKs. Using the script causes error

"There are no primary or candidate keys in the referenced table"

on some tables due them referencing tables with multiple primary key columns as one foreign key.

The code as is returns these to recreate the fks in table FK_stmnt

ALTER TABLE [dbo].[sometable] WITH NOCHECK 
    ADD CONSTRAINT [sometable$othertable__12345] 
        FOREIGN KEY([fk1]) REFERENCES [dbo].[othertable] ([pk1])

ALTER TABLE [dbo].[sometable] WITH NOCHECK 
    ADD CONSTRAINT [sometable$othertable__12345] 
        FOREIGN KEY([fk2]) REFERENCES [dbo].[othertable] ([pk2])

ALTER TABLE [dbo].[sometable] WITH NOCHECK 
    ADD CONSTRAINT [sometable$othertable__12345] 
        FOREIGN KEY([fk3]) REFERENCES [dbo].[othertable] ([pk3])

ALTER TABLE [dbo].[sometable] WITH NOCHECK 
    ADD CONSTRAINT [sometable$othertable__12345] 
        FOREIGN KEY([fk4]) REFERENCES [dbo].[othertable] ([pk4])

ALTER TABLE [dbo].[sometable] WITH NOCHECK 
    ADD CONSTRAINT [sometable$othertable__12345] 
        FOREIGN KEY([fk5]) REFERENCES [dbo].[othertable] ([pk5])

Need results returned as

ALTER TABLE [dbo].[sometable] WITH NOCHECK 
    ADD CONSTRAINT [sometable$othertable__12345] 
        FOREIGN KEY ([fk1], [fk2], [fk3], [fk4], [fk5]) 
            REFERENCES [dbo].[othertable] ([pk1], [pk2], [pk3], [pk4], [pk5])

that is for every sometable$othertable__12345 constraint combine the columns in one statement.

Any ideas please?

*Composite keys* (including primary keys) are OK if they address the business requirements. — The Impaler, Sep 13 '19 at 18:26
Yes are OK, IF they address the business requirements. But this is literally a colossal nightmare of a database with repeated data everywhere, hence the composite keys on almost every table with fk — Amommy, Sep 13 '19 at 18:34

pmbAustin · Accepted Answer · 2019-09-13T20:40:01.880

0

The following SQL will list all FKs, the table name, the FK key(s), the referenced table, and the referenced key(s) … if there is more than one key, it's a comma separated list.

This is just SQL from my stock toolkit, not really tailored to your exact problem, but you should be able to adapt it for your use to generate what you need.

 SELECT fkeys.[name] AS FKName, 
        OBJECT_NAME(fkeys.parent_object_id) AS TableName,
        (SELECT STUFF((SELECT ',' + c.[name]
         FROM sys.foreign_keys fk INNER JOIN sys.tables t ON fk.parent_object_id = t.object_id
              INNER JOIN sys.columns as c ON t.object_id = c.object_id
              INNER JOIN sys.foreign_key_columns AS fc ON c.column_id = fc.parent_column_id 
                                                      AND fc.constraint_object_id = fk.object_id 
                                                      AND fc.parent_object_id = fk.parent_object_id 
        WHERE fk.[name] = fkeys.[name]
        FOR XML PATH ('')), 1, 1, '')) AS FKFolumns,
        OBJECT_NAME(fkeys.referenced_object_id) AS ReferencedTableName,
        (SELECT STUFF((SELECT ',' + c.[name]
         FROM sys.foreign_keys fk INNER JOIN sys.tables t ON fk.referenced_object_id = t.object_id
              INNER JOIN sys.columns as c ON t.object_id = c.object_id
              INNER JOIN sys.foreign_key_columns AS fc ON c.column_id = fc.referenced_column_id 
                                                      AND fc.constraint_object_id = fk.object_id 
                                                      AND fc.referenced_object_id = fk.referenced_object_id 
        WHERE fk.[name] = fkeys.[name]
        FOR XML PATH ('')), 1, 1, '')) AS ReferencedFKFolumns
   FROM sys.foreign_keys fkeys
  ORDER BY FKName;

edited Sep 13 '19 at 20:40

answered Sep 13 '19 at 18:31

pmbAustin

3,890
1
22
33

`AND c.column_id = fc.parent_column_id` this fails for me as fc.parent_column_id sometimes is not the same as column id in parent: `T(u,obj); Pairs(u,o1,o2, fk1 (u,o1) ref T(u,obj), fk2(u,o2) ref T(u,obj)` Here 3 cols in `Pairs` reference 2 cols in `T`. – Serg Sep 13 '19 at 18:59
I'm not sure I understand... how can one FK relate 3 columns to 2 columns? It can't. They have to match. – pmbAustin Sep 13 '19 at 19:07
Yes they can there are 2 FKs, see example in the comment, it's legal sql. – Serg Sep 13 '19 at 19:10
Oh, I think I see what you're saying. There's a copy paste error in my version. It should be referenced_column_id. I'll update the answer. – pmbAustin Sep 13 '19 at 19:28
This looks usable but for some reason out the box returns lots of nulls in ReferencedFKFolumns randomly it seems. – Amommy Sep 13 '19 at 20:11
@Amommy My apologies. I tried to adapt my toolkit version for you here and made more than one copy/paste error in so doing. I've updated it again. Please give it another try. It should work for you now. – pmbAustin Sep 13 '19 at 20:40
Did you get my update working correctly for you @Amommy – pmbAustin Sep 18 '19 at 16:06
Yes thanks. I had actually found the solution the other day and was going to update here. This is an old question with a recently updated solution by @db2 that works great as well :) https://dba.stackexchange.com/questions/31720/find-the-foreign-keys-associated-with-a-given-primary-key I like the way you stuck with just the column and key tables for the needed information. Thanks – Amommy Sep 18 '19 at 19:03

score 0 · Answer 2 · answered Sep 13 '19 at 18:47

0

To have multiple columns in one table have fk relationship to another table's multiple columns this syntax works:

alter table dbo.x
    add constraint FK_A foreign key ([SalesOrderID] ,[SalesOrderDetailID])
        references dbo.y1 ([SalesOrderID] , [SalesOrderDetailID])

In table dbo.y1 the two columns are a primary key on dbo.y1.

answered Sep 13 '19 at 18:47

benjamin moskovits

5,261
1
12
22

The question was how to get the FK const already in the table not how to create them. Thanks – Amommy Sep 18 '19 at 19:05

Drop and recreate foreign key referencing multiple columns

2 Answers2