[GENERAL] Replacing an existing unique constraint (not UNIQUE INDEX) with primary key - Mailing list pgsql-general

From Andreas Joseph Krogh
Subject [GENERAL] Replacing an existing unique constraint (not UNIQUE INDEX) with primary key
Date
Msg-id VisenaEmail.20.a1f84b5cfbe5c12d.1591b8bcbaa@tc7-visena
Whole thread Raw
Responses Re: [GENERAL] Replacing an existing unique constraint (not UNIQUE INDEX) with primary key
List pgsql-general
Hi all.
 
For historical reasons I have a table which at first had an "id"-column (the PK) and later got an "entity_id"-column (which is a UNIQUE CONSTRAINT).
 
I'm now trying to get rid of the "id"-column and make the "entity_id"-column the new PK. The tricky part is that both of these columns are referenced as FK's from many tables, so disabling/removing FKs is not so easy. I'm facing a problem when issuing:
ALTER TABLE person ADD PRIMARY KEY USING INDEX person_entity_id_key;
ERROR:  index "person_entity_id_key" is already associated with a constraint
 
A full example of what I'm trying to do (replacing the PK of the "person"-table) is here:

DROP TABLE IF EXISTS phone;
DROP TABLE IF EXISTS address;
DROP TABLE IF EXISTS person;
CREATE TABLE person (    id        BIGINT PRIMARY KEY,    entity_id BIGINT  NOT NULL UNIQUE,    name      VARCHAR NOT NULL
);

CREATE TABLE address (    id        BIGINT PRIMARY KEY,    person_id BIGINT NOT NULL REFERENCES person (id)
);

CREATE TABLE phone (    id               BIGINT PRIMARY KEY,    person_entity_id BIGINT  NOT NULL REFERENCES person (entity_id),    number           VARCHAR NOT NULL
);

INSERT INTO person (id, entity_id, name) VALUES (1, 101, 'Andreas'), (2, 102, 'Santa');
INSERT INTO address (id, person_id) VALUES (1, 1), (2, 2);
INSERT INTO phone (id, person_entity_id, number) VALUES (1, 101, '1800555123'), (2, 102, '1800555456');

-- Drop the deprecated foreign key on address
ALTER TABLE address    DROP CONSTRAINT address_person_id_fkey;

-- Update address and make person_id point to person.entity_id instead of person.id
UPDATE address a
SET person_id = p.entity_id
FROM person p
WHERE p.id = a.person_id;
ALTER TABLE address    ADD FOREIGN KEY (person_id) REFERENCES person (entity_id);

-- Drop the deprecated id-column
ALTER TABLE person    DROP COLUMN id;

-- Try to make new PK using the UNIQUE CONSTRAINT person_entity_id_key
ALTER TABLE person    ADD PRIMARY KEY USING INDEX person_entity_id_key;

ERROR:  index "person_entity_id_key" is already associated with a constraint
 

 
I see that if I had declared person.entity_id without the UNIQUE-keyword and instead created a UNIQUE INDEX:
create UNIQUE INDEX person_entity_id_key on person(entity_id);
 
Then the ADD PRIMARY KEY USING INDEX command would have succeeded.
 
I have lots of queries which have GROUP BY person.id which now should use GROUP BY person.entity_id, and not having to also list all other columns selected from the person-table.
 
How do I proceed with this?
 
Thanks.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Attachment

pgsql-general by date:

Previous
From:
Date:
Subject: Re: [GENERAL] Postgres 9.6 Streaming Replication on Solaris 10
Next
From: Achilleas Mantzios
Date:
Subject: Re: [GENERAL] Replacing an existing unique constraint (not UNIQUE INDEX) with primary key