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

On 20/12/2016 11:43, Andreas Joseph Krogh wrote:
> 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 personADD 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 (
> idBIGINT PRIMARY KEY,
> entity_idBIGINT NOT NULL UNIQUE,
> name VARCHAR NOT NULL );
>
> CREATE TABLE address (
> idBIGINT PRIMARY KEY,
> person_idBIGINT NOT NULL REFERENCES person (id)
> );
>
> CREATE TABLE phone (
> idBIGINT PRIMARY KEY,
> person_entity_idBIGINT 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 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
BEGIN;
ALTER TABLE person ADD CONSTRAINT person_pk PRIMARY KEY (entity_id);
alter table person drop constraint person_entity_id_key CASCADE;
alter table phone add CONSTRAINT phone_fk FOREIGN KEY (person_entity_id) REFERENCES person(entity_id);
alter table address add CONSTRAINT address_fk FOREIGN KEY (person_id) REFERENCES person(entity_id);
COMMIT;
> --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>
> 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_keyon 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(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
> www.visena.com <https://www.visena.com>
> <https://www.visena.com>

--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt