Menu
Is there any drawback to not having a primary key for a table in Postgres? Since all data is stored unordered in the heap anyway, is the primary key just a way to enforce a unique key and an index at the same time? Or is there a fundamental feature that a primary key provides in a table as opposed to a table that does not have a primary key?
936
A table with no primary key will only send out INSERTs on the logical decoding stream; UPDATEs and DELETEs are lost. Reading the postgres docs at postgresql.org/docs/10/static/…
Every table can have (but does not have to have) a primary key. The column or columns defined as the primary key ensure uniqueness in the table; no two rows can have the same key. The primary key of one table may also help to identify records in other tables, and be part of the second table's primary key.
By simply setting our id column as SERIAL with PRIMARY KEY attached, Postgres will handle all the complicated behind-the-scenes work and automatically increment our id column with a unique, primary key value for every INSERT .
A primary key is a field in a table, which uniquely identifies each row/record in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values.
Per the Postgres documentation (http://www.postgresql.org/docs/9.2/static/sql-createtable.html):
Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL, but identifying a set of columns as primary key also provides metadata about the design of the schema, as a primary key implies that other tables can rely on this set of columns as a unique identifier for rows.
From my experience, I have created plenty of tables without them. But some replication solutions require there be a primary key, or at the single column identifier per row.
152
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
