title: Setting a composite foreign key to NULL in Postgres
body: <p>I have two tables, <code>parents</code> and <code>children</code>, and a column in <code>children</code> called <code>parent</code>. I also have a column called <code>account_id</code> in both tables that should be <code>NOT NULL</code>.</p>
  <p>I also have a composite foreign key like this:</p>
  <pre><code>ALTER TABLE children
      ADD COLUMN IF NOT EXISTS parent UUID,
      ADD CONSTRAINT parent_id FOREIGN KEY (account_id, parent) REFERENCES parents(account_id, id)
      ON UPDATE RESTRICT ON DELETE SET NULL;
  </code></pre>
  <p>Upon deleting a row from <code>parents</code>, I want the child to remain in the <code>children</code> table, but with a <code>parent</code> of <code>NULL</code>. However, the above migration causes an error in the case of a delete, complaining that the value of <code>account_id</code> cannot be <code>NULL</code>. How can I rewrite my constraint to achieve this?</p>