MySQL - Duplicate entry for key "PRIMARY"

Question

I have the following situation:

In my database, I have a table called Person. A person has an ID. In my conceptual model, student is inherited from person, so I have another table called Student.

I wrote C# code to insert into the Student table:

string query = "INSERT INTO Person (ID, ...) VALUES("id",...);";
MySqlCommand command = new MySqlCommand(query, connection);
command.ExecuteNonQuery();
query = "INSERT INTO Student (..., ID) VALUES(...,"id");";
command.ExecuteNonQuery();

Obviously, I need to add values into the Person class first, because every student is a person. So, after I did that, I try to add the rest of the Student data into the table.

The problem is that I am getting this error:

Duplicate entry (id) for key "PRIMARY"

which I don't understand, since this key needs to be the same.

Answer 1

The exception message is pretty clear:

Duplicate entry (id) for key "PRIMARY"

You ARE duplicating the ID on a table. You didn't tell in which line this is happening, so, let's assume both possibilities (and that the error is not elsewhere).

1. The exception is happening when you are trying to insert into table PERSON.

In this case,if the PRIMARY KEY of this table was AUTO INCREMENT, this wouldn't be possible. If it isn't, and you are inserting the ID of the record by yourself, your code is not creating the ID's correctly and is inserting a value that already exists in the table.

To check if this is the case during runtime, just make a select for the ID you are trying to insert BEFORE actually inserting it:

string query = "SELECT count(*) FROM Person WHERE ID = " + id;
MySqlCommand command = new MySqlCommand(query, connection);
int count = (int)command.ExecuteScalar();
if (count > 0) 
{
  //You already inserted this ID. Warn the user
}

2. You are getting the exception on when inserting into table STUDENT

First, lets assume that the ID you are inserting into STUDENT that you're showing here is not the PRIMARY KEY of the table student, but only a FK (foreign key) to table PERSON.

In this case, the same fro the item 1 applies here. You ARE entering a duplicate id in the table. Use the same approach from item 1 to verify this.

But if the ID from PERSON is really the same ID from STUDENT (a ONE to ONE relationship), what's the problem?

Exactly the same. You are entering a duplicated ID.

So, no matter where the error is happening, you are allowing your code to try to insert a DUPLICATE ID (primary key) in the table.

Again, you must be creating the ID's manually, as an auto-increment primary key would not cause this problem (UNLESS you are manually setting the ID, in which case MySQL would use this value instead of the automatic value).

If you are creating the ID's manually, you MUST ensure that they are not duplicates.

Answer 2

I don't even know if this should be called an answer or a hack, but it worked:

string query = "INSERT INTO Person (ID, ...) VALUES(id, ...); 
                INSERT INTO Student (..., ID) VALUES(..., (select ID from Person WHERE Person.ID = id));";
MySqlCommand command = new MySqlCommand(query, connection);