title: MySQL auto-increment primary key causing value count mismatch and foreign-key error
body: <p>I have a MySQL database with a table defined like this:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS chug_dedup_instances(
  left_chug_name varchar(50) NOT NULL,
  FOREIGN KEY fk_left_chug_name(left_chug_name) REFERENCES chugim(name)
  ON DELETE CASCADE
  ON UPDATE CASCADE,
  right_chug_name varchar(50) NOT NULL,
  FOREIGN KEY fk_right_chug_name(right_chug_name) REFERENCES chugim(name)
  ON DELETE CASCADE
  ON UPDATE CASCADE,
  chug_dedup_instance_id int NOT NULL AUTO_INCREMENT PRIMARY KEY)
  COLLATE utf8_unicode_ci
  ENGINE = INNODB;
  </code></pre>
  
  <p>I tried to insert into this table like this:</p>
  
  <pre><code>INSERT INTO chug_dedup_instances VALUES ("Swimming", "Swimming");
  </code></pre>
  
  <p>This returns an error:</p>
  
  <pre><code>ERROR 1136 (21S01): Column count doesn't match value count at row 1
  </code></pre>
  
  <p>If I remove the chug_dedup_instance_id primary key, then the insert works fine. It also works if I give the ID value explicitly:</p>
  
  <pre><code>mysql&gt; INSERT INTO chug_dedup_instances VALUES ("Swimming", "Swimming", 0);
  Query OK, 1 row affected (0.00 sec)
  </code></pre>
  
  <p>This seems wrong to me: shouldn't the database supply the key value?  I don't think this is a dup of <a href="https://stackoverflow.com/questions/5931900/php-mysql-error-column-count-doesnt-match-value-count-at-row-1">PHP, MySQL error: Column count doesn&#39;t match value count at row 1</a>, because I'm asking why the auto-increment field is not being added automatically, as I believe it should be.   </p>
  
  <p>I tried removing the primary key and instead using a unique key on the two main values:</p>
  
  <pre><code>UNIQUE KEY uk_chug_dedup_instances(left_chug_name, right_chug_name))
  </code></pre>
  
  <p>I find that when I do this, I can't delete from the source table (chugim) that the two foreign keys reference- the database reports that the constraint is broken:</p>
  
  <pre><code>Cannot add or update a child row: a foreign key constraint fails    (`camprama_chugbot_db`.`chug_dedup_instances`, CONSTRAINT `chug_dedup_instances_ibfk_2` FOREIGN KEY (`right_chug_name`) REFERENCES `chugim` (`name`) ON DELETE CASCADE ON UPDATE CASCADE)
  </code></pre>
  
  <p>I would have expected the child table fields to be updated, since the table has "ON UPDATE CASCADE".</p>
  
  <p>I've copied the source table ("chugim") definition below.  Any help with either of these questions would be appreciated!</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS chugim(
  name varchar(50) NOT NULL,
  group_id int,
  FOREIGN KEY fk_group(group_id) REFERENCES groups(group_id)
  ON DELETE SET NULL
  ON UPDATE CASCADE,
  max_size int NULL,
  min_size int NULL DEFAULT 0,
  description varchar(2048),
  UNIQUE KEY uk_chugim(name, group_id),
  chug_id int NOT NULL AUTO_INCREMENT PRIMARY KEY)
  COLLATE utf8_unicode_ci
  ENGINE = INNODB;
  </code></pre>