How to violate duplicate key constraint (Unique_Violation) when updating reference table in PostgreSQL?

Question

In PostgreSQL version 9.1, I have two tables: ICD9, and Dx. I now wish to update the parent table, ICD9, by changing an existing record to a different key value (in cdesc).

If the new key (cicd9,cdesc) does not already exist in the ICD9 table, then the new values are cascaded to the child table, Dx :-)

However, if the "new" key with the new value of cdesc already exists in the ICD9 table, then the record is not updated due to

error 23505: duplicate key value violates unique constraint "constraint_cdesc".

What I need to happen is that the update of the parent table, ICD9, updates the old record to the new values and this update is cascaded to all the children records in DX that used the old key and then to remove the now unused "old" record from the ICD9 table.

Any help for this newbie would be much appreciated. TIA

CREATE TABLE icd9
(
 cicd9 character varying(8),
 cdesc character varying(80) NOT NULL,
 CONSTRAINT constraint_cdesc UNIQUE (cicd9, cdesc),
 CONSTRAINT desccheck CHECK (cdesc::text <> ''::text)
)

CREATE TABLE dx
(
  cicd9 character varying(8),
  cdesc character varying(80) NOT NULL,
  CONSTRAINT fk_icd9 FOREIGN KEY (cicd9, cdesc)
      REFERENCES icd9 (cicd9, cdesc) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED 
)

Edit #1: I guess I simplified these table structures to much in order to clarify my point, here are the full structures. Any help with this is much appreciated.

CREATE TABLE dx
(
recid serial NOT NULL,
cpatient character varying(33) NOT NULL,
cicd9 character varying(8),
cdesc character varying(80) NOT NULL,
tposted timestamp without time zone NOT NULL,
"timestamp" timestamp without time zone DEFAULT now(),
modified timestamp without time zone DEFAULT now(),
resolved boolean DEFAULT false,
treated boolean DEFAULT false,
chronic boolean DEFAULT false,
groupid character varying(33) NOT NULL,
service integer DEFAULT 0,
pmh boolean DEFAULT false,
explanation text,
CONSTRAINT pk_dx_recid PRIMARY KEY (recid),
CONSTRAINT dx_cpatient_fkey FOREIGN KEY (cpatient)
  REFERENCES patients (cpatient) MATCH SIMPLE
  ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT dx_groupid_fkey FOREIGN KEY (groupid)
  REFERENCES charts (groupid) MATCH SIMPLE
  ON UPDATE CASCADE ON DELETE RESTRICT,
CONSTRAINT fk_icd9 FOREIGN KEY (cicd9, cdesc)
  REFERENCES icd9 (cicd9, cdesc) MATCH SIMPLE
  ON UPDATE CASCADE ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT noduplicate_dx UNIQUE (cicd9, cdesc, groupid, tposted),
CONSTRAINT desccheck CHECK (cdesc::text <> ''::text),
CONSTRAINT groupcheck CHECK (groupid::bpchar <> ''::bpchar),
CONSTRAINT patientcheck CHECK (cpatient::bpchar <> ''::bpchar)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE dx
OWNER TO postgres;


  CREATE TABLE icd9
  (
   recid serial NOT NULL,
   cicd9 character varying(8),
   cdesc character varying(80) NOT NULL,
   "timestamp" timestamp without time zone DEFAULT now(),
   modified timestamp without time zone DEFAULT now(),
 chronic boolean NOT NULL DEFAULT false,
 common boolean NOT NULL DEFAULT false,
 CONSTRAINT pk_icd9_recid PRIMARY KEY (recid),
 CONSTRAINT constraint_cdesc UNIQUE (cicd9, cdesc),
 CONSTRAINT desccheck CHECK (cdesc::text <> ''::text)
)
  WITH (
   OIDS=FALSE
 );
 ALTER TABLE icd9
  OWNER TO postgres;

Answer 1

Aside from probable errors in table design, I could find no way of temporarily suspending the "unique_violation" which comes from updating a record's key values to values held by another record. In the design above, the ICD9 file is intended as a library where the records may or may not be used elsewhere in the system.

The solution to this question is to move child records from the tables referencing the library, and then delete the record which would cause the conflict. Much of this code comes from Finding Foreign Keys with No Indexes and a special thanks to @Patrick for providing the final key I needed to make this work. Below is the code (which works for me) to perform this work. Hope it helps somebody.

CREATE OR REPLACE FUNCTION g_saveicd9(ocicd9 text, ocdesc text, ncicd9 text, ncdesc text, nchronic boolean, ncommon boolean)
  RETURNS void AS
$BODY$
DECLARE
    -- Declare row format
    o ICD9%rowtype;
    n ICD9%rowtype;

BEGIN
    -- Existing key values
    o.cicd9 := ocicd9;
    o.cdesc := ocdesc;

    -- New record values
    n.cicd9 := ncicd9;
    n.cdesc := ncdesc;
    n.chronic := nchronic;
    n.common := ncommon;

    -- Must set contraints all immediate to trigger constraints immediately for exceptions.
    SET CONSTRAINTS ALL IMMEDIATE; 

    BEGIN
        -- Edit ICD9 record. 
        UPDATE icd9 SET 
            cicd9 = n.cicd9, cdesc = n.cdesc, chronic = n.chronic, common = n.common
        WHERE 
            cicd9 = o.cicd9 and cdesc = o.cdesc;

        IF FOUND THEN
        -- Successfully changed an existing record to new values. (The new value did not already exist). Exit function.
            RETURN;
        END IF;

        EXCEPTION                   

            WHEN unique_violation THEN

                -- The new key already exists, so need to manually move all children to the "new" key.
                PERFORM merge_children_of_icd9(o.cicd9, o.cdesc, n.cicd9, n.cdesc);

                -- Remove the old key values from ICD9
                DELETE FROM icd9
                    WHERE cicd9 = o.cicd9 and cdesc = o.cdesc;

                -- Update existing record.
                UPDATE icd9 SET 
                        chronic = n.chronic, common = n.common
                        WHERE 
                        cicd9 = n.cicd9 and cdesc = n.cdesc;

                RETURN; -- Exit function.
    END;    

    -- No record found to update, so create new record.     
    BEGIN   
        INSERT INTO icd9(
            cicd9, cdesc, chronic, common)
        VALUES ( 
            n.cicd9, n.cdesc, n.chronic, n.common);

        -- Successfully inserted a new icd9 record, so exit function. This line is not reached if INSERT throws an exception.
        RETURN;  

        EXCEPTION                   

            WHEN unique_violation THEN

                -- Key (o.cicd9,o.cdesc) not found for update, but target key (n.cicd9,n.cdesc) already exists.
                -- Update target record with non-key values.
                UPDATE icd9 SET 
                        chronic = n.chronic, common = n.common
                    WHERE 
                        cicd9 = n.cicd9 and cdesc = n.cdesc;

                RETURN;                 

    END;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION g_saveicd9(text, text, text, text, boolean, boolean)
  OWNER TO postgres;


CREATE OR REPLACE FUNCTION merge_children_of_icd9(ocicd9 text, ocdesc text, ncicd9 text, ncdesc text)
  RETURNS void AS
$BODY$

DECLARE
    r RECORD;

BEGIN
    FOR r IN

        WITH fk_actions ( code, action ) AS (
            VALUES  ( 'a', 'error' ),
                ( 'r', 'restrict' ),
                ( 'c', 'cascade' ),
                ( 'n', 'set null' ),
                ( 'd', 'set default' )
        ),
        fk_list AS (
            SELECT pg_constraint.oid as fkoid, conrelid, confrelid::regclass as parentid,
            conname, relname, nspname,
            fk_actions_update.action as update_action,
            fk_actions_delete.action as delete_action,
            conkey as key_cols
            FROM pg_constraint
            JOIN pg_class ON conrelid = pg_class.oid
            JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid
            JOIN fk_actions AS fk_actions_update ON confupdtype = fk_actions_update.code
            JOIN fk_actions AS fk_actions_delete ON confdeltype = fk_actions_delete.code
            WHERE contype = 'f'
        ),
        fk_attributes AS (
            SELECT fkoid, conrelid, attname, attnum
            FROM fk_list
            JOIN pg_attribute
                ON conrelid = attrelid
                AND attnum = ANY( key_cols )
            ORDER BY fkoid, attnum
        ),
        fk_cols_list AS (
            SELECT fkoid, array_agg(attname) as cols_list
            FROM fk_attributes
            GROUP BY fkoid
        )
        SELECT fk_list.fkoid, fk_list.conrelid, fk_list.parentid, fk_list.conname, fk_list.relname, fk_cols_list.cols_list 
        FROM fk_list
        JOIN fk_cols_list USING (fkoid)
        WHERE parentid = 'icd9'::regclass

    LOOP

        -- In an UPDATE statement in PL/pgSQL, the table name has to be given as a literal. If you want to dynamically set the
        -- table name and the columns, use the EXECUTE command and paste the query string together.
        -- The USING clause can only be used for substituting data values.
        -- cols_list[1] is cicd9. cols_list[2] is cdesc.

        EXECUTE 'UPDATE ' || quote_ident(r.relname) ||
            ' SET '   || quote_ident(r.cols_list[1]) || ' = $1, ' 
                      || quote_ident(r.cols_list[2]) || ' = $2' ||
            ' WHERE ' || quote_ident(r.cols_list[1]) || ' = $3 AND ' 
                      || quote_ident(r.cols_list[2]) || ' = $4'
        USING ncicd9, ncdesc, ocicd9, ocdesc;

    END LOOP;       

RETURN;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION merge_children_of_icd9(text, text, text, text)
  OWNER TO postgres;