Replacing an existing unique constraint (not UNIQUE INDEX) with primary key

[Andreas Joseph Krogh <andreas@xxxxxxxxxx>]

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

andreas@xxxxxxxxxx

www.visena.com