How to not allow duplicate entries to be entered a MySQL Table?

To not allow any duplicate entry to be entered in a MySQL table, you need to add unique key. The syntax is as follows −

alter ignore table yourTableName add constraint unique key(yourColumName);

The above syntax sets unique key. To understand the above syntax, let us create a table.

The following is the query to create a table −

mysql> create table RemoveDuplicateEntry
   −> (
   −> Id int,
   −> Name varchar(100)
   −> )ENGINE = MyISAM;
Query OK, 0 rows affected (0.15 sec)

Now you can implement the syntax discussed in the beginning. The query is as follows −

mysql> alter table RemoveDuplicateEntry add constraint unique key(Id);
Query OK, 0 rows affected (0.56 sec)
Records: 0 Duplicates: 0 Warnings: 0

Let us check the table structure now. The query is as follows −

mysql> desc RemoveDuplicateEntry;

The following is the output −

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| Id    | int(11)      | YES  | UNI | NULL    |       |
| Name  | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

Now, let us insert some records with duplicate entries. This won’t get inserted since we used UNIQUE above −

The query to insert record is as follows −

mysql> insert into RemoveDuplicateEntry values(101,'John');
Query OK, 1 row affected (0.06 sec)

Now, if you will insert same record again, then the following error will be visible −

mysql> insert into RemoveDuplicateEntry values(101,'John');
ERROR 1062 (23000): Duplicate entry '101' for key 'Id'

Check all records from the table with select statement. The query is as follows −

mysql> select *from RemoveDuplicateEntry;

The following is the output displaying records with no duplicate entries −

+------+------+
| Id   | Name |
+------+------+
| 101  | John |
+------+------+
1 row in set (0.00 sec)

Jennifer Nicholas

Updated on 30-Jul-2019 22:30:24

• Related Questions & Answers
• Selecting the top occurring entries in MySQL from a table with duplicate values?
• Delete multiple entries from a MySQL table
• How to delete all the duplicate records in a MySQL table?
• Avoid duplicate entries in MongoDB?
• Program to remove duplicate entries in a list in Python
• How to remove duplicate entries by two keys in MongoDB?
• How to remove duplicate values from a MySQL table using LEFT JOIN?
• How to delete a single value from a MySQL table with duplicate records?
• How to Fill the Entries in Parsing Table?
• How do I modify a MySQL column to allow NULL?
• Program to remove duplicate entries in a linked list in Python
• How to select from MySQL table A that does not exist in table B?
• How to get the number of entries in a Lua table?
• How to allow a MySQL user account to connect from any host?
• Allow a label to be used for form validation with Bootstrap