- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)