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 17:39:05