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

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

create UNIQUE INDEX person_entity_id_key on person(entity_id);