Home » Technology » PostgreSQL » PostgreSQL – Add primary key using existing unique index

PostgreSQL – Add primary key using existing unique index

postgres=# — Create test table students
postgres=#
postgres=# create table students ( id numeric, name varchar(15) ) ;
CREATE TABLE
postgres=#
postgres=# — create unique index on students table.
postgres=# create unique index unique_students on students (id) ;
CREATE INDEX
postgres=#
postgres=# — check table structure
postgres=# \d students
Table “public.students”
Column | Type | Modifiers
——–+———————–+———–
id | numeric |
name | character varying(15) |
Indexes:
“unique_students” UNIQUE, btree (id)

postgres=#
postgres=# — insert some sample data
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.
postgres=# —- last insert statement failed because we have unique key contraint on id column of our table. ;
postgres=# — review the records in students table
postgres=# select * from students ;
id | name
—-+———
1 | Steve
2 | Bill
3 | Charlie
(3 rows)

postgres=# — Now, Let’s create primary key on id column of students table.
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=# — as you can confirm from table structure, primary key got created successfully and index name also changed to same as primary key

Other useful PostgreSQL posts –

Leave a Reply

Your email address will not be published. Required fields are marked *