dropping duplicate foreign keys

Question

I have about 100 tables where all of them have duplicate foreign key constraints on them.

Is there a way that I could get rid of it? Is there a query that could give me all duplicate keys in it?

Answer 1

This removes the most recently created duplicates

;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

Answer 2

I'm using this T-SQL script here to detect potentially duplicate FK constraints - and it also produces the necessary ALTER TABLE...DROP CONSTRAINT statements in the last of the output columns.

You cannot reliably automatically detect and pick which of multiple FK constraints to drop - so you're basically left to detect them and then manually pick which ones to drop (using that drop statement produced 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