Error: Duplicate entry '1' for key 'students.PRIMARY' Error Code: ER_DUP_ENTRY

Question

CREATE TABLE IF NOT EXISTS students (
    student_id INT,
  name VARCHAR(24),
  major VARCHAR(24),
  PRIMARY KEY(student_id)
  );
SELECT * FROM student;

INSERT INTO students VALUES(1,'Jack','Biology');

Answer 1

You're specifying the primary key (student_id) and from the error it already exists. You have a few options:

1. Don't specify the primary key. It should be set to autoincrement anyway, assuming that this is the primary table that students are entered into, and from the name of the table (students) it seems like it is. Then the query will be:

INSERT INTO students VALUES('Jack','Biology');

and then the table will autoincrement the primary key to the next pointer.

2. Use INSERT IGNORE. This will silently fail if you try to insert a student ID that already exists (or on any query that violates unique keys).

INSERT IGNORE INTO students VALUES(1, 'Jack','Biology');

This will not cause table changes, but it will also not cause an error that interrupts the script, and it will insert any rows that don't fail, say if you had multiple values inserted. The plain INSERT will fail for the entire list, not just the erroneous value.

3. Use ON DUPLICATE KEY UPDATE. This will update a list of values if it encounters a duplicate key.

INSERT INTO students VALUES(1, 'Jack','Biology')
ON DUPLICATE KEY UPDATE name = values(name), major = values(major);

In this case, you will change the values in the table that match the key. In this case, whichever student is student_id 1 will have its name and major updated to the supplied values. For instance, let's say that Jack changed his major to Chemistry. This would update student_id 1 to Jack, Chemistry and reflect his new major.

4. Use REPLACE INTO. I avoid this one. It is similar to ON DUPLICATE KEY UPDATE, but it removes the old entry and replaces it with a new one with a new ID. This can cause you problems with foreign keys, and also if you have a small primary key and you constantly replace into it, you can end up with a primary id that's bigger than the limits you set.