title: MySql composite foreign key ON DELETE set null
body: <p><strong>Please refer to</strong> <a href="http://sqlfiddle.com/#!9/ce650/1/0" rel="nofollow noreferrer">this SQLFiddle</a></p>
  <p><div class="snippet" data-lang="js" data-hide="true" data-console="false" data-babel="false">
  <div class="snippet-code snippet-currently-hidden">
  <pre class="snippet-code-html lang-html prettyprint-override"><code>CREATE TABLE `parent` (
    `id` varchar(64) NOT NULL,
    `master_id` varchar(64) NOT NULL,
    `c1_id` varchar(64) DEFAULT NULL,
    `c2_id` varchar(64) DEFAULT NULL,
    PRIMARY KEY (`master_id`, `id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  CREATE TABLE `child_1` (
    `id` varchar(64) NOT NULL,
    `master_id` varchar(64) NOT NULL,
    PRIMARY KEY (`master_id`, `id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  CREATE TABLE `child_2` (
    `id` varchar(64) NOT NULL,
    `master_id` varchar(64) NOT NULL,
    PRIMARY KEY (`master_id`, `id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  
  INSERT INTO child_1 (`id`, `master_id`)
    VALUES
      (1, 'm1');
  INSERT INTO child_2 (`id`, `master_id`)
    VALUES
      (2, 'm2'),
      (3, 'm1');
  INSERT INTO parent (`id`, `master_id`, `c1_id`, `c2_id`)
    VALUES
      (4, 'm1', null, null),
      (5, 'm1', 1, null),
      (6, 'm2', null, 2);</code></pre>
  </div>
  </div>
  </p>
  <p>I have multiple tables that have composite Primary Key.</p>
  <p>All PK are <code>PRIMARY KEY (master_id, id)</code></p>
  <hr />
  <p>And now I try to add Foreign Keys to these tables.</p>
  <pre><code>ALTER TABLE `parent`
      ADD CONSTRAINT parent_fk_1
      FOREIGN KEY (`master_id`, `c1_id`)
      REFERENCES child_1 (`master_id`, `id`)
      ON UPDATE CASCADE
      ON DELETE SET NULL;
  </code></pre>
  <p>But it throws error <code>Cannot add foreign key constraint</code>.</p>
  <p>Here <code>parent.master_id</code> always must stay <code>NOT NULL</code> while <code>c1_id</code> and <code>c2_id</code> can be set to <code>NULL</code>.</p>
  <p>Is it possible to achieve this kind of Foreign Key setup?<br />
  I found that there is <a href="https://dba.stackexchange.com/questions/58894/differences-between-match-full-match-simple-and-match-partial"><code>MATCH SIMPLE</code></a> option that allows composite key to be partial <code>NULL</code>, but how to apply it for <code>ON DELETE SET NULL</code>?</p>