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_idBIGINT NOT NULL REFERENCES person (id) ); CREATE TABLE phone ( id BIGINT PRIMARY KEY, person_entity_id BIGINT NOT NULL REFERENCES person (entity_id), numberVARCHAR 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 addressALTER TABLE address DROP CONSTRAINT address_person_id_fkey;-- Update address and make person_id point to person.entity_id instead of person.idUPDATE address a SET person_id = p.entity_idFROM person p WHERE p.id = a.person_id; ALTER TABLE address ADD FOREIGN KEY(person_id) REFERENCES person (entity_id); -- Drop the deprecated id-columnALTER TABLE person DROP COLUMN id; -- Try to make new PK using the UNIQUE CONSTRAINT person_entity_id_keyALTER TABLE person ADD PRIMARY KEY USING INDEXperson_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 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>
