Primary Key in DBMS: Definition & Types
The guide
Query your table as a graph.Petabytes. Subsecond. No data movement.

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.
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. 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.
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, 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.

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:
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:
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.
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 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 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:
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:
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:
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 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:
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 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 is:
ALTER TABLE imported_courses DROP PRIMARY KEY;SQLite uses a different migration path. Its table-rebuild procedure 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 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 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 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:
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.
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 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.

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.

Figure: Companies that use PuppyGraph.

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 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, 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 and book a 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.
Keep learning
Database Concept
SQL Execution Plan: How It Works and How to Read It
Learn what a SQL execution plan is, how to generate and read execution plans, understand query operators, identify performance bottlenecks, and optimize SQL queries.
Database Concept
Predicate Pushdown: How It Works?
Learn what predicate pushdown is, how it works in SQL, Spark, and data lakes, with practical examples, performance benefits, and query optimization tips.
Database Concept
What Is Relational Model? A Guide
Learn what the relational model is in DBMS, how it works, its key components, constraints, examples, advantages, limitations, and applications.
Start free. Pay when it goes to production.
Contact us for any questions about capabilities, sizing and support.
* No payment required to start either one.
$ docker run -p 8081:8081 \
-p 8182:8182 -p 7687:7687 \
puppygraph/puppygraph:latestPulling puppygraph/puppygraph:latestStarting PuppyGraphWeb console listening on 0.0.0.0:8081Gremlin endpoint listening on 0.0.0.0:8182PuppyGraph is running# no pipelines · no second database · no data movement




