Using AUTO_INCREMENT in MYSQL

Question

Why do I get the following exception when I did this, what mistake did i made? What is the right way to insert tuple into tables with AUTO_INCREMENT fields? How about delete, is it possible to update the ids?

string connStr = "server=localhost;user=root;database=test;port=3306;password=XXX;";
MySqlConnection conn = new MySqlConnection(connStr);
try{
    MessageBox.Show("Hello");
    conn.Open();
    string s0 = "CREATE TABLE school.student ( id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(45) NOT NULL, PRIMARY KEY (id))";
    string s1 = "INSERT INTO school.student VALUES ( LAST_INSERT_ID(), \'john\' )";
    string s2 = "INSERT INTO school.student VALUES ( LAST_INSERT_ID(), \'mary\' )";
    MySqlCommand cmd = new MySqlCommand(s0, conn);
    cmd.ExecuteNonQuery();                        
    cmd = new MySqlCommand(s1, conn);
    cmd.ExecuteNonQuery();                        
    cmd = new MySqlCommand(s2, conn);
    cmd.ExecuteNonQuery();
    conn.Close();
}catch(Exception ex){
    TextWriter tw = new StreamWriter("C:\\test.txt");
    MessageBox.Show(ex.ToString());
    tw.WriteLine(ex.ToString());
}

​

MySql.Data.MySqlClient.MySqlException: Duplicate entry '1' for key 'PRIMARY'
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.ReadResult()
   at MySql.Data.MySqlClient.ResultSet.NextResult()
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
   at ProjectInfo.Connect.Exec(String commandName, vsCommandExecOption executeOption, Object& varIn, Object& varOut, Boolean& handled)

Answer 1

Just omit the field that has AUTO_INCREMENT, MySQL will take care of the rest. You can update the IDs after DELETE, but I would advise you to not do that.

Answer 2

You shouldn't be attempting to insert a value into an auto increment field - as (the hint is in the name) this will be automatically incremented by MySQL.

Answer 3

These lines are the problem:

string s1 = "INSERT INTO school.student VALUES ( LAST_INSERT_ID(), \'john\' )";
string s2 = "INSERT INTO school.student VALUES ( LAST_INSERT_ID(), \'mary\' )";

See, the LAST_INSERT_ID() function returns the value of the last generated auto_increment value. At th time of your first insert, no insert has taken place yet and LAST_INSERT_ID() evaluates to NULL. Suppose your table was empty the row is inserted with the generated value of 1. At the time of the second insertt, LAST_INSERT_ID() will be 1 due to the previous row you inserted. So, this time, there is already a row with 1 in the db, and the second insert does not succeed because of the duplicate 1.

Rewrite like this:

string s1 = "INSERT INTO school.student (name) VALUES (\'john\' )";
string s2 = "INSERT INTO school.student (name) VALUES (\'mary\' )";