In the process of setting up replication for a client, one of the first things I did was to check to ensure that all the tables identified for replication had a primary key. I found a number that did not and of those, a good portion had unique indexes defined. I thought simple enough just drop the unique index and define the primary key using the column(s) from the index.
Unfortunately, I ended up getting an error stating that I could not explicitly drop the index(es) as they were being used for foreign key enforcement. As it turned out the unique indexes participated in quite a few foreign key constraints.
I came up with this script to generate the alter table statements to create the foreign keys. I used the recursive CTE to capture any composite keys. If using AdventureWorks, the Sales.SpecialOfferProduct contains a composite key.
; WITH CTE ( constraint_object_id, parent_object_id, referenced_object_id, parent_columns, referenced_columns, constraint_column_id ) AS ( SELECT keys.object_id AS constraint_object_id , keys.parent_object_id , keys.referenced_object_id , CAST('' AS NVARCHAR(4000)) AS parent_columns , CAST('' AS NVARCHAR(4000)) AS referenced_columns , 0 AS constraint_column_id FROM sys.foreign_keys keys UNION ALL SELECT cte.constraint_object_id , cte.parent_object_id , cte.referenced_object_id , cte.parent_columns + ', ' + parent_cols.name , cte.referenced_columns + ', ' + referenced_cols.name , fkc.constraint_column_id FROM CTE cte INNER JOIN sys.foreign_key_columns fkc ON cte.constraint_object_id = fkc.constraint_object_id INNER JOIN sys.columns parent_cols ON fkc.parent_object_id = parent_cols.object_id AND fkc.parent_column_id = parent_cols.column_id INNER JOIN sys.columns referenced_cols ON fkc.referenced_object_id = referenced_cols.object_id AND fkc.referenced_column_id = referenced_cols.column_id WHERE fkc.constraint_column_id = cte.constraint_column_id + 1 ) SELECT 'ALTER TABLE [' + OBJECT_SCHEMA_NAME(c.parent_object_id) + '].[' + OBJECT_NAME(c.parent_object_id) + ']' + CHAR(10) + ' ADD CONSTRAINT [' + OBJECT_NAME(c.constraint_object_id) + ']' + CHAR(10) + ' FOREIGN KEY (' + STUFF(c.parent_columns, 1, 2, '') + ')' + CHAR(10) + ' REFERENCES [' + OBJECT_SCHEMA_NAME(c.referenced_object_id) + '].[' + OBJECT_NAME(c.referenced_object_id) + '](' + STUFF(c.referenced_columns, 1, 2, '') + ')' + CHAR(10) FROM CTE c INNER JOIN ( SELECT constraint_object_id , MAX(constraint_column_id) AS constraint_column_id FROM CTE GROUP BY constraint_object_id ) AS x ON c.constraint_object_id = x.constraint_object_id AND c.constraint_column_id = x.constraint_column_id --WHERE c.referenced_object_id = OBJECT_ID('Sales.SpecialOfferProduct')
If you want to also script the drops for the foreign key constraints, the following will do the trick
SELECT 'IF EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N''[' + OBJECT_SCHEMA_NAME(parent_object_id) + '].[' + name + ']'')' + ' AND parent_object_id = OBJECT_ID(N''[' + OBJECT_SCHEMA_NAME(parent_object_id) + '].[' + OBJECT_NAME(parent_object_id) + ']''))' + CHAR(10) + 'ALTER TABLE [' + OBJECT_SCHEMA_NAME(parent_object_id) + '].[' + OBJECT_NAME(parent_object_id) + ']' + ' DROP CONSTRAINT [' + name + '];' + CHAR(10) + 'GO' + CHAR(10) FROM sys.foreign_keys --WHERE referenced_object_id = OBJECT_ID('Sales.SpecialOfferProduct')
The CHAR(10) forces a new line in the output, so you’ll want to have the results returned as text to keep the formatting.