Postgres Pro
Downloads
Home   >   mailing lists

Thread: [GENERAL] Replacing an existing unique constraint (not UNIQUE INDEX) with primary key

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

From
Andreas Joseph Krogh
Date:
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
Attachment

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

From
Achilleas Mantzios
Date:
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


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

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

From
Andreas Joseph Krogh
Date:
På tirsdag 20. desember 2016 kl. 11:02:27, skrev Achilleas Mantzios <achill@matrix.gatewaynet.com>:
On 20/12/2016 11:43, Andreas Joseph Krogh wrote:
[snip]
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;
 
Yea, I was hoping to avoid having to manually add the FK's to the referencing tables (34).
Is there really no way to accomplish this without DROP CONSTRAINT ...  CASCADE, hacking the system-catalogs or something?
 
Thanks.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Attachment

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

From
Achilleas Mantzios
Date:
On 20/12/2016 12:27, Andreas Joseph Krogh wrote:
På tirsdag 20. desember 2016 kl. 11:02:27, skrev Achilleas Mantzios <achill@matrix.gatewaynet.com>:
On 20/12/2016 11:43, Andreas Joseph Krogh wrote:
[snip]
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;
 
Yea, I was hoping to avoid having to manually add the FK's to the referencing tables (34).
Is there really no way to accomplish this without DROP CONSTRAINT ...  CASCADE, hacking the system-catalogs or something?
 
You may write a script to output those 34 FK constraints. Definitely safer than hacking pg_constraint.conindid .
Thanks.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 


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

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

From
Andreas Joseph Krogh
Date:
På tirsdag 20. desember 2016 kl. 11:42:56, skrev Achilleas Mantzios <achill@matrix.gatewaynet.com>:
On 20/12/2016 12:27, Andreas Joseph Krogh wrote:
På tirsdag 20. desember 2016 kl. 11:02:27, skrev Achilleas Mantzios <achill@matrix.gatewaynet.com>:
On 20/12/2016 11:43, Andreas Joseph Krogh wrote:
[snip]
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;
 
Yea, I was hoping to avoid having to manually add the FK's to the referencing tables (34).
Is there really no way to accomplish this without DROP CONSTRAINT ...  CASCADE, hacking the system-catalogs or something?
 
You may write a script to output those 34 FK constraints. Definitely safer than hacking pg_constraint.conindid 
 
Yes.
 
I'd still argue that what I'm trying to do should "just work" as PG treats UNIQUE CONSTRAINT and UNIQUE INDEX the same wrt. the planner and FK-enforcement.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Attachment

Re: [GENERAL] Replacing an existing unique constraint (not UNIQUEINDEX) with primary key

From
Adrian Klaver
Date:
On 12/20/2016 03:03 AM, Andreas Joseph Krogh wrote:
> På tirsdag 20. desember 2016 kl. 11:42:56, skrev Achilleas Mantzios
> <achill@matrix.gatewaynet.com <mailto:achill@matrix.gatewaynet.com>>:
>
>     On 20/12/2016 12:27, Andreas Joseph Krogh wrote:
>>     På tirsdag 20. desember 2016 kl. 11:02:27, skrev Achilleas
>>     Mantzios <achill@matrix.gatewaynet.com
>>     <mailto:achill@matrix.gatewaynet.com>>:
>>
>>         On 20/12/2016 11:43, Andreas Joseph Krogh wrote:
>>>         [snip]
>>         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;
>>
>>
>>     Yea, I was hoping to avoid having to manually add the FK's to the
>>     referencing tables (34).
>>     Is there really no way to accomplish this without DROP CONSTRAINT
>>     ...  CASCADE, hacking the system-catalogs or something?
>>
>     You may write a script to output those 34 FK constraints. Definitely
>     safer than hacking pg_constraint.conindid
>
>
> Yes.
>
> I'd still argue that what I'm trying to do should "just work" as PG
> treats UNIQUE CONSTRAINT and UNIQUE INDEX the same wrt. the planner and
> FK-enforcement.

Close as I can come:

test=# ALTER TABLE person
     ADD CONSTRAINT person_pkey PRIMARY KEY (entity_id);
ALTER TABLE


test=# \d person
            Table "public.person"
   Column   |       Type        | Modifiers
-----------+-------------------+-----------
  entity_id | bigint            | not null
  name      | character varying | not null
Indexes:
     "person_pkey" PRIMARY KEY, btree (entity_id)
     "person_entity_id_key" UNIQUE CONSTRAINT, btree (entity_id)
Referenced by:
     TABLE "address" CONSTRAINT "address_person_id_fkey" FOREIGN KEY
(person_id) REFERENCES person(entity_id)
     TABLE "phone" CONSTRAINT "phone_person_entity_id_fkey" FOREIGN KEY
(person_entity_id) REFERENCES person(entity_id)


Though you cannot DROP the original constraint index until you change
what the FKs point to. It buys you time to do that though.

test=# ALTER TABLE person DROP CONSTRAINT person_entity_id_key;
ERROR:  cannot drop constraint person_entity_id_key on table person
because other objects depend on it
DETAIL:  constraint phone_person_entity_id_fkey on table phone depends
on index person_entity_id_key
constraint address_person_id_fkey on table address depends on index
person_entity_id_key
HINT:  Use DROP ... CASCADE to drop the dependent objects too.


>
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andreas@visena.com <mailto:andreas@visena.com>
> www.visena.com <https://www.visena.com>
> <https://www.visena.com>
>


--
Adrian Klaver
adrian.klaver@aklaver.com