Batch movement of child records with primary key violation (merge of child records)
<p>In PostgreSQL 9.5, I have a table: A</p>

<p>Table A has two properties as:</p>

<ol>
<li>recid -- the primary key for the table (serial)</li>
<li>description -- a unique key for the table (text)</li>
</ol>

<p>The primary key of table A is used as a foreign key by other tables with the definition of:</p>

<pre><code>CONSTRAINT fk FOREIGN KEY (A_recid)
      REFERENCES A (recid) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT
</code></pre>

<p>I need to change the description in record 1 in table A to an already existing description in record 2 and delete record 1 to keep the descriptions unique for the table.</p>

<p>Record 1 in table A will have child records in other tables which will need to be moved to the new recid of record 2 according to the cascading update of the foreign key constraint.</p>

<p>How is this done? Could writable CTE's be used?</p>

<p>(If necessary, I am not opposed to changing the table design).</p>

<p>Much thanks for any suggestions. </p>

<p>Edit #1 The full table declaration for the parent table is:</p>

<pre><code>CREATE TABLE cpt
(
  recid serial NOT NULL,
  code text,
  cdesc text NOT NULL,
  modified timestamp without time zone DEFAULT now(),
  procedure_type text,
  CONSTRAINT pk_cpt_recid PRIMARY KEY (recid),
  CONSTRAINT cpt_noduplicate UNIQUE (cdesc)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE cpt
  OWNER TO postgres;
</code></pre>

<p>And example of a child table (one of many tables):</p>

<pre><code>CREATE TABLE cpt_invoice
(
  recid serial NOT NULL,
  cpt_recid integer NOT NULL,
  ninsurance numeric(10,2),
  ncash numeric(10,2),
  mustschedule boolean,
  doneinoffice boolean,
  common boolean,
  "timestamp" timestamp without time zone DEFAULT now(),
  modified timestamp without time zone DEFAULT now(),
  CONSTRAINT pk_cpt_invoice_recid PRIMARY KEY (recid),
  CONSTRAINT cs_cpt_invoice FOREIGN KEY (cpt_recid)
      REFERENCES cpt (recid) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE,
  CONSTRAINT cs_unique_cpt_invoice UNIQUE (cpt_recid)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE cpt_invoice
  OWNER TO postgres;
</code></pre>

<p>Edit #2: This is kind-of what I'm hoping for. But this will not work with error:</p>

<p>ERROR:  update or delete on table "cpt" violates foreign key constraint "nursebilling_cpt_fk" on table "nursebilling" DETAIL:  Key (recid)=(459) is still referenced from table "nursebilling".</p>

<pre><code>with plana as (  -- get snapshot of original record
    select * from cpt where recid = 459
)
, planb as (  -- change the primary key on snapshot -- I am hoping this will be cascaded
    update cpt
    set recid = 2
    from plana
    where cpt.recid = plana.recid
)   
, planc as ( -- delete the one-to-one record in cpt_invoice
    delete from cpt_invoice
    using planA 
    where cpt_invoice.cpt_recid = planA.recid
)
, pland as(  -- delete the now unused record from cpt
    delete from cpt
    using plana
    where plana.cdesc = cpt.cdesc
)
select * from cpt
</code></pre>