Comments (0)

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.

Comments (0)

Create a list of tables that do not have a primary key defined.

SELECT	TABLE_SCHEMA, TABLE_NAME
FROM	INFORMATION_SCHEMA.TABLES AS t
WHERE	t.TABLE_TYPE = 'BASE TABLE'
		AND NOT EXISTS
		(
			SELECT *
			FROM	INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
			WHERE	t.TABLE_SCHEMA = tc.TABLE_SCHEMA
					AND t.TABLE_NAME = tc.TABLE_NAME
					AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
		)
ORDER BY TABLE_SCHEMA, TABLE_NAME;
GO