Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Avoiding inserting duplicate rows in mySQL

Tags:

database

mysql

I have a table with an auto_inc id (primary key). I am trying to avoid the insertion of duplicate rows.

Example of a duplicate row:

id  | field a | field b | field c |
1       4          6         7
2       4          6         7

The key (id) is not duplicate since it is automatically generated by MySQL, but all other fields are identical.

like image 755
Alex Flom Avatar asked Jan 06 '12 10:01

Alex Flom


2 Answers

Make a unique index on fields a,b,c.

ALTER TABLE `table` ADD UNIQUE (
`a` ,
`b` ,
`c`
);
like image 67
Lennart Avatar answered Oct 21 '22 02:10

Lennart


You should use ON DUPLICATE KEY UPDATE and declaring the fields as unique .

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed.

like image 20
aleroot Avatar answered Oct 21 '22 02:10

aleroot