title: MySQL ON DUPLICATE KEY UPDATE and auto_increment index
body: <p>I've been scooping around in the MySQL documentation but haven't found a clear answer on the following problem.</p>
  
  <p>Let's say that I have a table "users". With the following fields:</p>
  
  <pre><code>CREATE TABLE `users` (
    `userId` int(11) NOT NULL AUTO_INCREMENT,
    `username` varchar(16) NOT NULL,
    `password` varchar(128) NOT NULL,
    `salt` varchar(128) NOT NULL,
    `email` varchar(256) NOT NULL,
    PRIMARY KEY (`userId`),
    UNIQUE KEY `username` (`username`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
  </code></pre>
  
  <p>So I have a primary key "userId" which is an auto_increment value. And a unique field "username". (So each username can only appear once, but the username should be editable).</p>
  
  <p>Now I would like to know how I should use the ON DUPLICATE KEY UPDATE statement in this case. (Don't mind the unsecure way, but this is easier to read in this example.)</p>
  
  <pre><code>INSERT INTO users (`userId`, `username`, `password`, `salt`, `email`)
  VALUES('$userId', '$username', '$password', '$salt', '$email')
  ON DUPLICATE KEY UPDATE 
     username = VALUES(username), 
     password = VALUES(password), 
     email = VALUES(email);
  </code></pre>
  
  <p>What I was wondering was how I would have to insert a new user. Should I just pass a "0" or null for the UserId variable and it will create a new record with the appropriate auto incremented id? There is little information about how to use this with an auto_increment.</p>
  
  <p>Because this current statement gives an error "<strong>Column count doesn't match value count at row 1</strong>".</p>
  
  <p>Also what happens if I alter the username of a user, or if I enter a user with a different id but the same username?</p>
  
  <p><strong><em>Edit</em></strong>
  <em>What I want to know is how to use the ON DUPLICATE KEY statement with a primary key that is an auto_increment. The problem is that I can't just pass on the the key in the insert statement, but I also need the key to update if it already exists. So is it possible to use the ON DUPLICATE KEY with an auto_increment primary?</em> </p>