Can I calculate the next value as the largest existing ID plus one?

That approach can race with other inserts. Use the database's supported generator and retrieval mechanism.

Are identity values always consecutive?

No. Failures, allocation behavior and administrative actions can create gaps.

Does an identity column prevent duplicate customers?

No. It distinguishes rows; determining whether rows describe the same customer requires appropriate business rules or entity resolution.

AI Glossary / Data engineering and architecture / Beginner

Identity column

Definition

An identity column is a database column whose values can be generated automatically when new rows are inserted. It is commonly used to provide a numerical reference for each row. The database manages the generation mechanism, while separate constraints determine whether values must be unique and how other tables may reference them. Exact syntax and behavior depend on the database system.

What it means

Instead of asking an application to invent the next row number, an identity mechanism supplies a value during insertion. This is useful when a record needs a technical identifier independent of names or business descriptions. An automatically generated value is not automatically a business sequence, an accurate row count or a record of transaction completion order. It is also not proof that the row represents a distinct real-world entity; duplicate customer records can receive different perfectly valid IDs.

How it works

A table definition enables identity generation for a column. Supported systems commonly provide configuration for the starting value and increment, with rules for explicit values and overrides. On insertion, the database obtains a value using its generation mechanism. A primary-key or uniqueness constraint should enforce the intended uniqueness requirement. Applications retrieve the assigned value using the database's supported insertion-return mechanism rather than guessing from the current largest value. Documentation must be checked for reset, import, replication and failure behavior.

Three practical examples

A support system assigns each newly stored ticket a technical ticket-row ID, while retaining any separate customer-facing reference.
A data-labeling platform identifies individual annotation records so edits and review decisions can point to the correct row.
A document catalog generates IDs for imported records, maintaining separate constraints to detect repeated source documents.

Think of it this way

Think of a ticket dispenser that issues a number whenever someone requests service. The dispenser simplifies number allocation, but the numbers do not tell you how many people remain in the room or who finished first. The analogy is limited because database transactions can roll back, systems can restart and identity settings can be changed. Those events make the database's documented rules more important than the familiar dispenser image.

Worked example

Imagine a generator configured to begin at 100 and increase by one. A successful insertion receives 100. A later attempt obtains 101 but its transaction is rolled back. Another successful insertion receives 102 under a system where the consumed value is not reclaimed. The table now contains two rows with IDs 100 and 102. The missing 101 does not establish that someone deleted a row. Nor can the largest value, 102, be used as the row count. The exact handling of failed inserts must be checked for the selected database, but gapless numbering must not be assumed.

Illustrative case study

In a fictional team, Juniper Records adds identity-generated IDs to a catalog. An import includes old externally assigned reference numbers, which staff keep in a separate column. They add uniqueness rules for the appropriate source-reference combination and use the generated ID for internal relationships. During testing, a failed import leaves a gap, which the team documents as expected behavior for its chosen system. This case illustrates separating technical allocation from business identity; it does not claim that one identity setting solves every migration or duplicate-detection problem.

Common misconceptions and mistakes

An identity column is not automatically a primary key. In PostgreSQL, identity generation does not by itself guarantee uniqueness; Microsoft likewise documents separate uniqueness enforcement for SQL Server's identity property. Gaps do not necessarily indicate corruption. Higher IDs should not be treated as a reliable substitute for explicit event timestamps or commit ordering. Identity values are also not inherently safe to expose as an authorization mechanism. An application must verify access even when a user supplies a valid-looking row ID.

Business use

Use identity columns when database-managed allocation fits the system's architecture, but plan how references survive exports, imports and consolidation. Two independent databases can generate the same number, requiring a mapping when combined. Keep statutory or business document numbering requirements separate from technical row IDs and review those requirements with the appropriate specialists. For AI data pipelines, retaining source identifiers alongside generated row IDs helps trace errors back to their origin. Check capacity limits before a heavily used column approaches its data type's range.

Check your understanding

A table contains identity values 10, 11 and 14. Can you conclude that it once contained exactly five rows?

Answer: No. Values may have been consumed without committed rows, inserted under special rules or affected by administrative operations. Count rows directly and consult audit evidence when investigating history.

Related terms

Primary key
Sequence
Surrogate key
Database transaction
unique constraint
auto-increment
entity resolution

Learn next

Compare the identity syntax and guarantees in the database you actually use. Test an insertion, rollback and import in a disposable environment. Then design separate rules for technical row references and source-system identifiers, including how relationships are remapped during migration.

Sources

Sources: PostgreSQL 17, “Identity Columns,” https://www.postgresql.org/docs/17/ddl-identity-columns.html; Microsoft, “IDENTITY (Property) (Transact-SQL),” https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property?view=sql-server-ver17; PostgreSQL 18, “CREATE TABLE,” https://www.postgresql.org/docs/18/sql-createtable.html. Primary-source search excerpts consulted; full-document and platform-specific editorial review remain pending. The numerical example is illustrative and explicitly depends on the selected system's allocation behavior.

Previous: Identity and access management Next: Identity matrix