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