How to change a primary key that foreign keys already point at

You try to move a table's primary key onto a different column and get:

Msg 3725, Level 16, State 1
The constraint 'PK_Customer' is being referenced by table 'Order', foreign key constraint
'FK_Order_Customer'.
Msg 3727, Level 16, State 1
Could not drop constraint. See previous errors.

A foreign key is a promise that every value in the child column exists exactly once in the parent, and the primary key is what makes that true. Drop the key and the promise has nothing holding it up, so SQL Server refuses while any foreign key still points at it.

The message names one constraint. If three tables point at the key you will see this three times, one drop at a time.

A unique index does not spare you the drop

The intuition is that adding a unique index on the same column gives SQL Server another guarantee to lean on, so the key can then go. It does not work:

CREATE UNIQUE INDEX UQ_Customer_CustomerCode ON dbo.Customer(CustomerCode);

ALTER TABLE dbo.Customer DROP CONSTRAINT PK_Customer;
-- Msg 3725 again, word for word

The foreign key is bound to the constraint it was created against, not to whatever happens to be unique at the time. So the order of work is fixed:

1. Script the foreign keys that point at the key, so you can put them back.
2. Drop those foreign keys.
3. Drop the primary key.
4. Add the new primary key.
5. Recreate the foreign keys.

Step 1 is the one people skip and the only one that cannot be recovered from.

Find what points at the key

The constraints live on the other tables, which is why reading your table's definition does not show them:

SELECT
    fk.name AS foreign_key,
    OBJECT_SCHEMA_NAME(fk.parent_object_id) + '.' +
    OBJECT_NAME(fk.parent_object_id) AS referencing_table
FROM sys.foreign_keys AS fk
WHERE fk.referenced_object_id = OBJECT_ID(N'dbo.Customer');

Script them before you drop them

Recreating a foreign key from memory means getting its columns, its name and its referential actions right. Get the ON DELETE CASCADE wrong and you have either a delete that fails forever or one that quietly removes rows nobody asked it to. Generate the statements instead, and keep the output:

SELECT
    'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id)) + '.' +
    QUOTENAME(OBJECT_NAME(fk.parent_object_id)) +
    ' ADD CONSTRAINT ' + QUOTENAME(fk.name) + ' FOREIGN KEY (' +
    STUFF((SELECT ', ' + QUOTENAME(pc.name)
           FROM sys.foreign_key_columns fkc
           JOIN sys.columns pc ON pc.object_id = fkc.parent_object_id
                              AND pc.column_id = fkc.parent_column_id
           WHERE fkc.constraint_object_id = fk.object_id
           ORDER BY fkc.constraint_column_id
           FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 2, '') +
    ') REFERENCES ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.referenced_object_id)) + '.' +
    QUOTENAME(OBJECT_NAME(fk.referenced_object_id)) + ' (' +
    STUFF((SELECT ', ' + QUOTENAME(rc.name)
           FROM sys.foreign_key_columns fkc
           JOIN sys.columns rc ON rc.object_id = fkc.referenced_object_id
                              AND rc.column_id = fkc.referenced_column_id
           WHERE fkc.constraint_object_id = fk.object_id
           ORDER BY fkc.constraint_column_id
           FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 2, '') +
    ')' +
    CASE fk.delete_referential_action
        WHEN 1 THEN ' ON DELETE CASCADE'
        WHEN 2 THEN ' ON DELETE SET NULL'
        WHEN 3 THEN ' ON DELETE SET DEFAULT' ELSE '' END +
    CASE fk.update_referential_action
        WHEN 1 THEN ' ON UPDATE CASCADE'
        WHEN 2 THEN ' ON UPDATE SET NULL'
        WHEN 3 THEN ' ON UPDATE SET DEFAULT' ELSE '' END +
    ';' AS recreate_statement
FROM sys.foreign_keys AS fk
WHERE fk.referenced_object_id = OBJECT_ID(N'dbo.Customer');

Each row comes back ready to run:

ALTER TABLE [dbo].[Order] ADD CONSTRAINT [FK_Order_Customer]
    FOREIGN KEY ([CustomerCode]) REFERENCES [dbo].[Customer] ([CustomerCode]);

Swapping ADD CONSTRAINT for DROP CONSTRAINT in the same query gives you step 2:

SELECT 'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(fk.parent_object_id)) + '.' +
       QUOTENAME(OBJECT_NAME(fk.parent_object_id)) +
       ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';' AS drop_statement
FROM sys.foreign_keys AS fk
WHERE fk.referenced_object_id = OBJECT_ID(N'dbo.Customer');

Run both before you change anything, and put the recreate output somewhere you will still have it tomorrow. It is the only record of what the keys were.

If the first query returns an error about QUOTED_IDENTIFIER rather than any rows, the tool you are running it in has that option off. SET QUOTED_IDENTIFIER ON; at the top fixes it.

Check the new key's column first

A primary key column must have no duplicates and no empty values. Both failures arrive after the old key is already gone, which is the worst moment to discover them:

SELECT COUNT(*) AS rows_total,
       COUNT(DISTINCT CustomerId) AS distinct_values,
       SUM(CASE WHEN CustomerId IS NULL THEN 1 ELSE 0 END) AS empty_values
FROM dbo.Customer;

All three numbers have to agree: distinct_values equal to rows_total, and empty_values zero. When they do not, this names the values to fix:

SELECT CustomerId, COUNT(*) AS rows_with_this_value
FROM dbo.Customer
GROUP BY CustomerId
HAVING COUNT(*) > 1;

Skip the check and you get one of these. Duplicates:

Msg 1505, Level 16, State 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object
name 'dbo.Customer' and the index name 'PK_Customer'. The duplicate key value is (AAA).

That says CREATE UNIQUE INDEX even though you wrote ALTER TABLE … ADD CONSTRAINT, because a key is enforced by an index underneath. Usefully, it names the first duplicate value it found.

An empty value:

Msg 8111, Level 16, State 1
Cannot define PRIMARY KEY constraint on nullable column in table 'Customer'.

The column has to be NOT NULL before it can be in a key, and making it so is its own job once the table has rows.

If the new key has more than one column

A foreign key must name every column of the key it points at, in the key's order. Anything else is refused:

-- the key is (Country, SiteCode); this names one of the two
ALTER TABLE dbo.Asset ADD CONSTRAINT FK_Asset_Site
    FOREIGN KEY (SiteCode) REFERENCES dbo.Site(SiteCode);

Msg 1776, Level 16, State 1
There are no primary or candidate keys in the referenced table 'dbo.Site' that match the
referencing column list in the foreign key 'FK_Asset_Site'.

The same message appears for naming both columns in the wrong order. So widening a key from one column to two means every table pointing at it needs a second column added and filled before its foreign key can go back — which is usually the real size of the job, and worth knowing before you drop anything.

The keys you put back need not point at the primary key

A foreign key can reference any set of columns carrying a unique index, not only the primary key. That is what lets you move the key without rewriting the children:

-- keep the old column unique, so the old relationships are still legal
CREATE UNIQUE INDEX UQ_Customer_CustomerCode ON dbo.Customer(CustomerCode);

ALTER TABLE dbo.[Order]  DROP CONSTRAINT FK_Order_Customer;
ALTER TABLE dbo.Customer DROP CONSTRAINT PK_Customer;
ALTER TABLE dbo.Customer ADD  CONSTRAINT PK_Customer PRIMARY KEY (CustomerId);

-- points at the unique index now, and enforces exactly as before
ALTER TABLE dbo.[Order] ADD CONSTRAINT FK_Order_Customer
    FOREIGN KEY (CustomerCode) REFERENCES dbo.Customer(CustomerCode);

The children keep the column they already have, full of values that already match. You still drop and recreate each foreign key, but nothing has to be backfilled and no child table changes shape.

To confirm what a key is actually resting on:

SELECT fk.name AS foreign_key, i.name AS points_at, i.is_primary_key
FROM sys.foreign_keys AS fk
JOIN sys.indexes AS i ON i.object_id = fk.referenced_object_id
                     AND i.index_id = fk.key_index_id;

is_primary_key of 0 means that relationship survives the primary key moving again.

Before you run it

• Keep the recreate script. A foreign key you forget to put back is not an error. It is a rule that has stopped being enforced, and you find out when bad data turns up months later.
• Wrap it in one transaction. Half applied, the table has no key and the children have no relationships.
• Count the keys afterwards against the list you generated at the start. The same query, run again, should return the same rows.

Seeing the whole job before you start

In WoodFireERD, untick PK against the old column and you are asked Change the primary key? before anything changes, with the relationships it affects named. There are two answers: Keep CustomerCode unique, which adds the unique index and keeps every relationship pointing at that column, or Let CustomerCode repeat, which removes them. Tick PK against the new column and the Primary key section lists the key in order, with its Constraint name there to edit.

Pending changes then writes the migration script in the order SQL Server needs — the drops, the key, the keys put back. For anything it cannot do for you, such as filling a new key column that is not an identity, it says so as a step rather than generating a statement that would fail. If you untick a key and change your mind, Restore 1 relationship appears above the editor and puts back what unticking removed.

---

*Originally published at woodfireerd.com.