w3resource logo


postgresql primary key

PostgreSQL PRIMARY KEY constraint

rating Average rating 7 out of 10. Total 11 users rated.

<<PreviousNext>>

Description

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 represent 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

postgresql primary key constraint example1

Constraint data dictionary

postgresql primary key constraint data dictionary1

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

postgresql primary key constraint example2

Constraint data dictionary

postgresql primary key constraint data dictionary2

Explain

The function of above two examples are 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 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

postgresql primary key constraint example3

Constraint data dictionary

postgresql primary key constraint data dictionary3

Explain

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

postgresql primary key constraint example4

Constraint data dictionary

postgresql primary key constraint data dictionary4

Explain

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.



<<PreviousNext>>