title: MySQL composite foreign key only one column used
body: <p>I'm using MySQL (Server version: 10.3.22-MariaDB MariaDB Server). When I create a composite foreign key, it seems that only the first column of the foreign key is in effect. Here, Emails references Persons via (firstname, lastname):</p>
  <pre><code>create table Persons (
  firstname varchar(10),
  lastname varchar(10),
  dob date,
  primary key (firstname, lastname)
  ) engine=innodb;
  
  create table Emails (
  firstname varchar(10),
  lastname varchar(10),
  email varchar(100),
  foreign key (firstname, lastname) references Persons(firstname, lastname)
  ) engine=innodb;
  </code></pre>
  <p>When I do &quot;desc Emails&quot;, I get</p>
  <pre><code>+-----------+--------------+------+-----+---------+-------+
  | Field     | Type         | Null | Key | Default | Extra |
  +-----------+--------------+------+-----+---------+-------+
  | firstname | varchar(10)  | YES  | MUL | NULL    |       |
  | lastname  | varchar(10)  | YES  |     | NULL    |       |
  | email     | varchar(100) | YES  |     | NULL    |       |
  +-----------+--------------+------+-----+---------+-------+
  </code></pre>
  <p>The MUL is marked only for firstname. I inserted the following:</p>
  <pre><code>insert Persons (firstname, lastname) values (&quot;John&quot;, &quot;Doe&quot;);
  insert Persons (firstname, lastname) values (&quot;John&quot;, &quot;Smith&quot;);
  insert Emails (firstname, lastname, email) values (&quot;John&quot;, &quot;Doe&quot;, &quot;a@b.com&quot;);
  insert Emails (firstname, email) values (&quot;John&quot;, &quot;c@d.com&quot;);
  </code></pre>
  <p>and when I do a &quot;select * from Persons,Emails&quot;, I get</p>
  <pre><code>+-----------+----------+------+-----------+----------+---------+
  | firstname | lastname | dob  | firstname | lastname | email   |
  +-----------+----------+------+-----------+----------+---------+
  | John      | Doe      | NULL | John      | Doe      | a@b.com |
  | John      | Smith    | NULL | John      | Doe      | a@b.com |
  | John      | Doe      | NULL | John      | NULL     | c@d.com |
  | John      | Smith    | NULL | John      | NULL     | c@d.com |
  +-----------+----------+------+-----------+----------+---------+
  </code></pre>
  <p>which shows that (&quot;John&quot;, &quot;Doe&quot;) of Emails is joined with (&quot;John&quot;, &quot;Smith&quot;) of Persons (second row). It seems that lastname is not used for the joining.</p>
  <p>Am I doing foreign keys wrongly?</p>