The error occurs because the identity value you see is not the value the foreign key is validating. A foreign key checks data type compatibility, collation, schema ownership, and transaction visibility before it checks existence, and any one of those four mismatches produces the same "value does not exist" error even when the row is visibly present. The identity property of a column plays no role in the lookup, which is why a value that is obviously there can be rejected.
The identity property only controls how new numbers are generated. It never registers a value in the referenced table's constraint-check machinery. Most of these failures fall into a small number of repeated patterns: uncommitted inserts, mismatched types, case or whitespace differences, and connection defaults. A smaller group involves cross-database references, permission boundaries, or triggers that silently alter what you insert. This article explains the validation order, quantifies the frequency of each pattern, and gives the exact diagnostic queries that separate a real missing row from a false negative.
How the engine validates a foreign key
Every relational engine follows the same conceptual sequence for a foreign key check. First it resolves the referenced table and column. Then it coerces the value you supplied to that column's declared type. Then it runs an existence lookup inside the same transactional snapshot that your statement sees. Only then does it raise a violation. The identity property is nowhere in this sequence. The value is a number, and the check is a lookup, so an identity column is treated exactly like any other indexed integer.
The reason this surprises people is that identity columns are frequently used as surrogate keys across an entity relationship. A surrogate key is generated by the system, not derived from business data, so the same business record can have different surrogate values in different tables or environments. When you copy a value from the source table by eye or from a spreadsheet export, you are trusting that the value you copied is the one the engine will compare. The coercion and snapshot steps are where that trust breaks down.
In SQL Server, foreign key enforcement is deferred only in the sense that it happens per statement, and self-referencing foreign keys are checked against the current state of the table, not a pre-statement state. In PostgreSQL, foreign key checks run within the statement's snapshot, and a row inserted in a not-yet-committed transaction on another connection is invisible until commit. In MySQL InnoDB, the existence check uses a consistent read against the parent table as of the current transaction, and an uncommitted parent insert on another session is not visible. At Read Committed isolation, a parent row committed within the last few milliseconds can also be missed by a child statement that started before the commit. That is the silent race condition behind many intermittent failures.
The practical consequence is that the error message names the parent table and column, but the engine has already decided the row is absent. You need to test for absence the same way the engine does. That means checking committed data, with the referenced column's exact type, collation, and schema path.
Quantified context: how often each cause applies
The distribution matters because it determines where you spend your diagnostic time. Uncommitted or rolled-back inserts account for roughly four in ten failures in development and CI environments. Application frameworks that wrap work in a unit-of-work pattern often hold the parent insert in the same transaction as the child, and a retry or a nested savepoint can leave the parent insert uncommitted when the child statement runs on a different connection.
Type and collation mismatches account for roughly two to three in ten failures. An identity column defined as INT compares differently to a value supplied as BIGINT, NVARCHAR, or VARCHAR, and the comparison can fail across the whole table if an implicit conversion overflows. Case-sensitive collations produce a mismatch when the parent value is uppercase and the child value is lowercase, which is common when data arrives from an API or a CSV import.
A further one to two in ten failures trace to schema and naming divergence. A table may exist in both the dbo and sales schema, or in a user schema that is not the default path, and the foreign key was created against the wrong one. The remaining failures split between permission boundaries, triggers that silently modify inserted values, and references to a different database or linked server.
The identity property itself explains almost none of these cases. That is the first counterintuitive detail worth internalizing: the error message says your value is missing, and the value genuinely does exist in the table you are looking at, but the engine validated a different table or a different value. A value that passes visual inspection has not passed the engine's check.
Edge cases that produce false negatives
A smaller but persistent class of cases produces an error even though every visible condition appears correct. These are worth knowing because they are the hardest to reproduce and the most embarrassing to escalate.
The child row is inserted by a trigger that fires before the foreign key check, and the trigger writes a different parent identifier than the application intended. This is common in audit tables that remap identifiers. The foreign key references a parent column in a different database or linked server, and the connection fails silently or resolves a stale metadata cache. Case-sensitive cross-database references fail more often than collation-identical local ones. The value contains a trailing space or a non-breaking space that renders identically in a client grid. SQL Server compares trailing spaces as equal in some collations but not others, so a value can look identical and fail. The column is a composite key and you supplied only one part of it. The engine compares the full tuple, and a single matching component is not enough. The referenced column is not unique or not indexed as declared, and the foreign key was created against an index that was later rebuilt or dropped. Some engines reuse an index and refuse the constraint when the referenced column's uniqueness is not provable. The insert runs inside an explicit transaction that was previously rolled back to a savepoint, and the parent row is gone while the app still holds the identifier variable.
Each of these produces the same error text, which is why a systematic diagnostic order matters more than trial and error.
| Cause | Typical symptom | Fast diagnostic | Fix |
|---|---|---|---|
| Uncommitted parent insert | Fails in dev, passes in test | Query parent in a separate session | Commit parent before child insert |
| Type mismatch (INT vs BIGINT) | Fails for every row | Compare column types in both tables | Align types explicitly |
| Collation mismatch | Fails for mixed-case values only | Check collation of both columns | Use a common collation |
| Wrong schema | Fails in only one environment | Qualify table with schema name | Point foreign key at correct schema |
| Trigger rewrites value | Appears sporadic | Read the insert trigger definition | Adjust trigger logic |
| Composite key partial match | Fails only on multi-column keys | List all key columns | Supply the full key tuple |
Where identity, primary key, and foreign key get confused
The most durable misconception is that an identity column is the same thing as a primary key. It is not, and the distinction explains much of the confusion. An identity column generates sequential values. A primary key enforces uniqueness and non-nullness. The two properties are often combined, but they are independent, and a table that appears to have a primary key on a column may instead have a separate unique index or only a clustered index that does not enforce uniqueness at all.
A foreign key references any column or column set with a unique constraint or primary key, not necessarily the identity column. If you create the foreign key against a different unique column, such as a natural key or an alternate key, the identity value you see will never match because the engine is checking the natural key. Review the constraint definition before assuming which column is being validated.
A second misconception is that the identity property propagates across tables. It does not. A foreign key column has no identity property, should never have one, and receives its value from application logic or a relationship. A related misconception is that identity values are globally unique. They are unique only within the table and, historically, only within the current seed and increment. A restored or copied table can carry values that overlap with the source, which is exactly the condition that makes a value look correct while pointing at the wrong record.
A third common error is assuming that the constraint's existence is obvious. A disabled or unverified foreign key still appears in the metadata in some engines and does not enforce anything. If a prior load disabled the constraint and never re-enabled it, you will not get an error at insert time, but your joins will silently fail. The inverse is also common: a constraint exists against a table that was renamed, and the error message uses the old name, which sends you to the wrong place.
Plain language: identity is a numbering rule, not a relationship rule, and the foreign key validates the relationship on the exact columns you named.
Practical actions: a diagnostic order that converges
Work through these steps in order, because each step rules out a class of causes rather than a single case. The goal is to make the failing lookup reproduce in a query you control.
Reproduce the lookup with the exact types. Run SELECT 1 FROM parent_table WHERE referenced_column = @value, casting the parameter to the parent column's declared type. If this returns zero rows, the engine is right and the value is not there in committed form. If it returns a row, the failure is in type, collation, or snapshot behavior. Verify the constraint definition. Read the foreign key's referenced schema, table, and column list from the catalog. Confirm all columns and confirm the referenced column set has a unique constraint or primary key. Test from a separate connection. Open a second session and query the parent with no transaction open. This catches uncommitted parent inserts and snapshot races in one step. Compare collation and type metadata side by side. Mismatched types appear in the column metadata and can be diffed with one query per engine. Use explicit conversion in your insert to force the intended type. Check for triggers on the child table. Read the insert trigger and confirm it does not rewrite the parent identifier. This is the cause most often missed by application developers. Inspect the actual stored value. Query with a byte-level length function or hex representation. Trailing spaces, case, and invisible characters are visible only this way in many clients.
For ongoing prevention, set a threshold rather than an aspiration. Treat any environment where parent and child inserts span more than one connection as a two-phase write and commit the parent first, exactly once. Align column types at the schema level and enforce it in a migration rather than in application code. Run a data cleanup for whitespace and case on the referenced key columns once per quarter. Re-enable and reverify any disabled foreign keys after every major load. Test the relationship path with a seeded integration test that inserts a parent, commits, and then inserts a child, so the snapshot race is exercised rather than hidden.
The identity value being "strong" is irrelevant here. Strength comes from the data type, collation, and committed state of the referenced column. Treat the identity as a label you carry between tables, not as proof of existence.
Limitations and what this does not cover
This explanation covers the common relational failure patterns across SQL Server, PostgreSQL, and MySQL InnoDB. It does not cover engine-specific deferred constraint modes, distributed transactions, or replication topologies where the parent and child live on different nodes. Object-relational features such as PostgreSQL partitioned tables with their own constraint semantics, or SQL Server filtered indexes, introduce additional failure modes that depend on the exact version and configuration. Constraint behavior also differs when the row-level security is enabled: a policy can hide parent rows from the validating session, and the error appears identical to a missing row.
If you are diagnosing this in a production system, capture the failing statement, the transaction isolation level, and the connection string, together with the catalog definitions of both tables. Those four artifacts resolve almost every case within a single review. If the relationship spans systems or a regulated data store, have the schema change reviewed by the responsible data administrator before altering types, collations, or keys.