Removing duplicate foreign keys

Question

I have about 100 tables where all of them have duplicate foreign keys.

Is there any way to get rid of it? Is there a request that can give me all duplicate keys?

Answer 1

I use this T-SQL script here to detect potentially duplicate FK constraints - and it also creates the necessary ALTER TABLE...DROP CONSTRAINT in the last column of the output.

You cannot reliably automatically detect and select which of the several FK constraints should be discarded - therefore, you basically need to detect them, and then manually select which ones should be removed (using this reset operator created by my query).

 ;WITH FKData AS ( SELECT fk.parent_object_id, fkc.parent_column_id, fk.referenced_object_id, fkc.referenced_column_id, FKCount = COUNT(*) FROM sys.foreign_keys fk INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id GROUP BY fk.parent_object_id, fkc.parent_column_id, fk.referenced_object_id, fkc.referenced_column_id HAVING COUNT(*) > 1 ), DuplicateFK AS ( SELECT FKName = fk.Name, ParentSchema = s1.Name, ParentTable = t1.Name, ParentColumn = c1.Name, ReferencedTable = t2.Name, ReferencedColumn = c2.Name FROM sys.foreign_keys fk INNER JOIN sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id INNER JOIN FKData f ON fk.parent_object_id = f.parent_object_id AND fk.referenced_object_id = f.referenced_object_id AND fkc.parent_column_id = f.parent_column_id AND fkc.referenced_column_id = f.referenced_column_id INNER JOIN sys.tables t1 ON f.parent_object_id = t1.object_id INNER JOIN sys.columns c1 ON f.parent_object_id = c1.object_id AND f.parent_column_id = c1.column_id INNER JOIN sys.schemas s1 ON t1.schema_id = s1.schema_id INNER JOIN sys.tables t2 ON f.referenced_object_id = t2.object_id INNER JOIN sys.columns c2 ON f.referenced_object_id = c2.object_id AND f.referenced_column_id = c2.column_id ) SELECT FKName, ParentSchema, ParentTable, ParentColumn, ReferencedTable, ReferencedColumn, DropStmt = 'ALTER TABLE ' + ParentSchema + '.' + ParentTable + ' DROP CONSTRAINT ' + FKName FROM DuplicateFK 

Answer 2

For 100 tables, this is not an option, but if you have only a few tables, create a diagram in SQL Server Management Studio, add your tables there and delete them visually.

Answer 3

This removes the last duplicates created.

 ;WITH fkeys AS ( SELECT f.object_id , f.name , f.parent_object_id, ROW_NUMBER() OVER(PARTITION BY t.column_names ORDER BY f.create_date,f.[object_id]) AS RowNum FROM sys.foreign_keys f CROSS APPLY ( SELECT fc.parent_object_id,parent_column_id,fc.referenced_object_id ,fc.referenced_column_id FROM sys.foreign_key_columns fc WHERE fc.constraint_object_id = f.object_id ORDER BY constraint_column_id FOR XML PATH('') ) t ( column_names ) ) SELECT 'ALTER TABLE '+QUOTENAME(OBJECT_SCHEMA_NAME(f.parent_object_id)) + '.'+QUOTENAME(OBJECT_NAME(f.parent_object_id)) +' DROP CONSTRAINT '+QUOTENAME(f.name)+';' AS DropStatement FROM fkeys f WHERE f.RowNum >= 2