- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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 −
name | roll_no | marks_obtained | perc_marks | max_marks | date_of_entry |
---|---|---|---|---|---|
Yash | 26 | 42 | 42 | 100 | 2021-01-31 |
Isha | 56 | 175 | 87.5 | 200 | 2021-01-31 |
Yash | 35 | 12 | 24 | 50 | 2021-01-31 |
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.
- Related Articles
- 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?
- How can we apply the PRIMARY KEY constraint to the field of an existing MySQL table?
- Set existing column as Primary Key in MySQL?
- How to generate a “create table” command based on an existing table in MySQL?
- Create MySQL query to create a table from an existing table?
- How to create a table in PostgreSQL?
- How can we set PRIMARY KEY on multiple columns of a MySQL table?
- ALTER TABLE to add a composite primary key in MySQL?
- How to get primary key of a table in MySQL?
- How can I define a column of a MySQL table PRIMARY KEY without using the PRIMARY KEY keyword?
- How to make MySQL table primary key auto increment?
- Can we remove a primary key from MySQL table?
