> ## Content Index
> Fetch the complete content index at: https://blog.jsinh.in/llms.txt
> Use this file to discover other available public pages before exploring further.

# Primary key constraint on table in PostgreSQL
- URL: https://blog.jsinh.in/primary-key-constraint-on-table-in-postgresql/
- Published: 2015-01-05T19:35:00.000Z
- Updated: 2026-07-07T15:08:40.000Z
- Author: Jsinh
- Tags: #Import 2026-10-04 19:16

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](https://www.pgadmin.org/?ref=blog.jsinh.in), it displayed hint (warning, I would say) as

> Edit table data without primary key

![Edit table data without primary key - pgAdmin III hint / warning](https://blob.jsinh.in/media/2015/01/06/SS01-PrimaryKeyPostgreSQL.png)

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](https://www.postgresql.org/docs/8.4/static/datatype-oid.html?ref=blog.jsinh.in) 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:

![Could not create unique index "pk_cities" on city column due to duplicate values](https://blob.jsinh.in/media/2015/01/06/SS02-PrimaryKeyPostgreSQL.png)

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](https://www.webucator.com/database/postgresql.cfm?ref=blog.jsinh.in) by [Webucator](https://www.webucator.com/?ref=blog.jsinh.in), thanks!

Watch out other free courses on their [YouTube channel](https://www.youtube.com/user/WebucatorInc/feed?ref=blog.jsinh.in)