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
andr...@visena.com <mailto:andr...@visena.com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
