Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux