Postgres Pro
Downloads
Home   >   mailing lists

Re: [GENERAL] Replacing an existing unique constraint (not UNIQUE INDEX) with primary key - Mailing list pgsql-general

From Andreas Joseph Krogh
Subject Re: [GENERAL] Replacing an existing unique constraint (not UNIQUE INDEX) with primary key
Date
Msg-id VisenaEmail.25.b9c2bfd5968f8f3b.1591be44345@tc7-visena
Whole thread Raw
In response to Re: [GENERAL] Replacing an existing unique constraint (not UNIQUE INDEX) with primary key  (Achilleas Mantzios <achill@matrix.gatewaynet.com>)
Responses Re: [GENERAL] Replacing an existing unique constraint (not UNIQUEINDEX) with primary key
List pgsql-general
Tree view
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

pgsql-general by date:

Previous
From: Mickaël Le Baillif
Date:
Subject: Re: [GENERAL] JSON objects merge using || operator
Next
From: Adrian Klaver
Date:
Subject: Re: [GENERAL] Replacing an existing unique constraint (not UNIQUEINDEX) with primary key