DEV Community: Sam

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

Sam — Tue, 13 Oct 2026 04:07:04 +0000

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:

Script the foreign keys that point at the key, so you can put them back.
Drop those foreign keys.
Drop the primary key.
Add the new primary key.
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.

ALTER COLUMN failed because one or more objects access this column

Sam — Tue, 06 Oct 2026 10:44:40 +0000

You change a column's type, and SQL Server refuses:

ALTER TABLE dbo.Product ALTER COLUMN Code nvarchar(20) NOT NULL;

Msg 5074, Level 16, State 1, Line 1
The object 'DF_Product_Code' is dependent on column 'Code'.
Msg 5074, Level 16, State 1, Line 1
The object 'ProductCode' is dependent on column 'Code'.
Msg 5074, Level 16, State 1, Line 1
The index 'IX_Product_Code' is dependent on column 'Code'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN Code failed because one or more objects access this column.

The useful part is the 5074s above the 4922, one per thing in the way. Read those and you have your list. The trouble is that a deployment tool often shows only the last message, and 4922 on its own names nothing at all.

What counts as being in the way

An index that has the column as a key or an included column
A default constraint on the column
A check constraint that mentions the column, even one on a different column
A view or function created WITH SCHEMABINDING
A computed column whose expression uses it
A statistics object created by hand on the column

A foreign key is not on that list, and neither is a primary key on another column. Both can still block the change for their own reasons, but they are not what 4922 is about.

Not every change is blocked

Making a string longer is not a type change, and an index does not stop it:

CREATE TABLE dbo.Widen (Id int NOT NULL PRIMARY KEY, Code varchar(10) NOT NULL);
CREATE INDEX IX_Widen_Code ON dbo.Widen (Code);

ALTER TABLE dbo.Widen ALTER COLUMN Code varchar(50) NOT NULL;

That succeeds, index and all, and the index is rebuilt for you. varchar(10) to varchar(20), int to bigint, decimal(10,2) to decimal(12,2) — widening within the same type is allowed.

A schema-bound view is the exception that catches people out. Add one and even the widening above is refused, because WITH SCHEMABINDING is a promise that the column will not change shape underneath it:

Msg 5074, Level 16, State 1, Line 1
The object 'ProductCode' is dependent on column 'Code'.

So before assuming you are in for the whole drop-and-recreate dance, try the statement. If the change is a widening and nothing is schema-bound, you are already done.

Listing what holds the column

Rather than reading error messages one at a time, ask the database. This covers the three that account for most of them — indexes, default constraints, and anything schema-bound:

DECLARE @table sysname = 'dbo.Product';
DECLARE @column sysname = 'Code';

SELECT  i.name AS DependentObject, 'index' AS Kind
FROM    sys.indexes AS i
JOIN    sys.index_columns AS ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
JOIN    sys.columns AS c ON c.object_id = ic.object_id AND c.column_id = ic.column_id
WHERE   i.object_id = OBJECT_ID(@table) AND c.name = @column

UNION ALL
SELECT  d.name, 'default constraint'
FROM    sys.default_constraints AS d
JOIN    sys.columns AS c ON c.object_id = d.parent_object_id AND c.column_id = d.parent_column_id
WHERE   d.parent_object_id = OBJECT_ID(@table) AND c.name = @column

UNION ALL
SELECT  OBJECT_NAME(sed.referencing_id), 'view or routine'
FROM    sys.sql_expression_dependencies AS sed
WHERE   sed.referenced_id = OBJECT_ID(@table)
  AND   sed.referenced_minor_id = COLUMNPROPERTY(OBJECT_ID(@table), @column, 'ColumnId');

DependentObject   Kind
----------------  ------------------
IX_Product_Code   index
DF_Product_Code   default constraint
ProductCode       view or routine

Check constraints are worth a separate look, because one can mention a column it is not attached to:

SELECT name, definition
FROM   sys.check_constraints
WHERE  parent_object_id = OBJECT_ID('dbo.Product')
  AND  definition LIKE '%Code%';

Clearing it, in order

Drop in this order, change the column, then rebuild in reverse:

DROP VIEW dbo.ProductCode;                            -- schema-bound things first
DROP INDEX IX_Product_Code ON dbo.Product;
ALTER TABLE dbo.Product DROP CONSTRAINT DF_Product_Code;

ALTER TABLE dbo.Product ALTER COLUMN Code nvarchar(20) NOT NULL;

ALTER TABLE dbo.Product ADD CONSTRAINT DF_Product_Code DEFAULT (N'NEW') FOR Code;
CREATE INDEX IX_Product_Code ON dbo.Product (Code);
GO
CREATE VIEW dbo.ProductCode WITH SCHEMABINDING AS SELECT ProductId, Code FROM dbo.Product;

Three things go wrong here more often than the change itself.

The default comes back subtly different. The column above became nvarchar, so the default is written N'NEW'. Recreate it as 'NEW' and every row inserted from then on takes an implicit conversion.

The index comes back without its options. Script the original before dropping it — the fill factor, the included columns, whether it is filtered, the filegroup. CREATE INDEX with none of that is a different index wearing the same name, and nobody notices until a query plan changes.

NOT NULL is not carried over. ALTER COLUMN takes the whole definition, so leaving the nullability off makes the column nullable, quietly:

ALTER TABLE dbo.Product ALTER COLUMN Code nvarchar(20);   -- now nullable

Before you start

The list of what holds a column is the hard part, and none of it is visible from the table definition: the view lives elsewhere, the check constraint may be on another column, and the index options only exist in the index.

That is what WoodFireERD reads for you — pick the table, see what points at it and what it points at, and get the change out as an ordered script with the drops, the alter and the rebuilds already in the right sequence.

Originally published at woodfireerd.com. Every statement in it was run against a real SQL Server before publishing — the error messages are copied from the output, not from memory.

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint

Sam — Mon, 05 Oct 2026 09:46:40 +0000

You add a foreign key to a table that has been live for years, and SQL Server refuses:

ALTER TABLE dbo.SalesOrder ADD CONSTRAINT FK_SalesOrder_Customer
    FOREIGN KEY (CustomerId) REFERENCES dbo.Customer (CustomerId);

Msg 547, Level 16, State 1, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_SalesOrder_Customer".
The conflict occurred in database "erd_article_test", table "dbo.Customer", column 'CustomerId'.

Read that message carefully, because it sends most people to the wrong table. It names dbo.Customer and column CustomerId — the table being pointed at. There is nothing wrong with any row in it. The rows at fault are in dbo.SalesOrder: they hold a CustomerId that Customer does not have.

Find the rows

SELECT o.OrderId, o.CustomerId
FROM   dbo.SalesOrder AS o
WHERE  o.CustomerId IS NOT NULL
  AND  NOT EXISTS (SELECT 1 FROM dbo.Customer AS c WHERE c.CustomerId = o.CustomerId)
ORDER BY o.OrderId;

OrderId  CustomerId
-------  ----------
11       99
13       404

IS NOT NULL is not optional. A null foreign key means "points at nothing", which a foreign key allows; without that line you get every unlinked row back and the list is useless.

If you only want to know the size of the problem before deciding anything:

SELECT COUNT(*) AS Orphans
FROM   dbo.SalesOrder AS o
WHERE  o.CustomerId IS NOT NULL
  AND  NOT EXISTS (SELECT 1 FROM dbo.Customer AS c WHERE c.CustomerId = o.CustomerId);

Three honest fixes

Create the missing parents if the values mean something — a customer deleted by hand, a load that ran out of order. The orders keep their history.

INSERT dbo.Customer (CustomerId)
SELECT DISTINCT o.CustomerId
FROM   dbo.SalesOrder AS o
WHERE  o.CustomerId IS NOT NULL
  AND  NOT EXISTS (SELECT 1 FROM dbo.Customer AS c WHERE c.CustomerId = o.CustomerId);

Set them to null if the link is wrong but the row is real. The order survives, pointing at nobody, which is exactly what the data is telling you:

UPDATE dbo.SalesOrder
SET    CustomerId = NULL
WHERE  CustomerId IS NOT NULL
  AND  NOT EXISTS (SELECT 1 FROM dbo.Customer AS c WHERE c.CustomerId = dbo.SalesOrder.CustomerId);

Delete them only when you know the rows are rubbish. Check what points at them first, or you will be reading this article again about the next table down.

WITH NOCHECK, and what it costs

There is a fourth option, and it is the one reached for under time pressure:

ALTER TABLE dbo.SalesOrder WITH NOCHECK ADD CONSTRAINT FK_SalesOrder_Customer
    FOREIGN KEY (CustomerId) REFERENCES dbo.Customer (CustomerId);

That succeeds. The existing rows are not examined, and the bad ones stay exactly as they are.

What it buys you is real: from now on, nothing new can be inserted that breaks the rule.

INSERT dbo.SalesOrder (OrderId, CustomerId) VALUES (14, 777);

Msg 547, Level 16, State 1, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_SalesOrder_Customer".

What it costs is that the key is not trusted:

SELECT name, is_not_trusted, is_disabled
FROM   sys.foreign_keys
WHERE  name = 'FK_SalesOrder_Customer';

name                     is_not_trusted  is_disabled
-----------------------  --------------  -----------
FK_SalesOrder_Customer   1               0

is_not_trusted = 1 means SQL Server is enforcing the rule but will not believe it when building a query plan. It cannot eliminate the join to Customer in a query that only needs columns from SalesOrder, because as far as it knows there are rows in there with no parent — and there are. The constraint documents an intention the data does not meet.

Worth knowing: a key you never fix stays untrusted forever, and nothing warns you. This finds them all:

SELECT OBJECT_SCHEMA_NAME(parent_object_id) AS SchemaName,
       OBJECT_NAME(parent_object_id)        AS TableName,
       name                                 AS ForeignKey
FROM   sys.foreign_keys
WHERE  is_not_trusted = 1 AND is_disabled = 0;

Making it trusted later

Once the rows are sorted out, ask SQL Server to check what it skipped:

ALTER TABLE dbo.SalesOrder WITH CHECK CHECK CONSTRAINT FK_SalesOrder_Customer;

The doubled CHECK CHECK is not a typo: the first says check the existing rows, the second names the constraint to enable. Run it while bad rows are still there and you get Msg 547 again — the same message, for the same reason. Run it after they are gone and is_not_trusted goes to 0.

This is the step that gets forgotten, because the constraint already looks right in every tool that lists it. Add it to the migration script that does the cleanup, not to a note for later.

The part worth doing first

Before adding the key at all, know what the table is already joined to and what is pointing at it — a foreign key's effect is on the other table, so reading the table you are changing tells you very little.

That is the question WoodFireERD answers: pick a table, see what it points at and what points at it, and have the relationship come out as an ordered script, with the rows that would break it listed before anything runs.

Originally published at woodfireerd.com. Every statement in it was run against a real SQL Server before publishing — the error messages are copied from the output, not from memory.