Display an error while inserting duplicate records in a MySQL table

For this, you can use UNIQUE KEY. Let us first create a table −

mysql> create table DemoTable1553    -> (    -> EmployeeId int NOT NULL AUTO_INCREMENT PRIMARY KEY,    -> EmployeeName varchar(20),    -> EmployeeSalary int    -> ); Query OK, 0 rows affected (0.47 sec)

Here is the query to add unique key −

mysql> alter table DemoTable1553 add unique(EmployeeSalary); Query OK, 0 rows affected (0.53 sec) Records: 0  Duplicates: 0  Warnings: 0

Insert some records in the table using insert command −

mysql> insert into DemoTable1553(EmployeeName,EmployeeSalary) values('Chris',45000); Query OK, 1 row affected (0.21 sec) mysql> insert into DemoTable1553(EmployeeName,EmployeeSalary) values('David',35000); Query OK, 1 row affected (0.14 sec) mysql> insert into DemoTable1553(EmployeeName,EmployeeSalary) values('Sam',25000); Query OK, 1 row affected (0.15 sec) mysql> insert into DemoTable1553(EmployeeName,EmployeeSalary) values('Carol',45000); ERROR 1062 (23000): Duplicate entry '45000' for key 'EmployeeSalary'

Display all records from the table using select statement −

mysql> select * from DemoTable1553;

This will produce the following output −

+------------+--------------+----------------+ | EmployeeId | EmployeeName | EmployeeSalary | +------------+--------------+----------------+ |          1 | Chris        |          45000 | |          2 | David        |          35000 | |          3 | Sam          |          25000 | +------------+--------------+----------------+ 3 rows in set (0.00 sec)