Summary: in this tutorial, we will show you what the primary key is and how to manage PostgreSQL primary key constraints through SQL statements.
A primary key is a column or a group of columns that is used to identify a row uniquely in a table.
You define primary keys through primary key constraints. Technically, a primary key constraint is the combination of a not-null constraint and a UNIQUE constraint.
A table can have one and only one primary key. It is a good practice to add a primary key to every table. When you add a primary key to a table, PostgreSQL creates a unique btree index on the column or a group of columns used to define the primary key.
Define primary key when creating the table
Normally, we add the primary key to a table when we define the table’s structure using CREATE TABLE statement.
1 2 3 4 5 | CREATE TABLE TABLE ( column_1 data_type PRIMARY KEY, column_2 data_type, … ); |
The following statement creates a purchase order (PO) header table with the name po_headers.
1 2 3 4 5 6 | CREATE TABLE po_headers ( po_no INTEGER PRIMARY KEY, vendor_no INTEGER, description TEXT, shipping_address TEXT ); |
The po_no is the primary key of the po_headers table, which uniquely identifies purchase order in the po_headers table.
In case the primary key consists of two or more columns, you define the primary key constraint as follows:
1 2 3 4 5 6 | CREATE TABLE TABLE ( column_1 data_type, column_2 data_type, … PRIMARY KEY (column_1, column_2) ); |
For example, the following statement creates the purchase order line items table whose primary key is a combination of purchase order number ( po_no) and line item number ( item_no).
1 2 3 4 5 6 7 8 | CREATE TABLE po_items ( po_no INTEGER, item_no INTEGER, product_no INTEGER, qty INTEGER, net_price NUMERIC, PRIMARY KEY (po_no, item_no) ); |
If you don’t specify explicitly the name for primary key constraint, PostgreSQL will assign a default name to the primary key constraint. By default, PostgreSQL uses table name_pk as the default name for the primary key constraint. In this example, PostgreSQL creates the primary key constraint with the name po_items_pk for the po_items table.
In case you want to specify the name of the primary key constraint, you use CONSTRAINT clause as follows:
1 | CONSTRAINT constraint_name PRIMARY KEY(column_1, column_2,...); |
Define primary key when changing the existing table structure
It is rare to define a primary key for existing table. In case you have to do it, you can use the ALTER TABLE statement to add a primary key constraint.
1 | ALTER TABLE TABLE ADD PRIMARY KEY (column_1, column_2); |
The following statement creates a table named products without defining any primary key.
1 2 3 4 5 | CREATE TABLE products ( product_no INTEGER, description TEXT, product_cost NUMERIC ); |
Suppose you want to add a primary key constraint to the products table, you can execute the following statement:
1 2 | ALTER TABLE products ADD PRIMARY KEY (product_no); |
How to add auto-incremented primary key to existing table
Suppose, we have a vendors table that does not have any primary key.
1 | CREATE TABLE vendors (name VARCHAR(255)); |
And we add few rows to the vendors table using INSERT statement:
1 2 3 4 5 6 | INSERT INTO vendors (NAME) VALUES ('Microsoft'), ('IBM'), ('Apple'), ('Samsung'); |
To verify the insert operation, we query data from the vendors table using the following SELECT statement:
1 2 3 4 | SELECT * FROM vendors; |
Now, if we want to add a primary key named id into the vendors table and the id field is auto-incremented by 1, we use the following statement:
1 | ALTER TABLE vendors ADD COLUMN ID SERIAL PRIMARY KEY; |
Let’s check the vendors table again.
1 2 3 4 | SELECT id,name FROM vendors; |
Remove primary key
To remove an existing primary key constraint, you also use the ALTER TABLE statement with the following syntax:
1 | ALTER TABLE TABLE DROP CONSTRAINT primary_key_constraint; |
For example, to remove the primary key constraint of the products table, you use the following statement:
1 2 | ALTER TABLE table DROP CONSTRAINT products_pkey; |
In this tutorial, you have learned how to add and remove primary key constraints using CREATE TABLE and ALTER TABLE statements.