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)

AmitDiwan

Published on 12-Dec-2019 06:29:04

• Related Questions & Answers
• Format date while inserting records in MySQL
• Can we use INTERVAL keyword while inserting date records in a MySQL table?
• Find and display duplicate records in MySQL?
• Inserting records into a MySQL table using PreparedStatement in Java?
• Escaping quotes while inserting records in MongoDB?
• Ignore MySQL INSERT IGNORE statement and display an error if duplicate values are inserted in a table
• No error while inserting record in child table with no match in master table in SAP
• Inserting Records into Table using Node
• Display table records from a stored procedure in MySQL
• How to fix the incorrect datetime value while inserting in a MySQL table?
• How to delete all the duplicate records in a MySQL table?
• Display selected records from a MySQL table with IN() operator
• MySQL query to display the count of distinct records from a column with duplicate records
• How to avoid inserting duplicate rows in MySQL?
• How to delete a single value from a MySQL table with duplicate records?