
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
Ignore MySQL INSERT IGNORE statement and display an error if duplicate values are inserted in a table
Let us first create a table −
mysql> create table DemoTable ( FirstName varchar(100) ); Query OK, 0 rows affected (0.58 sec)
As stated in the official MySQL docs, “without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted”.
Therefore, let us now add a unique index −
mysql> alter table DemoTable add unique index(FirstName); Query OK, 0 rows affected (0.50 sec) Records: 0 Duplicates: 0 Warnings: 0
Insert some records in the table using insert command. As you can see, since we haven’t used INSERT IGNORE, an error would be visible while inserting duplicate records −
mysql> insert into DemoTable values('Chris'); Query OK, 1 row affected (0.32 sec) mysql> insert into DemoTable values('Robert'); Query OK, 1 row affected (0.09 sec) mysql> insert into DemoTable values('David'); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable values('Robert'); ERROR 1062 (23000): Duplicate entry 'Robert' for key 'FirstName' mysql> insert into DemoTable values('Chris'); ERROR 1062 (23000): Duplicate entry 'Chris' for key 'FirstName' mysql> insert into DemoTable values('Sam'); Query OK, 1 row affected (0.12 sec)
Display all records from the table using select statement −
mysql> select *from DemoTable;
This will produce the following output −
+-----------+ | FirstName | +-----------+ | Chris | | David | | Robert | | Sam | +-----------+ 4 rows in set (0.00 sec)
- Related Articles
- What is the use of MySQL IGNORE INSERT statement?
- Ignore null values in MySQL and display rest of the values
- Display an error while inserting duplicate records in a MySQL table
- Why does div display: table-row ignore margin?
- Can we ignore duplicate rows in COUNT?
- Ignore first 4 values in MongoDB documents and display the next 3?
- Ignore NULL values from separate tables in a single MySQL query and display count of NOT NULL records
- Ignore null values in MongoDB documents
- Ignore NULL and UNDEFINED values while running a MongoDB query
- How to ignore an exception and proceed in Python?
- Update multiple values in a table with MySQL IF Statement
- How can we ignore the negative values return by MySQL DATEDIFF() function?
- Find duplicate column values in MySQL and display them
- How to insert an array of values in a MySQL table with a single INSERT?
- Select * but ignore displaying results containing a specific character in MySQL

Advertisements