- Trending Categories
- Data Structure
- Operating System
- C Programming
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Prevent a combination of items from being inserted twice in MySQL?
152 Lectures 16 hours
87 Lectures 5.5 hours
To prevent a combination of items from being inserted twice, alter the table and set UNIQUE for the column as shown in the below syntax −
alter table yourTableName add constraint yourConstraintName unique(yourColumnName1,yourColumnName2,....N);
Let us first create a table −
mysql> create table DemoTable ( Value1 int, Value2 int ); Query OK, 0 rows affected (0.47 sec)
Here is the query to prevent a combination of items from being inserted twice −
mysql> alter table DemoTable add constraint Value1_Value2_ConstraintKey unique(Value1,Value2); Query OK, 0 rows affected (0.80 sec) Records : 0 Duplicates : 0 Warnings : 0
Insert some records in the table using insert command. Here, when we try to insert duplicate combination, an error “Duplicate entry” is visible and the duplicate insertion won’t work −
mysql> insert into DemoTable values(10,10); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(10,20); Query OK, 1 row affected (0.11 sec) mysql> insert into DemoTable values(10,10); ERROR 1062 (23000) : Duplicate entry '10-10' for key 'Value1_Value2_ConstraintKey' mysql> insert into DemoTable values(20,10); Query OK, 1 row affected (0.18 sec) mysql> insert into DemoTable values(20,20); Query OK, 1 row affected (0.23 sec) mysql> insert into DemoTable values(20,10); ERROR 1062 (23000) : Duplicate entry '20-10' for key 'Value1_Value2_ConstraintKey'
Display all records from the table using select statement −
mysql> select *from DemoTable;
This will produce the following output −
+--------+--------+ | Value1 | Value2 | +--------+--------+ | 10 | 10 | | 10 | 20 | | 20 | 10 | | 20 | 20 | +--------+--------+ 4 rows in set (0.00 sec)
- How to Prevent Twitter Accounts from being Hacked
- WebGL: Prevent color buffer from being cleared in HTML5
- How can I prevent a window from being resized with Tkinter?
- How to prevent raw rice from being destroyed by termites?
- How to prevent a background process from being stopped after closing SSH client in Linux?
- Using LIKE clause twice in a MySQL query
- Access last inserted row in MySQL?
- How to prevent a user from accessing a specific schema in MySQL?
- How do I stop a MySQL decimal field from being rounded?
- Set how auto-placed items are inserted in the CSS grid
- Fetch records containing a specific character twice in MySQL
- How to prevent numbers being changed to exponential form in Python Matplotlib?
- Show column value twice in MySQL Select?
- How to create a combination of pairs from a vector in R?
- Prevent negative numbers in MySQL?