Incident brief
Multiple primary keys not allowed
A table can have at most one PRIMARY KEY; declaring PRIMARY KEY on two columns separately raises this invalid-table-definition error. Use a composite key for several columns.
What lands in your log
ERROR: multiple primary keys for table "two_pk" are not allowed
In 10 seconds
- What triggers it
- Write CREATE TABLE with PRIMARY KEY on two different columns.
- Fix
- Keep a single PRIMARY KEY, use one column, or a composite PRIMARY KEY (a, b).
- Proof
- Reproduced on PostgreSQL 18.4 → A CREATE TABLE reproduces SQLSTATE 42P16; the second PRIMARY KEY clause is rejected and the LINE points at it.
Fix
What to do right now
Application-level steps for this error.
- Keep a single PRIMARY KEY, use one column, or a composite PRIMARY KEY (a, b).
- Model additional uniqueness with UNIQUE constraints instead of extra primary keys.
CREATE TABLE two_pk (
a int PRIMARY KEY,
b int UNIQUE
);For this error
See this error live on the server
Run these against the affected instance to confirm the diagnosis before you act.
A table can have one primary key but any number of UNIQUE constraints. Inspect the existing primary key before altering the design.
Primary keys already defined
Map each primary key to its table and exact column list.
SELECT conrelid::regclass AS table_name, conname,
pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE contype = 'p'
ORDER BY conrelid::regclass::text;Why it happens
What PostgreSQL is telling you
The mechanism behind the error, grounded in the official manual, not paraphrased.
PostgreSQL 18 Documentation, Appendix A. PostgreSQL Error Codes (Table A.1, Class 42, Syntax Error or Access Rule Violation)
42P16 → invalid_table_definitionRead the full section on postgresql.org →
Declaring two primary keys
Each of the two columns is marked PRIMARY KEY, so PostgreSQL raises 'multiple primary keys for table two_pk are not allowed'.The session continues normally
CREATE TABLE failed outside a transaction, so there's no partially-created object to clean up before the next statement.Reproduce & verify
A real, single-session PostgreSQL reproduction
A literal transcript of SQL run against a live PostgreSQL instance in an isolated lab. The commands below are exactly what was executed.
- 1Write CREATE TABLE with PRIMARY KEY on two different columns.
- 2PostgreSQL processes the constraints and finds more than one primary key.
- 3The definition is rejected with SQLSTATE 42P16.
A schema declared PRIMARY KEY inline on two columns intending a composite key, and the table failed to create.
-- The error is self-contained in one statement; no schema is required.
SELECT 'no schema needed' AS setup_note;CREATE TABLE two_pk (a int primary key, b int primary key);SELECT 'ok' AS session_after_error;What PostgreSQL actually returned
setup_note
------------------
no schema needed
(1 row)ERROR: multiple primary keys for table "two_pk" are not allowed
LINE 1: CREATE TABLE two_pk (a int primary key, b int primary key);
^ session_after_error
---------------------
ok
(1 row)The table is created once it has a single primary key.
Without this
Before: two primary keys abort
With this, tested
After: a single primary key is accepted
- A second operational test: exact SQL, raw output, measured result, and engineer notes
Card required. Cancel before day 7 and you are not charged.
Runbook to fix this
The runbook for this incident
Full step-by-step fixes for the condition behind this error: the diagnosis, the exact SQL, and output captured in the lab.
Connected
Everything this error touches
Every page this SQLSTATE connects to: the concept that explains it, the runbooks that fix it, the parameters you tune to prevent it, and the sibling errors it travels with. All real cross-references. Jump straight in, or open the full interactive map.
Verification
- Last verified
- 2026-07-24 (isolated lab, PostgreSQL 18.4)
- Verification scope
- Verified against PostgreSQL 18.4 in an isolated lab environment
- Audit status
- reviewed
Went further?
Pro unlocks the second lab proof
Free page stops the bleeding. Pro adds the operational test, SQLSTATE audit, and deeper evidence, same error, more certainty.