A table without a primary key looks harmless when you create it. The trouble shows up later, once the table has grown, replication is running, and something downstream depends on each row being uniquely identifiable. By then the missing primary key is no longer a cosmetic detail. It's the reason your duplicates are creeping in, your CDC pipeline is dropping events, or your replica has quietly stopped catching up.
Bytebase SQL Review includes the rule:
Bytebase considers this rule to be violated if the SQL tries to create a no primary key table or drop the primary key. If the SQL drops all columns in the primary key, Bytebase also considers that this SQL drops the primary key.
In other words, it catches both the CREATE TABLE that forgets a primary key and the ALTER TABLE that removes one, before either reaches production.
Real Incidents Caused by Missing Primary Keys
This is not a theoretical concern. The same failure has been reported across different databases and tools.
PostgreSQL logical replication breaks. Without a primary key, Postgres has no way to identify which row an update or delete applies to, so it can't replay those changes during logical replication (or through Debezium). See TIL: Creating tables without primary keys CAN cause updates and deletes to fail in Postgres for a walkthrough.
Matomo production replication stalled. A single table with no primary key was enough to stop MySQL master-slave replication. The thread is here: Master-Slave Replication Stalls Because of Missing Primary Key.
GitLab reported schema inconsistencies. GitLab engineers traced environment drift and ongoing maintenance pain back to tables that lacked primary keys: Database schema missing many primary keys - breaks replication.
Why Missing Primary Keys Are Dangerous
1. Duplicate rows slip in
A primary key is what lets the database enforce uniqueness. Without one, nothing stops the same logical record from being inserted twice, and accidental duplicates quietly corrupt your analytics and reports.
2. CDC systems can't track row changes
Change-data-capture tools like Debezium and Kafka Connect need a stable identity for each row. If there's no primary key, they have no reliable way to emit the correct update and delete events.
3. Replication may stop or diverge
Both MySQL and PostgreSQL lean on the primary key during replication. A missing one can stall the replica or let it drift out of sync with the source, which is exactly what happened in the Matomo and GitLab cases above.
4. Upserts don't work correctly
INSERT … ON CONFLICT, MERGE, and the various UPSERT patterns all depend on a primary key to decide what counts as a conflict. Without one, the database can't resolve those conflicts reliably.
5. Debugging becomes guesswork
When you need to delete, fix, or investigate a single record, you need a way to point at exactly that row. With no unique identifier, every targeted operation becomes a gamble.
How to Fix Tables Without Primary Keys?
There's no single right answer here. The best key depends on whether your table already has a natural unique value to lean on.
1. Add a surrogate primary key
When there's no obvious natural key, a generated identifier is the simplest option:
ALTER TABLE events
ADD COLUMN id BIGSERIAL PRIMARY KEY;2. Use a natural composite key if appropriate
If a combination of existing columns is already guaranteed unique, you can promote it directly:
ALTER TABLE order_items
ADD PRIMARY KEY (order_id, line_number);3. Combine surrogate PK with a unique business key
Often you want both: a stable surrogate key for joins and replication, plus a unique constraint to protect the business value:
ALTER TABLE shipments
ADD COLUMN id BIGSERIAL PRIMARY KEY,
ADD CONSTRAINT shipments_unique UNIQUE (tracking_number);