Effective Procedure for Updating Table Primary Key Values

Introduction

The objectGUID attribute of Active Directory objects is globally unique and will never change. One of my company's solutions has a proprietary db containing a table of AD objects. It reads objects from AD and uses an O/R mapper for db CRUD operations. It made a lot of sense to use the AD objectGUID as PK of the corresponding db table. This made the code cleaner and faster.

One of our customers did a company merger and needed to scrap their AD and re-create objects on another existing AD. Keeping the old domain was not an option. Nor was moving all objects. My company was responsible for the AD migration as well moving all software, access and content to the new domain. Most software has "move user" commands to handle this type of AD domain change. Our software however did not. We needed a clever way to update primary key values.

SQL Scripts to Generate Helper SQL Scripts

To change a PK value, you need to update all FK values referencing it. This is an easy way to achieve it:

1. Drop all FK constraints.
2. Add trigger to cascade update PK value changes to all FK values.
3. Change all PK values.
4. Remove trigger added in 2.
5. Add all FK constraints.

Below are three SQL scripts that will generate SQL scripts to perform 1, 2 and 5. You need to change the table and column name.

If your database has triggers, you may want to turn them off during 3 as this is not ordinary operation. Run this to turn them of:

sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all'

And this to turn them back on:

sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER all'

Important: Make sure the trigger in 2 is not disabled during 3.

If you have tables referencing the PK value without FK constraint, this must be handled by the cascade trigger. The trigger generates script below shows an example of this, see also the script comment.

When running the scripts, set "Results to Text" in SSMS and increase the maximum number of characters displayed in each column to avoid cropping:

-- Use this to generate sql statements for dropping all FK constraints on user table
SELECT 'ALTER TABLE [' + FK.TABLE_NAME + '] DROP CONSTRAINT '+ C.CONSTRAINT_NAME + CHAR(13)
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
Where PK.TABLE_NAME='User'

-- Use this to generate trigger for cascading update of user PK
set nocount on
SELECT 'CREATE TRIGGER [dbo].[trCascadeUpdateUserId]
   ON  [dbo].[User]
   After Update
AS 
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
    declare @userId uniqueidentifier
    declare @PrevUserId uniqueidentifier
    Select @userId = i.UserId From inserted i
    Select @prevUserId = d.UserId From deleted d' + CHAR(13)
SELECT 'UPDATE [' + FK.TABLE_NAME + '] SET [' + _
CU.COLUMN_NAME + '] = @userId where [' + CU.COLUMN_NAME + '] = @prevUserId' + CHAR(13)
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
Where PK.TABLE_NAME='User'
Select '--------------------------—Start updating Revision tables fields not having _
FK--------------------------------------------------------------------------------------------_
----------------------------------------------------------------------------------------------_
----------------------------------------------------------------------------------------------_
--------------------------------------'
SELECT 'UPDATE ['+t.name+'] SET ['+c.name+']=@userId where ['+c.name+']=@prevUserId'+ CHAR(13)
FROM sys.tables AS t
    INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE Upper(c.name ) like '%USERID' and c.name not in (
    SELECT FK_Column = CU.COLUMN_NAME
    FROM    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
            INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
            INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON _
		C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU  ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
            INNER JOIN (SELECT i1.TABLE_NAME,i2.COLUMN_NAME
                        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
                            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 _
				ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
                        WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
                       ) PT
            ON PT.TABLE_NAME = PK.TABLE_NAME
    ) 
    and c.name not in (
        SELECT Col.Column_Name
        FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab _
	inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col 
                on Col.Constraint_Name = Tab.Constraint_Name
        WHERE  Col.Table_Name = Tab.Table_Name AND Constraint_Type = 'PRIMARY KEY'
    )
Order by 1
SELECT 'END TRY    
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT 
            @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE();

            RAISERROR (@ErrorMessage, -- Message text.
                       @ErrorSeverity, -- Severity.
                       @ErrorState -- State.
                       );
    
    END CATCH; 
END'
set nocount off

-- Use this to generate sql statements adding all FKs. It must be run before dropping the constraints
SELECT 'ALTER TABLE [' + FK.TABLE_NAME + '] ADD CONSTRAINT '+ _
C.CONSTRAINT_NAME + ' FOREIGN KEY (' + CU.COLUMN_NAME + ') _
REFERENCES dbo.[User] (UserId) ON UPDATE NO ACTION ON DELETE NO ACTION' + CHAR(13)
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
SELECT i1.TABLE_NAME, i2.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT ON PT.TABLE_NAME = PK.TABLE_NAME
Where PK.TABLE_NAME='User'

-- Use this to drop the cascade trigger
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trCascadeUpdateUserId]'))
DROP TRIGGER [dbo].[trCascadeUpdateUserId]
GO