title: Mysql create table with two foreign keys referencing primary key
body: <p>I have two mysql tables.  The first one is created using the following code:</p>
  
  <pre><code>create table project(
  project_id int not null auto_increment,
  project_name varchar(30)
  primary key(project_id));
  </code></pre>
  
  <p>The second table:</p>
  
  <pre><code>create table matches(
  match_id int not null auto_increment,
  match_name varchar(30),
  project_id int(4) foreign key (project_id) references projects(project_id));
  </code></pre>
  
  <p>Both these commands works fine.  I want to add the project_name column from the first table to the second table.  I tried using </p>
  
  <pre><code>alter table projects drop primary key, add primary key(project_id, project_name);
  </code></pre>
  
  <p>and then</p>
  
  <pre><code>alter table matches add column project_name varchar(30), add foreign key (project_name) references projects(project_name);
  </code></pre>
  
  <p>But got the following error:</p>
  
  <pre><code>ERROR 1005 (HY000): Can't create table 'matches.#sql-55e_311' (errno: 150)
  </code></pre>
  
  <p>How do i include both the columns from the first table into the second table.
  The current structure of my second table is as follows:</p>
  
  <pre><code>+------------+-------------+------+-----+---------+----------------+
  | Field      | Type        | Null | Key | Default | Extra          |
  +------------+-------------+------+-----+---------+----------------+
  | match_id   | int(11)     | NO   | PRI | NULL    | auto_increment |
  | match_name | varchar(30) | YES  |     | NULL    |                |
  | project_id | int(4)      | NO   | MUL | NULL    |                |
  +------------+-------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>I want to add the project_name as the fourth column in my second table.</p>