# Primary Key in DBMS: Definition & Types

> Learn what a primary key is in DBMS, how primary key constraints work, SQL syntax, examples, types, and differences between primary keys and other database keys.

- Canonical URL: https://www.puppygraph.com/learn/primary-key
- Published: 2026-11-20
- Author: Hao Wu

A primary key gives applications a dependable way to identify a row even when its descriptive attributes change. Choosing that identifier affects updates, relationships between tables, and how easily data can move between systems without losing its identity.

This guide explains primary key constraints, their SQL syntax, and the design choices behind them. A course catalog provides the running example. SQL examples use PostgreSQL unless another database is explicitly named.

## What is a primary key?

A primary key is the column or combination of columns designated to identify each row in a relational table. Its values must be unique and non-NULL. A table has at most one primary key constraint, although that constraint can cover several columns. These are the rules described in [PostgreSQL's primary key documentation](https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-PRIMARY-KEYS).

In a course catalog, `course_id = 101` identifies one course. Its title may change from “SQL Basics” to “SQL Foundations” while the identifier stays the same. Other tables can continue referring to course 101.

Several related terms describe different roles:

| **Key or constraint** | **Purpose** | **Course-catalog example** |
| --- | --- | --- |
| Primary key | The chosen row identifier | `courses.course_id` |
| Candidate key | A minimal set of columns capable of identifying a row | `course_id`, or `course_code` if always unique and present |
| Alternate key | A candidate key not selected as primary | `course_code` when `course_id` is primary |
| Unique constraint | Enforces uniqueness without designating the primary identifier | `UNIQUE (course_code)` |
| Foreign key | Requires referencing values to match an eligible key in a referenced table, subject to its NULL rules | `enrollments.course_id` referencing `courses.course_id` |

Minimal means no column can be removed without losing the identifying property. A primary key answers which row this is; a foreign key records a relationship to another row.

A unique constraint alone does not necessarily make a column a candidate key. PostgreSQL allows multiple NULLs under its default [unique-constraint rules](https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-UNIQUE-CONSTRAINTS). Add `NOT NULL` when an alternative identifier must always be present. Other databases can treat NULLs differently, so distinguish business identity from the SQL constraint used to enforce it.

## How does a primary key work?

An enforced primary key checks proposed inserts and updates. Reusing an existing key or assigning NULL violates the constraint. The database applies the rule regardless of which application submits the write.

For the primary keys in this guide, PostgreSQL creates a unique B-tree index. The index enforces uniqueness and can support lookups by ID; creating another identical index is unnecessary. See its [unique-index documentation](https://www.postgresql.org/docs/current/indexes-unique.html).

Uniqueness also matters when writes overlap. Two requests can both check that an ID is unused before either inserts it. PostgreSQL coordinates conflicting inserts through its [index uniqueness checks](https://www.postgresql.org/docs/current/index-unique-checks.html), so an application-side existence check does not replace a constraint.

The constraint protects the selected columns. Two rows with different IDs can still describe the same course. A separate unique constraint on `course_code` expresses that additional business rule.

![How does a primary key work?](https://o6xswp5m6ilbks2z.public.blob.vercel-storage.com/uploads/ab8e18ef4ebebeddc0b3152c/7e82b85f-3c07-494a-833c-a0a88c9fb8f6/hao-wu-20261120-4702e6e2c9a1019308e5689e.png)

Figure: Foreign-key values may repeat across rows. A primary key must be unique within its own table, whether it identifies a course by one ID or an enrollment by a pair of IDs.

## Primary key syntax in SQL

For a single column, write `PRIMARY KEY` after its data type:

```sql
CREATE TABLE course_categories (
    category_id BIGINT PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL
);
```

For a named constraint, place `CONSTRAINT constraint_name PRIMARY KEY (column_name)` after the column definitions. A composite key lists multiple columns inside those parentheses. Both forms declare the same kind of constraint; the table-level form accommodates multiple columns and makes the constraint name explicit.

`PRIMARY KEY` does not mean auto-increment. The declaration above requires the insert to supply `category_id`, because no default or generator is defined.

## How to create a primary key in SQL

Define the key when creating the table, alongside other rules that describe valid data:

```sql
CREATE TABLE courses (
    course_id BIGINT GENERATED ALWAYS AS IDENTITY,
    course_code VARCHAR(20) NOT NULL UNIQUE,
    title VARCHAR(200) NOT NULL,
    CONSTRAINT pk_courses PRIMARY KEY (course_id)
);

INSERT INTO courses (course_code, title)
VALUES ('SQL-101', 'SQL Foundations'),
       ('DB-201', 'Database Design')
RETURNING course_id, course_code;
```

Here, the identity clause generates IDs when they are omitted. The primary key enforces uniqueness and non-nullability, while `course_code` has its own business uniqueness rule. PostgreSQL explicitly distinguishes [identity generation from uniqueness enforcement](https://www.postgresql.org/docs/current/ddl-identity-columns.html).

The constraint name `pk_courses` also helps interpret errors and write later migrations. On a fresh table with the default identity sequence, these inserts receive IDs 1 and 2. The [RETURNING clause](https://www.postgresql.org/docs/current/dml-returning.html) sends each generated ID and its course code back to the caller. Applications can use those returned values when creating related records, avoiding a separate lookup or a guess about the next ID.

Sequence-generated identifiers can have gaps after failed transactions or other operations. PostgreSQL's [sequence documentation](https://www.postgresql.org/docs/current/functions-sequence.html) explicitly rules out a gapless guarantee. An unused number does not mean a course is missing, and the largest ID is not a row count. Keep any business requirement for consecutive numbering separate from primary-key generation.

## How to add a primary key to an existing table

An imported table may already contain IDs without enforcing them. This separate staging example starts that way:

```sql
CREATE TABLE imported_courses (
    course_id BIGINT,
    title VARCHAR(200)
);

INSERT INTO imported_courses VALUES
    (101, 'SQL Foundations'),
    (102, 'Database Design');
```

Check missing identifiers and duplicates before adding the constraint:

```sql
SELECT *
FROM imported_courses
WHERE course_id IS NULL;

SELECT course_id, COUNT(*) AS occurrences
FROM imported_courses
GROUP BY course_id
HAVING COUNT(*) > 1;
```

Both queries return no rows for this fixture. If either returns rows in your data, investigate their meaning. Duplicate IDs might represent duplicate records, different entities incorrectly sharing an ID, or IDs unique only within a tenant. Each situation needs a different repair.

Once the values are valid, add the key:

```sql
ALTER TABLE imported_courses
ADD CONSTRAINT pk_imported_courses PRIMARY KEY (course_id);
```

The database validates the existing data; the diagnostic queries do not reserve IDs against concurrent writers. On a busy table, plan the migration around the locking and index-building work. PostgreSQL's [ALTER TABLE reference](https://www.postgresql.org/docs/current/sql-altertable.html) documents those requirements. A short DDL statement can still be a substantial operation on a large table.

## How to drop a primary key in SQL

In PostgreSQL, remove the named constraint:

```sql
ALTER TABLE imported_courses
DROP CONSTRAINT pk_imported_courses;
```

The table and its rows remain. The primary-key constraint and its backing index are removed. Dropping it does not automatically remove the columns' `NOT NULL` property, so inspect the remaining definition before assuming NULLs are now accepted.

If another table has a foreign key depending on this primary key, PostgreSQL rejects the drop by default. Its [constraint-removal documentation](https://www.postgresql.org/docs/current/ddl-alter.html#DDL-ALTER-REMOVING-A-CONSTRAINT) explains the dependency. Plan the replacement key and dependent foreign keys together; adding `CASCADE` can remove those foreign-key constraints too.

For an equivalent MySQL table, the documented [DROP PRIMARY KEY syntax](https://dev.mysql.com/doc/refman/8.4/en/alter-table.html) is:

```sql
ALTER TABLE imported_courses DROP PRIMARY KEY;
```

SQLite uses a different migration path. Its [table-rebuild procedure](https://www.sqlite.org/lang_altertable.html#otheralter) covers adding or removing primary-key constraints. Do not assume one engine's `ALTER TABLE` syntax works unchanged in another.

## Types of primary keys

Primary keys have two independent design dimensions: how many columns identify the row, and where their values come from.

**Simple primary key.** One column identifies the row, such as `courses.course_id`. This keeps references concise. A simple key can use a number, a UUID, or another suitable data type; the designation refers to column count.

**Composite primary key.** Two or more columns identify the row together. In an active-enrollment table, `(learner_id, course_id)` identifies one learner's enrollment in one course. Either value may repeat individually; the pair cannot. Microsoft's [composite primary key explanation](https://learn.microsoft.com/en-us/sql/relational-databases/tables/primary-and-foreign-key-constraints?view=sql-server-ver17) describes this combination-level uniqueness.

**Natural primary key.** An existing domain identifier supplies the value. A course code could serve if the organization guarantees that it remains unique, present, and stable. Codes reused across departments would need additional scope.

**Surrogate primary key.** An identifier is introduced for database identity, such as the generated `course_id`. It lets a course code change without changing every reference to the course. [Oracle's data-integrity documentation](https://docs.oracle.com/en/database/oracle/oracle-database/26/cncpt/data-integrity.html) distinguishes natural and surrogate keys.

These categories overlap. The catalog's generated `course_id` is both simple and surrogate. Choosing a surrogate also leaves business uniqueness to other constraints, such as the catalog's unique course code.

## Can a primary key have NULL values?

Under standard SQL semantics, no. NULL represents a missing or unknown value, which cannot provide the required row identity. Every column in a composite primary key must also be non-NULL, not just one component.

An omitted value can still succeed when a default or identity generator supplies it. That produces a stored identifier; it does not store a NULL primary key.

SQLite has a documented compatibility exception: some ordinary primary-key declarations permit NULL unless `NOT NULL` is explicit. Its `INTEGER PRIMARY KEY`, `WITHOUT ROWID`, and `STRICT` table cases behave differently. The [SQLite CREATE TABLE documentation](https://www.sqlite.org/lang_createtable.html#the_primary_key) explains the exception. If SQLite tests stand in for another database, define nullability explicitly so this difference does not conceal invalid input.

## Can a table have multiple primary keys?

No. One table has at most one primary key constraint. A composite primary key contains several columns within that single constraint. Multiple candidate keys or unique constraints do not change that limit.

Using the earlier `courses` table, create learners and their active enrollments:

```sql
CREATE TABLE learners (
    learner_id BIGINT PRIMARY KEY,
    learner_name VARCHAR(100) NOT NULL
);

CREATE TABLE enrollments (
    learner_id BIGINT REFERENCES learners (learner_id),
    course_id BIGINT REFERENCES courses (course_id),
    CONSTRAINT pk_enrollments PRIMARY KEY (learner_id, course_id)
);
```

This table permits learner 7 to enroll in courses 1 and 2, and learner 8 to enroll in course 1, once those learners exist. It rejects a second `(7, 1)` pair. The foreign keys separately require each referenced learner and course to exist.

The model represents one active association per pair. To record repeat attempts or separate course offerings, change the row definition and key accordingly. Adding another primary-key declaration cannot resolve that modeling change.

## Primary key best practices

**Choose stable identifiers.** Keep names, titles, and other routinely edited attributes outside the primary key. A natural key works when its stability is a domain guarantee; otherwise, a surrogate often gives references a longer useful life.

**Match the key to the row's meaning.** Decide whether a row represents a course, a scheduled offering, or an enrollment attempt. A key valid for one of those entities may incorrectly reject legitimate records in another.

**Keep business constraints alongside surrogate keys.** Generated IDs make rows distinct even when users accidentally create the same course twice. Retain a unique course-code constraint when that is the business rule. Avoid treating “the insert succeeded” as proof that the business record is new.

**Keep references complete and deliberate.** Use the full identifying combination when referencing a composite key. Carrying only a tenant-local ID into another system loses the tenant scope that makes it unique. Keep key columns reasonably narrow and choose indexes for actual access patterns. A primary-key index does not guarantee result order; use [ORDER BY for ordered output](https://www.postgresql.org/docs/current/queries-order.html).

Stable course and learner IDs also make relationship analysis possible. If the catalog later records prerequisites, those IDs can anchor paths through several prerequisite levels, alongside enrollment links. The source database continues to enforce its keys as other query models use those identities.

[PuppyGraph](https://www.puppygraph.com/) is the first and only zero-ETL graph query engine that queries existing tables as a graph without loading them into a separate graph database. Its distributed engine reads tables in place in SQL databases, data warehouses, and data lakes or lakehouses, including direct reads of open table formats such as Iceberg and Delta Lake. Connect the sources, define a graph schema mapping tables to nodes and edges, and query with openCypher and Gremlin while the same tables remain queryable with SQL.

![Benefits of Elasticsearch Graph](https://o6xswp5m6ilbks2z.public.blob.vercel-storage.com/uploads/ab8e18ef4ebebeddc0b3152c/d0e40fcf-cf5a-40b7-97c1-5f5be25de9b6/hao-wu-20261120-4b2587a755bbd5c9068284ee.png)

Figure: PuppyGraph queries the existing tables in place, so graph and SQL queries share one copy of the data and no pipeline keeps a graph copy in sync.

![Benefits of Elasticsearch Graph](https://o6xswp5m6ilbks2z.public.blob.vercel-storage.com/uploads/ab8e18ef4ebebeddc0b3152c/37a756bf-deab-4cae-b1f3-aecb24bff3b1/hao-wu-20261120-5820007cae8f617f5bb57a14.png)

Figure: Companies that use PuppyGraph.

![Benefits of Elasticsearch Graph](https://o6xswp5m6ilbks2z.public.blob.vercel-storage.com/uploads/ab8e18ef4ebebeddc0b3152c/5f9c5853-ffc3-40c1-9bfe-b097ccf4296c/hao-wu-20261120-4492653ad4ba877e25923f1c.png)

Figure: Engineering teams at PuppyGraph customers on what they use it for.

## Primary key errors and how to fix them

Read the constraint name and conflicting columns before changing data. PostgreSQL's [SQLSTATE error codes](https://www.postgresql.org/docs/current/errcodes-appendix.html) let applications distinguish failure types without parsing localized message text.

**Duplicate key, SQLSTATE** **`23505`****.** The proposed key already exists. Check whether the operation is a repeated request, an intended update, or an ID-generation problem. This code also covers unique constraints, so a course-code conflict may concern the alternate identifier rather than `course_id`. Do not discard the existing row merely to make the insert pass.

**NULL key, SQLSTATE** **`23502`****.** Supply the missing identifier or configure the intended generator. For an [identity column](https://www.postgresql.org/docs/current/ddl-identity-columns.html), omit the column or supply `DEFAULT` to request a generated value; explicitly inserting `NULL` does not invoke the generator. For composite keys, check every component. Replacing all missing IDs with zero merely trades a missing-value problem for a uniqueness problem.

**Multiple primary keys.** Keep one primary key. Use a composite declaration when columns jointly identify the row, or a unique constraint when another identifier must also be unique.

**A primary-key drop is blocked by a dependent foreign key by default.** Inspect dependent foreign keys and plan their migration. Disabling integrity checks without repairing the relationships leaves downstream references unreliable.

When adding a constraint to populated data fails, rerun the duplicate and NULL diagnostics from the earlier section. Correct the identity rule or the records before retrying the DDL.

## Conclusion

A primary key establishes row identity through one constraint over one or more columns. Choose its columns from the table's actual meaning, distinguish generated values from enforced uniqueness, and preserve business rules with additional constraints. Adding or replacing a key also requires checking existing data and the relationships that depend on it.

Try the forever-free [PuppyGraph Developer Edition](https://www.puppygraph.com/download-confirmation) and [book a demo](https://www.puppygraph.com/book-demo) with the team to see how openCypher and Gremlin queries follow relationships across warehouse and lakehouse tables, with no graph-specific ETL, using the stable identifiers already connecting your records.
