Primary key constraint on table in PostgreSQL

Jaspalsinh Chauhan -

When you design your table, you choose to add a primary key constraint for one or more column while create the same.

Column attached with primary key constraint should contain UNIQUE and NOT NULL values. We all know that.

But PostgreSQL does not force this constraint while creating a new table, you might forget to add one (missing morning coffee may be).

I tried creating a table without adding primary key constraint in PostgreSQL

CREATE TABLE weather (
	city character varying(80),
    temp_lo integer,
    temp_hi integer,
    prcp real,
    date date
);

This took off and created the weather table successfully. Now when I tried to view data (which I inserted after creating the table) using pgAdmin III, it displayed hint (warning, I would say) as

Edit table data without primary key

This warning message say, I will not be able to edit or change existing row data since I do not have primary key or OIDs defined on this table.

You can alter table definition and add a primary key constraint now but it won't create on columns which does not have unique data already or if the column have NULL values. Would probably display an error similar to this:

If you try adding a new column and attached primary key constraint to it with ALTER TABLE query, it would give similar error too. Because the existing rows will have NULL values while recreating the table with new column added.

ALTER TABLE weather ADD COLUMN cityid INTEGER PRIMARY KEY;

Outputs: ERROR: column "cityid" contains null values

There is an exception to this, if you create a new column and add primary key constraint to it. It will not throw error if the new column's datatype is of type SERIAL.

ALTER TABLE weather ADD COLUMN cityid SERIAL PRIMARY KEY;

This will alter table successfully, as SERIAL datatype column will have integer values which are auto-incremented for each new row added to the table. When adding this column, for all existing rows it would add incremented value automatically.

What can go wrong if you do not add any PRIMARY KEY CONSTRAINT to a table (my opinion, feel free to comment and add more disadvantage if you know any):

  • You might find it difficult to add FOREIGN KEY constraint (column at-least needs to have UNIQUE KEY constraint)
  • Does not gaurentees uniqueness of rows in this table
  • Data is view only (value / data cannot be updated or edited)
  • Your ORM (if does not support tables without PRIMARY KEY) may go bananas and also your manager o.O

Designing and creating TABLES without PRIMARY KEY constraint, not recommended so make sure you have one for the table you creating.

Above topic is also explained as part of PostgreSQL Online and Onsite Training Classes series by Webucator, thanks!

Watch out other free courses on their YouTube channel