On 12/20/2016 03:03 AM, Andreas Joseph Krogh wrote:
På tirsdag 20. desember 2016 kl. 11:42:56, skrev Achilleas Mantzios
<achill@xxxxxxxxxxxxxxxxxxxxx <mailto:achill@xxxxxxxxxxxxxxxxxxxxx>>:
On 20/12/2016 12:27, Andreas Joseph Krogh wrote:
På tirsdag 20. desember 2016 kl. 11:02:27, skrev Achilleas
Mantzios <achill@xxxxxxxxxxxxxxxxxxxxx
<mailto:achill@xxxxxxxxxxxxxxxxxxxxx>>:
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@xxxxxxxxxx <mailto:andreas@xxxxxxxxxx>
www.visena.com <https://www.visena.com>
<https://www.visena.com>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
