Fix it now
These four are the database refusing to store data that breaks a rule you declared. 2627 is a duplicate key in a primary key or unique constraint, 547 is a foreign key or check constraint conflict, 515 is a NULL in a column that does not allow one, and 544 is an explicit identity value while IDENTITY_INSERT is off. Find the offending rows before weakening anything.
SELECT KeyCol, COUNT(*) AS Copies FROM #Staging GROUP BY KeyCol HAVING COUNT(*) > 1;
SELECT c.* FROM #Staging AS c
WHERE NOT EXISTS (SELECT 1 FROM dbo.Parent AS p WHERE p.Id = c.ParentId);
SELECT * FROM #Staging WHERE RequiredCol IS NULL;
- Read the message rather than looking the constraint up. 2627 names whether it was a PRIMARY KEY or a UNIQUE KEY and gives the duplicate value; 547 names the statement type, the constraint type, the database and the table.
- Run the matching query above against your source data. Whatever it returns is the fix list.
- For 544, either drop the identity column from the insert list, or set
SET IDENTITY_INSERT dbo.YourTable ON, insert with an explicit column list, and set it off again. - Fix the data and rerun. Change the constraint only if the rule itself turns out to be wrong.
Only one table per session can have IDENTITY_INSERT ON at a time, and you need to own the table or hold ALTER on it. A script loading several tables has to switch it off between them.
If the write completes, you are done. If it fails again on different rows, the next section covers what each constraint is checking and when.
Why it happens
A primary key or unique constraint is enforced by a unique index, and the engine checks it as each row is written. That is why an insert of ten thousand rows fails on the one duplicate and rolls the statement back: a single statement is atomic whether or not you wrapped it in a transaction. 2627’s published text names the constraint type and gives you the duplicate key value, which is usually enough to find the row without any further query. If the collision comes from a unique index that is not backing a constraint you get 2601 instead, whose text names the index rather than the constraint. The investigation is identical.
A foreign key checks that the value being written exists in the referenced table, and it fires in the opposite direction too: deleting a parent row that still has children raises 547 just as inserting an orphan child does. Check constraints use the same error number, so read the message rather than assuming which kind it was – its published text names both the statement type and the constraint type, along with the database and table where the conflict occurred.
515 is simpler than it looks. A column declared NOT NULL with no default rejects an insert that does not mention it at all, as well as one that supplies NULL explicitly. Its published text names the column, the table, and the statement that failed. Widening the insert column list and adding a sensible default are different fixes with different consequences, and which is right depends on whether the value is genuinely unknown or merely unmapped.
544 is the identity guard: an explicit value cannot be supplied for an identity column while IDENTITY_INSERT is OFF. Microsoft’s related guidance adds the part people miss – the explicit value can only be supplied when a column list is used and IDENTITY_INSERT is ON, so turning the setting on without also writing out the column list still fails.
The incoming data genuinely contains duplicates
You have this one if The grouping query returns rows: the same key appears more than once in the source.
- Decide which duplicate wins and de-duplicate in the staging query, with
ROW_NUMBER() OVER (PARTITION BY KeyCol ORDER BY LoadedAt DESC)and a filter on row number 1. - Where the load should be repeatable, insert only what is missing:
INSERT ... SELECT ... WHERE NOT EXISTS (SELECT 1 FROM dbo.Target WHERE ...). - If the duplicates are legitimate business data, the key is wrong rather than the data. Widen the key rather than dropping the constraint.
An identity or sequence collides with rows that already exist
You have this one if 2627 on the identity column itself, usually after a restore, a manual reseed or an import that used IDENTITY_INSERT.
- Check the seed against the data:
DBCC CHECKIDENT ('dbo.YourTable', NORESEED);returns the current identity value and the current maximum in the column without changing anything. - If they differ,
DBCC CHECKIDENT ('dbo.YourTable', RESEED);resets the seed to the maximum value in the column on its own – you rarely need to supply a number. - For a sequence object, compare
sys.sequenceswith the maximum value stored in the table before altering the restart value.
Only one table per session can have IDENTITY_INSERT ON, and the permission needed is ownership of the table or ALTER on it, which is often why the workaround fails for an application account.
Rows are inserted or deleted in the wrong order
You have this one if 547 naming a foreign key, on a load that touches several related tables.
- Insert parents before children, and delete children before parents.
- For a package or script loading many tables, order the work by the dependency graph in
sys.foreign_keys. - Where deletes are routine and the relationship genuinely cascades, declare
ON DELETE CASCADE. Do not add it as a shortcut around a data problem.
Two sessions race for the same key
You have this one if 2627 appears intermittently under load, on a table where the application checks for existence before inserting.
- Accept that a check followed by an insert is not atomic. Between the two, another session can insert the same key.
- Let the constraint do the work and handle the error in a
TRY/CATCHblock, treating 2627 as a signal that the row already exists. - Where you must serialise, take the lock inside the read: a
MERGEor anIF EXISTStest withHOLDLOCKon the target closes the window.
A required column is missing from the insert, not from the data
You have this one if 515 naming a column your source does populate, on a statement whose column list omits it.
- Read the column and table the message names, then compare them against the insert’s column list rather than against the source.
- Add the column to the list, or give it a default if the value is genuinely optional at write time.
- Only relax the NOT NULL if the business rule was wrong. A nullable column is a permanent change to what the table promises.
Full reference
Symptom to source
| What you see | Where the bad data is |
|---|---|
| 2627 on a load that used to work | Duplicates in the incoming file, or an identity or sequence that was reseeded |
| 2627 under concurrency only | Two sessions inserting the same key between the check and the write |
| 2601 rather than 2627 | A unique index that is not backing a constraint. Same investigation |
| 547 on an insert | A child row whose parent does not exist yet, or an ordering problem in the load |
| 547 on a delete | Child rows still referencing the parent you are removing |
| 547 naming a check constraint | A value outside the range the column is declared to accept |
| 515 | A column missing from the insert list, or a source value that is NULL |
| 544 | The insert supplies the identity value instead of letting the engine generate it |
Loading with IDENTITY_INSERT, properly
SET IDENTITY_INSERT dbo.YourTable ON;
INSERT dbo.YourTable (Id, Name, CreatedAt)
SELECT Id, Name, CreatedAt FROM #Staging;
SET IDENTITY_INSERT dbo.YourTable OFF;
DBCC CHECKIDENT ('dbo.YourTable', NORESEED);
- The explicit column list is not optional. Without it the insert fails even with the setting on.
- Only one table per session can have the setting ON. Switch it off before the next table or you get an error naming the table that already holds it.
- You need to own the table or hold ALTER permission on it.
- Check the seed afterwards. Loading explicit values leaves the identity behind the data, which is exactly how the next 2627 happens.
DBCC CHECKIDENT, and what it does on its own
| Call | Effect |
|---|---|
DBCC CHECKIDENT (table, NORESEED) |
Reports the current identity value and the current maximum in the column. Changes nothing |
DBCC CHECKIDENT (table) |
If the current value is below the maximum in the column, resets it to that maximum |
DBCC CHECKIDENT (table, RESEED) |
The same: resets to the maximum in the column when the current value is lower |
DBCC CHECKIDENT (table, RESEED, value) |
Sets the seed to the value you supply. Use only when you know why the automatic behaviour is wrong |
Run NORESEED first and read both numbers. Microsoft’s own guidance is that if the two are not the same you should reset the identity value to avoid errors or gaps, and in most cases the automatic form does exactly the right thing without you having to work out the maximum yourself.
Disabling a constraint, and what it costs
Disabling a constraint with NOCHECK does not merely skip the check for one load. The constraint is left untrusted, and the optimiser stops using it for plan choices until you re-enable it with WITH CHECK – which then validates every existing row and fails if the bad data is still there. Validate and re-enable in the same script, and never leave a foreign key untrusted in production.
ALTER TABLE dbo.Child NOCHECK CONSTRAINT FK_Child_Parent;
-- load
ALTER TABLE dbo.Child WITH CHECK CHECK CONSTRAINT FK_Child_Parent;
SELECT name, is_not_trusted FROM sys.foreign_keys WHERE is_not_trusted = 1;
De-duplicating a staging set
WITH Ranked AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY KeyCol ORDER BY LoadedAt DESC) AS rn
FROM #Staging
)
INSERT dbo.Target (KeyCol, Name, LoadedAt)
SELECT KeyCol, Name, LoadedAt
FROM Ranked AS r
WHERE r.rn = 1
AND NOT EXISTS (SELECT 1 FROM dbo.Target AS t WHERE t.KeyCol = r.KeyCol);
When the constraint is the thing that is wrong
Occasionally the data is right and the rule is not: a key that was always going to collide once a second source was added, a NOT NULL on something genuinely optional, a check constraint written for a narrower range than the business actually uses. Changing the declaration is then the correct fix, and it should be made deliberately, with the same review any schema change gets. What is not a fix is leaving the rule in place and defeating it per load, because that produces a table whose declared shape and actual contents have quietly stopped agreeing.
Every code this article covers
| Code | What it points at | Source |
|---|---|---|
2627 |
Violation of a PRIMARY KEY or UNIQUE KEY constraint: a duplicate key could not be inserted, and the message gives the duplicate value | Microsoft Learn |
547 |
The statement conflicted with the named constraint; the message gives the statement type, the constraint type, the database and the table | Microsoft Learn |
515 |
NULL cannot be inserted into the named column of the named table because it does not allow nulls, and the statement fails | Microsoft Learn |
544 |
An explicit value cannot be inserted for an identity column while IDENTITY_INSERT is set to OFF | Microsoft Learn |
Confirm the fix worked
- Re-run the grouping or anti-join query and confirm it returns no rows before you retry the write.
- Run the original statement and confirm the row count matches what you expected to insert.
SELECT name, is_not_trusted FROM sys.foreign_keys WHERE is_not_trusted = 1;returns nothing.- If you used IDENTITY_INSERT, confirm it is OFF again and that
DBCC CHECKIDENT ('dbo.YourTable', NORESEED)shows the seed at or above the maximum in the column. - Re-run the load a second time and confirm it is idempotent rather than producing a fresh set of duplicates.
Questions people ask about this
Can I just disable the constraint to get the load through?
You can, and you will regret it. An untrusted constraint stops the optimiser using it, and the bad rows are still there when someone tries to re-enable it with validation, which then fails. Fix the data instead.
What is the difference between 2627 and 2601?
They report the same collision from different places. 2627 comes from a primary key or unique constraint and names the constraint; 2601 comes from a unique index that is not backing a constraint and names the index. The investigation is identical.
IDENTITY_INSERT is on and the insert still fails. Why?
Probably a missing column list. The explicit identity value can only be supplied when a column list is used and the setting is ON. Check too that no other table in the same session already holds the setting, because only one may at a time, and that you own the table or hold ALTER on it.
Does this cost anything to fix?
No. Constraint violations behave identically on every edition, and there is nothing to buy. It is your data and your schema, and both are free to change.
Should I use IGNORE_DUP_KEY to make duplicates disappear?
Only with your eyes open. It turns the failure into a warning and silently discards the duplicate rows, which is right for some staging loads and quietly destructive for most other work.
