In this tutorial, we will be learning about the primary key constraint in PostgreSQL. It is one of the most important constraints that is used in any application out there in the world. So without wasting time, let’s get started!
Also Read: PostgreSQL TRUNCATE TABLE
What is a Primary Key?
In simple terms, the primary key constraint makes sure that each row in the table can be uniquely identified. Moreover, the column on which the primary key constraint is applied can not be null. This means the primary key is a combination of not null and the unique constraint.
Note that, you can set a single column to be a primary key as well as a combination of multiple columns. The primary key makes sure the data you will be inserting in the columns is unique and not null by default.
The table can have a minimum zero and a maximum of only one primary key. Even though it is not mandatory to have a primary key, it is essential in any application to have.
The Primary key plays the same role in almost any database management system, for example, MySQL, PostgreSQL, SQL Server etc. The only thing that changes is the syntax to define it.
How to Define a Primary Key in PostgreSQL?
As said earlier, you can define a primary key on a single column as well as a group of columns. There are multiple ways you can define a primary key in PostgreSQL. Let’s see them one by one.
Define Single-Column Primary Key
You can create a primary key on the column by using the PRIMARY KEY keyword in front of the column name while creating the table.
DROP TABLE IF EXISTS students;
CREATE TABLE students(
Id SERIAL PRIMARY KEY,
name VARCHAR(50),
birthDate DATE);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
As you can see in the highlighted area in the output, the description shows the primary key and the column name.
Related Article: How to Create a Table in PostgreSQL?
Define Multi-Column Primary Key
In the above method, we used the keyword PRIMARY KEY in front of the column name. But what if we have to specify the group of columns as the primary key? In this case, we use the CONSTRAINT keyword. Check out the example below to understand how it is used.
DROP TABLE IF EXISTS employees;
CREATE TABLE employees(
Id SERIAL,
employeeId INT,
departmentId INT,
PRIMARY KEY(employeeId,departmentId)
);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
In this syntax, you can use the PRIMARY KEY keyword with the column names in the parameter. PostgreSQL will automatically create the constraint name.
If you want to create the primary key with your own constraint name, you can replace this syntax with the following:
CONSTRAINT constraintName PRIMARY KEY(colNames);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Using this syntax, you can have control over the constraint name.
Define Primary Key on Existing Table
Till now we have seen steps to create the primary key on the new table. But now we will see how we can create the primary key on the existing table.
Note that, to create the primary key on the existing table, the specified column or group of columns must have unique data across the table.
First, create a table without a primary key.
DROP TABLE IF EXISTS students;
CREATE TABLE students(
Id SERIAL,
name VARCHAR(50),
birthDate DATE);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
As you can see, no primary key is present on the table. We can add the primary key using the ALTER TABLE command.
ALTER TABLE students
ADD PRIMARY KEY(Id);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
As you can see, the primary key has been added to the table. If you want to have a custom name for the primary key constraint, you can replace the PRIMARY KEY keyword in the above syntax to CONSTRAINT constraintName PRIMARY KEY.
Define Primary Key Without PRIMARY KEY Keyword
You can create the primary key on the table without using the PRIMARY KEY keyword. We have discussed earlier that the primary key is the combination of the UNIQUE and the NOT NULL constraint. Using these two constraints, we can create the primary key.
DROP TABLE IF EXISTS students;
CREATE TABLE students(
Id SERIAL UNIQUE NOT NULL,
name VARCHAR(50),
birthDate DATE);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
Note that, when you check the table schema, PostgreSQL will not show the constraint as a primary key because technically it is a unique key but using the base formula, we have made that column work as a primary key.
You can use the combination of the UNIQUE and NOT NULL constraints on as many as columns you want.
Remove Primary Key
If you want to remove the primary key from the table, you can use the ALTER TABLE statement. To remove the primary key constraint, you will need its name. You can use the following syntax to remove the primary key constraint.
ALTER TABLE tableName
DROP CONSTRAINT constraintName;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)Now let’s remove the primary key constraint that we created in the previous example.
ALTER TABLE students
DROP CONSTRAINT students_id_key;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)
As you can see, the primary key constraint has been removed from the ‘students’ table.
Conclusion
In this tutorial, we have learned what is a primary key and how to create it on a new as well as an existing table. We also learned how the primary key is made and what other ways are there to create the primary key-like constraint. Last but not least, we also learned to remove the primary key constraint from the table using the ALTER TABLE statement. It is very fun to play with such simple concepts in PostgreSQL. Happy coding!