PrimaryKey on existing Unique index in PostgreSQL Database

• PrimaryKey on existing Unique index in PostgreSQL Database

Indeed PrimaryKey is most important to force data uniqueness in the database table. Specifically, PostgreSQL database allow to create PrimaryKey on a single column and on a combination of candidate columns as well. Furthermore, the Later case is known as composite primary key.
When we create PrimaryKey , firstly it internally create a unique index in the table, afterwards PrimaryKey got created. Therefore, PrimaryKey and a unique index, both items got created simultaneously in one operation.
Additionally, You can use existing unique key index rather than creating a new index. To conclude, I have prepared below mentioned steps in very simple manner. Equally important, I have prepared the corresponding video tutorial too. Therefore, you can watch video instructions for the same purpose.
Finally, I hope this tutorial will be helpful for you.

Firstly, Let’s Create test table students

postgres=# create table students ( id numeric, name varchar(15) ) ;
CREATE TABLE
postgres=#

Secondly, Let’s create unique index on students table

postgres=# create unique index unique_students on students (id) ;
CREATE INDEX
postgres=#

Next, let’s check the table structure

postgres=# \d students
Table “public.students”
Column | Type | Modifiers
——–+———————–+———–
id | numeric |
name | character varying(15) |
Indexes:
“unique_students” UNIQUE, btree (id)

Thirdly, Let’s insert some sample data

postgres=# 
postgres=# insert into students values (1,’Steve’) ;
INSERT 0 1
postgres=# insert into students values (2,’Bill’) ;
INSERT 0 1
postgres=# insert into students values (3,’Charlie’) ;
INSERT 0 1
postgres=# insert into students values (3,’Cissi’) ;
ERROR: duplicate key value violates unique constraint “unique_students”
DETAIL: Key (id)=(3) already exists.

Now, the last insert statement failed because we have unique key constraint on id column of our table.
Before, we create PrimaryKey, let’s review the records in students table

postgres=# select * from students ;
id | name
—-+———
1 | Steve
2 | Bill
3 | Charlie
(3 rows)

Finally, Let’s create primary key on id column of students table using existing Unique Index.

postgres=# <strong</strong>
postgres=# alter table students add constraint pkey_students primary key using index unique_students;
NOTICE: ALTER TABLE / ADD CONSTRAINT USING INDEX will rename index “unique_students” to “pkey_students”
ALTER TABLE
postgres=# \d students
Table “public.students”
Column | Type | Modifiers
——–+———————–+———–
id | numeric | not null
name | character varying(15) |
Indexes:
“pkey_students” PRIMARY KEY, btree (id)

postgres=# <strong>‐‐</strong></span></code></pre>

In the end, as you have confirmed from table structure,You have successfully created PrimaryKey on existing Unique index in PostgreSQL Database.

Additionally, checkout more PostgreSQL posts

• 
• 
• 
•