Create Primary Key on an existing table in PostgreSQL?

Although quite infrequent, you may come across situations wherein you need to define the primary key on an existing table. This can be achieved using the ALTER TABLE statement.

The syntax is −

ALTER TABLE table_name ADD PRIMARY KEY (column_name1,
column_name2,…., columns_nameN)

As can be seen from the above syntax, you can define PRIMARY KEY on multiple columns. When you have defined the PRIMARY KEY on multiple columns, the condition is that the column pairs should have unique and non-null values. Thus, if the PRIMARY KEY is defined on (column1, column2), the values (value1, value2), (value3, value2), and (value1,value4) are allowed. Even though column1 has value1 repeated and column2 has value2 repeated, the pair of columns have unique values when considered together.

Let us consider an example to understand this. Let us create a table marks and populate it as follows −

CREATE TABLE marks(
   name VARCHAR,
   roll_no INTEGER,
   marks_obtained INTEGER,
   perc_marks DOUBLE PRECISION,
   max_marks INTEGER,
   date_of_entry DATE
);
INSERT INTO marks(name, roll_no, marks_obtained, perc_marks,
max_marks, date_of_entry)
VALUES ('Yash', 26, 42, 42.0, 100, current_date),
('Isha', 56, 175, 87.5, 200, current_date),
('Yash', 35, 12, 24, 50, current_date);

If you query the table (SELECT * from marks2), you will see output similar to the following −

This table has no primary key set so far. Now, let us try to define the name column as the primary key.

ALTER TABLE marks ADD PRIMARY KEY (name)

PostgreSQL will return an error −

ERROR: could not create unique index "marks_pkey" DETAIL: Key (name)=(Yash) is
duplicated. SQL state: 23505

This is as expected. PRIMARY KEY can’t have duplicate values. Now, let us try to set the PRIMARY KEY on the (name, roll_no) pair.

ALTER TABLE marks ADD PRIMARY KEY (name, roll_no)

This will work because no combination of (name, roll_no) is duplicate.

Yash Sanghvi

Published on 02-Feb-2021 13:09:05

• Related Questions & Answers
• How can we set PRIMARY KEY on multiple columns of an existing MySQL table?
• How can we remove composite PRIMARY KEY constraint applied on multiple columns of an existing MySQL table?
• How to add column to an existing table in PostgreSQL?
• How can we remove PRIMARY KEY constraint from a column of an existing MySQL table?
• Set existing column as Primary Key in MySQL?
• How to rename an existing column of a table in PostgreSQL?
• How can we apply the PRIMARY KEY constraint to the field of an existing MySQL table?
• Create MySQL query to create a table from an existing table?
• How to create a table in PostgreSQL?
• How to generate a “create table” command based on an existing table in MySQL?
• How can we set PRIMARY KEY on multiple columns of a MySQL table?
• Primary key Vs Unique key
• Changing the primary key on a MongoDB collection?
• How to make MySQL table primary key auto increment?
• Can we remove a primary key from MySQL table?