PostgreSQL PRIMARY KEY constraint

PRIMARY KEY constraint

The PostgreSQL PRIMARY KEY is a column in a table which must contain a unique value which can be used to identify each and every row of a table uniquely. So it can be said that the PRIMARY KEY of a table is a combination of NOT NULL and UNIQUE constraint.

The function of PRIMARY KEY is same as UNIQUE constraint but the difference is one table can contain only one PRIMARY KEY though the table can contain one or more NOT NULL and UNIQUE constraints.

A PRIMARY KEY can be represented for one column or combination of columns.

No NULL value can not be accepted in PRIMARY KEY.

PostgreSQL PRIMARY KEY example

SQL

CREATE TABLE orders(
ord_no integer NOT NULL UNIQUE,
ord_date date,
item_name character(35),
item_grade character(1),
ord_qty numeric,
ord_amount numeric
);

The table structure

Constraint data dictionary

OR

CREATE TABLE orders(
ord_no integer PRIMARY KEY,
ord_date date,
item_name character(35),
item_grade character(1),
ord_qty numeric,
ord_amount numeric
);

The table structure

Constraint data dictionary

Explanation

The function of above two examples is same. From the first example shows the table orders have created with constraints of NOT NULL and UNIQUE on ord_no column. The function of the second example are same and shows the PRIMARY KEY have created on ord_no column and by default NOT NULL constraint have been set.

PostgreSQL PRIMARY KEY constraint group of columns

SQL

CREATE TABLE orders(
ord_no integer,
ord_date date,
item_name character(35),
item_grade character(1),
ord_qty numeric,
ord_amount numeric,
PRIMARY KEY (ord_no,item_name)
);

Output :

Constraint data dictionary

Explanation

The above example shows, the table orders have created with one PRIMARY KEY but this key represents the combination of ord_no and item_name columns.

PostgreSQL PRIMARY KEY constraint with constraint name

SQL

CREATE TABLE orders(
ord_no integer,
ord_date date,
item_name character(35),
item_grade character(1),
ord_qty numeric,
ord_amount numeric,
CONSTRAINT ordno_itemname PRIMARY KEY (ord_no,item_name)
);

Output :

Constraint data dictionary

Explanation

The above example shows, the table orders have created with one PRIMARY KEY as named ordno_itemname, and this key represents the combination of ord_no and item_name columns.