title: How to edit a record that results in a uniqueness violation and echo the change to child tables?
body: <p>PostgreSQL 11.1</p>
  
  <p>How can "Editing" of a record be transmitted to dependent records?</p>
  
  <p>Summary of my issue:</p>
  
  <ul>
  <li>The master table, disease, needs a unique constraint on the description column. This unique constraint is needed for foreign key ON UPDATE CASCADE to its children tables.</li>
  <li>To allow for a temporary violation of the unique constraint, it must be made deferrable.  <em>BUT A DEFERABLE CONSTRAINT CAN NOT BE USED IN A FOREIGN KEY</em>.</li>
  </ul>
  
  <p>Here is the situation.</p>
  
  <ul>
  <li>The database has 100+ tables (and keeps on growing).</li>
  <li>Most all information has been normalized in that repeating groups of information  have been delegated to their own table.</li>
  <li>Following normalization, most tables are lists without duplication of records. Duplication of records within a table is not allowed.</li>
  <li>All tables have a unique ID assigned to each record (in addition to a unique constraint placed on the record information).</li>
  <li>Most tables are dependent on another table. The foreign keys reference the primary key of the table they are dependent on.</li>
  <li>Most unique constraints involve a foreign key (which in turn references the primary key of the parent table).</li>
  </ul>
  
  <p>So, assume the following schema:</p>
  
  <pre><code>CREATE TABLE phoenix.disease
  (
      recid integer NOT NULL DEFAULT nextval('disease_recid_seq'::regclass),
      code text COLLATE pg_catalog."default",
      description text COLLATE pg_catalog."default" NOT NULL,
      CONSTRAINT disease_pkey PRIMARY KEY (recid),
      CONSTRAINT disease_code_unique UNIQUE (code)
  
          DEFERRABLE,
      CONSTRAINT disease_description_unique UNIQUE (description)
  ,
      CONSTRAINT disease_description_check CHECK (description &lt;&gt; ''::text)
  )
  
  CREATE TABLE phoenix.dx
  (
      recid integer NOT NULL DEFAULT nextval('dx_recid_seq'::regclass),
      disease_recid integer NOT NULL,
      patient_recid integer NOT NULL,
      CONSTRAINT pk_dx_recid PRIMARY KEY (recid),
      CONSTRAINT dx_unique UNIQUE (tposted, patient_recid, disease_recid)
  ,
      CONSTRAINT dx_disease_recid_fkey FOREIGN KEY (disease_recid)
          REFERENCES phoenix.disease (recid) MATCH SIMPLE
          ON UPDATE CASCADE
          ON DELETE RESTRICT,
      CONSTRAINT dx_patients FOREIGN KEY (patient_recid)
          REFERENCES phoenix.patients (recid) MATCH SIMPLE
          ON UPDATE CASCADE
          ON DELETE RESTRICT
  )
  </code></pre>
  
  <p>(Columns not involved in this question have been removed. :) )</p>
  
  <p>There are many other children tables of disease with the same basic dependency on the disease table. Note that the primary key of the disease table is a foreign key to the dx table and that the dx table uses this foreign key in a unique constraint. Also note that the dx table is just one table of a long chain of table references. (That is the dx table also has its primary key referenced by other tables).</p>
  
  <p>The problem: I wish to "edit" the contents of the parent disease record. By "edit", I mean:</p>
  
  <ul>
  <li>change the data in the description column.</li>
  <li>if the result of the change causes a duplication in the disease table, then one of the "duplicated" records will need to be deleted.</li>
  </ul>
  
  <p>Herein lies my problem. There are many different tables that use the primary key of the disease table in their own unique constraint. If those tables ALSO have a foreign key reference to the duplicated record (in disease), then cascading the delete to those tables would be appropriate -- i.e., no duplication of records will occur. </p>
  
  <p>However, if the child table does NOT have a reference to the "correct" record in the parent disease table, then simply deleting the record (by cascade) will result in loss of information.</p>
  
  <pre><code>Example:
  Disease Table:
      record 1:  ID = 1    description = "ABC"
      record 2:  ID = 2    description = "DEF"
  
  Dx Table:
      record 5:  ID = 5   refers to ID=1 of Disease Table.
  
  Editing of record 1 in Disease table results in description becoming "DEF"
  Disease Table:
      record 1:  ID = 1   "ABC" --&gt; "DEF"
  </code></pre>
  
  <p>I have tried deferring the primary key of the disease table so as to allow the "correct" ID to be "cascaded" to the child tables. This causes the following errors:</p>
  
  <ul>
  <li>A foreign key can not be dependent on a deferred column. <em>"cannot use a deferrable unique constraint for referenced table "disease"</em></li>
  <li>additionally, the parent table (disease) has no way of knowing ahead of time if its children already have a reference to the "correct" record so allowing deletion, or if the child needs to change its own column data to reflect the new "correct" id.</li>
  </ul>
  
  <p>So, how can I allow a change in the parent table (disease) and notify the child tables to change their column values -- and delete within them selves should a duplicate record arise?</p>
  
  <p>Lastly, I do not know today what future tables I will need. So I cannot "precode" into the parent table who its children are or will be.</p>
  
  <p>Thank you for any help with this.</p>