MySQL – INSERT IGNORE

There is no way to duplicate a primary key in a table. A student's roll number must, for example, always be unique within the student table.

There is no way to duplicate a primary key in a table. A student’s roll number must, for example, always be unique within the student table. Similar to the EmployeeID, it is expected that the EmployeeID is unique within an employee table. In the case of a table with a repeating primary key, we encounter an error when attempting to insert a tuple. By using the INSERT IGNORE statement, we can prevent the appearance of such errors, especially when inserting entries in bulk. The only warning that is generated is instead a warning.

Upon the insertion of a duplicate key in a column that must contain a PRIMARY KEY or UNIQUE constraint
The NULL value is inserted when the column has a NOT NULL constraint.
When inserting a row into a partitioned table, the inserted values violate the partition format.

Syntax

INSERT IGNORE INTO table_name (column_names)  
VALUES ( value_list), ( value_list) .....;

Example

Here is an example of how the INSERT IGNORE statement in MySQL works. As a first step, we need to create a table named “employees” by using the following statement:

CREATE TABLE employees (  
  emp_no int AUTO_INCREMENT PRIMARY KEY,  
  Name varchar(45) DEFAULT NULL,  
  Email varchar(45) NOT NULL UNIQUE
);

UNIQUE constraint ensures that duplicate values cannot be inserted into the email column. In the next step, the records must be inserted into the table. In order to add data to a table, we can use the following statement:

INSERT INTO employees 
VALUES (1,'sudha', 'sudha@gmail.com'),   
( 2,'Sai', 'sai@gmail.com'),   
( 3,'Ravi', 'ravi@gmail.com');

The final step in verifying the insert operation is to execute the SELECT statement:

OUTPUT:

In order to add two records to the table, we will execute the following statement:

INSERT INTO employees  
VALUES (4,'sri', 'sai@gmail.com'),   
(5, 'Ravi', 'ravi@gmail.co');

There will be an error message: ERROR 1062 (23000): Duplicate entry ‘Joseph@javatpoint.com’ for key ‘student.Email’ since the email address violates the UNIQUE constraint.

We will now examine what happens when the INSERT IGNORE statement is used in the above query:

INSERT IGNORE INTO  employees 
VALUES (4,'sri', 'sai@gmail.com'),   
(5, 'Ravi', 'ravi@gmail.com');