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

Achilleas Mantzios <achill@xxxxxxxxxxxxxxxxxxxxx> · Tue, 20 Dec 2016 12:02:27 +0200



    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 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
      
    
    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_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
          
        
    -- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt