Open In App
Related Articles


Improve Article
Save Article
Like Article

We know that a primary key of a table cannot be duplicated. For instance, the roll number of a student in the student table must always be distinct. Similarly, the EmployeeID is expected to be unique in an employee table. When we try to insert a tuple into a table where the primary key is repeated, it results in an error. However, with the INSERT IGNORE statement, we can prevent such errors from popping up, especially when inserting entries in bulk and such errors can interrupt the flow of insertion. Instead, only a warning is generated. Cases where INSERT IGNORE avoids error

  • Upon insertion of a duplicate key where the column must contain a PRIMARY KEY or UNIQUE constraint
  • Upon insertion of NULL value where the column has a NOT NULL constraint.
  • Upon insertion of a row to a partitioned table where the inserted values go against the partition format.

Example: Say we have a relation, Employee. Employee Table:

EmployeeID Name City
15001 Aakash Delhi
15003 Sahil Bangalore
15010 John Hyderabad
15008 Shelley Delhi
15002 Ananya Mumbai
15004 Sia Pune

As we can notice, the entries are not sorted on the basis of their primary key, i.e. EmployeeID. Sample Query:

INSERT OR IGNORE INTO Employee (EmployeeID, Name, City)
VALUES (15002, 'Ram', 'Mumbai');

Output: No entry inserted. Sample Query:

Inserting multiple records

When inserting multiple records at once, any that cannot be inserting will not be, but any that can will be:

INSERT OR IGNORE INTO Employee (EmployeeID, Name, City)
VALUES (15007, 'Shikha', 'Delhi'), (15002, 'Ram', 'Mumbai'), (15009, 'Sam', 'Ahmedabad');

Output: The first and the last entries get inserted; the middle entry is simple ignored. No error is flashed. Disadvantage Most users do not prefer INSERT IGNORE over INSERT since some errors may slip unnoticed. This may cause inconsistencies in the table, thereby causing some tuples to not get inserted without the user having a chance to correct them. Hence, INSERT IGNORE must be used in very specific conditions. If you like GeeksforGeeks and would like to contribute, you can also write an article using or mail your article to See your article appearing on the GeeksforGeeks main page and help other Geeks. Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above.

Unlock the Power of Placement Preparation!
Feeling lost in OS, DBMS, CN, SQL, and DSA chaos? Our Complete Interview Preparation Course is the ultimate guide to conquer placements. Trusted by over 100,000+ geeks, this course is your roadmap to interview triumph.
Ready to dive in? Explore our Free Demo Content and join our Complete Interview Preparation course.

Last Updated : 19 Jul, 2023
Like Article
Save Article
Similar Reads