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

On 12/20/2016 03:03 AM, Andreas Joseph Krogh wrote:
> På tirsdag 20. desember 2016 kl. 11:42:56, skrev Achilleas Mantzios
> <achill(at)matrix(dot)gatewaynet(dot)com <mailto:achill(at)matrix(dot)gatewaynet(dot)com>>:
>
> On 20/12/2016 12:27, Andreas Joseph Krogh wrote:
>> På tirsdag 20. desember 2016 kl. 11:02:27, skrev Achilleas
>> Mantzios <achill(at)matrix(dot)gatewaynet(dot)com
>> <mailto:achill(at)matrix(dot)gatewaynet(dot)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(at)visena(dot)com <mailto:andreas(at)visena(dot)com>
> www.visena.com <https://www.visena.com>
> <https://www.visena.com>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com