Re: [GENERAL] Replacing an existing unique constraint (not UNIQUEINDEX) 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@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