Preamble
You will learn how to create, drop, disable, and enable a primary key in PostgreSQL with syntax and examples.
What is the primary key in PostgreSQL?
In PostgreSQL, a primary key is a single field or a combination of fields that uniquely define a record. None of the fields that are part of the primary key can contain the value NULL. A table can have only one primary key.
Note:
- In PostgreSQL, the primary key is created using the CREATE TABLE operator or ALTER TABLE operator.
You use ALTER TABLE in PostgreSQL to add or delete the primary key.
Create a primary key with the CREATE TABLE operator
You can create a primary key in PostgreSQL using the CREATE TABLE operator.
The syntax for creating a primary key with CREATE TABLE in PostgreSQL
CREATE TABLE table_name
(
column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],
…
CONSTRAINT constraint_name
PRIMARY KEY (index_col1, index_col2,... index_col_n)
);
OR
CREATE TABLE table_name
(
column1 datatype CONSTRAINT constraint_name PRIMARY KEY,
column2 datatype [ NULL | NOT NULL ],
…
);
- table_name – The name of the table you want to create.
- column1, column2 – The columns you want to create in the table.
- Table_name – Name of the primary key.
- index_col1, index_col2,… index_col_n – The columns that make up the primary key.
Consider an example of how to create a primary key using the CREATE TABLE operator in PostgreSQL.
CREATE TABLE order_details
( order_detail_id integer NOT NULL,
order_id integer NOT NULL,
order_date date,
size integer,
notes varchar(200),
CONSTRAINT order_details_pk PRIMARY KEY (order_detail_id)
);
Or you can also create a primary key in the order_details table using the following syntax:
CREATE TABLE order_details
( order_detail_id integer CONSTRAINT order_details_pk PRIMARY KEY,
order_id integer NOT NULL,
order_date date,
size integer,
notes varchar(200)
);
In these two examples, we created a primary key in the order_details table named order_details_pk. It consists of only one column, the order_detail_id column.
We can also create a primary key with more than one field, as in the example below:
CREATE TABLE order_details
( order_date date NOT NULL,
customer_id integer NOT NULL,
size integer,
notes varchar(200),
CONSTRAINT order_details_pk PRIMARY KEY (order_date, customer_id)
);
In this example, a primary key is created with the name order_details_pk, which consists of a combination of order_date and customer_id columns. Thus, each combination of order_date and customer_id must be unique in the order_details table.
Create a primary key using the ALTER TABLE operator
You can create a primary key in PostgreSQL using the ALTER TABLE operator.
The syntax for creating a primary key using the ALTER TABLE operator in PostgreSQL
ALTER TABLE table_name
ADD CONSTRAINT [ constraint_name ]
PRIMARY KEY (index_col1, index_col2,... index_col_n)
- table_name – The name of the table to change.
- constraint_name – The name of the primary key.
- index_col1, index_col2,… index_col_n – The columns that make up the primary key.
Consider an example of how to create a primary key using the ALTER TABLE operator in PostgreSQL.
ALTER TABLE order_details
ADD CONSTRAINT order_details_pk
PRIMARY KEY (order_detail_id);
In this example, we created a primary key for an existing order_details table named order_details_pk. It consists of an order_detail_id column.
We can also create a primary key with more than one field, as in the example below:
ALTER TABLE order_details
ADD CONSTRAINT order_details_pk
PRIMARY KEY (order_date, customer_id);
In this example, we have created a primary contact_pk key which consists of a combination of last_name and first_name columns.
Drop the primary key
You can delete a primary key in PostgreSQL using the ALTER TABLE operator.
The syntax for removing the primary key in PostgreSQL
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
- table_name – The name of the table to change.
- constraint_name – The name of the primary key that you want to delete.
Let’s consider an example of how to delete a primary key using the ALTER TABLE operator in PostgreSQL.
ALTER TABLE order_details
DROP CONSTRAINT order_details_pk;
In this example, we have removed the primary key from the contacts table. We do not need to give the name of the primary key, because there can only be one key in the table.
PostgreSQL Primary Key | Course
About Enteros
IT organizations routinely spend days and weeks troubleshooting production database performance issues across multitudes of critical business systems. Fast and reliable resolution of database performance problems by Enteros enables businesses to generate and save millions of direct revenue, minimize waste of employees’ productivity, reduce the number of licenses, servers, and cloud resources and maximize the productivity of the application, database, and IT operations teams.
The views expressed on this blog are those of the author and do not necessarily reflect the opinions of Enteros Inc. This blog may contain links to the content of third-party sites. By providing such links, Enteros Inc. does not adopt, guarantee, approve, or endorse the information, views, or products available on such sites.
Are you interested in writing for Enteros’ Blog? Please send us a pitch!
RELATED POSTS
Optimizing Budgeting and Cost Allocation in the Finance Sector with Enteros: A Smarter Approach to Financial Efficiency
- 12 November 2024
- Database Performance Management
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Enteros and Cloud FinOps: Unleashing Big Data Potential for eCommerce Profitability
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Strengthening Financial Operations: How Enteros and Cloud FinOps Transform Balance Sheet Management and Root Cause Analysis
- 10 November 2024
- Database Performance Management
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…
Optimizing Database Management with Enteros: Revolutionizing RevOps, AIOps, and DevOps on Azure
In the fast-evolving world of finance, where banking and insurance sectors rely on massive data streams for real-time decisions, efficient anomaly man…