10

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.

ReinstateMonica3167040
  • 732
  • 9
  • 27
Alex Flom
  • 121
  • 1
  • 1
  • 8

4 Answers4

21

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

ALTER TABLE `table` ADD UNIQUE (
`a` ,
`b` ,
`c`
);
Lennart
  • 1,018
  • 1
  • 12
  • 27
9

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.

aleroot
  • 71,077
  • 30
  • 176
  • 213
  • but the field is not unique, for example it is fine to have field a=6 many times, what is not allowed is the combination of same values for a,b,c – Alex Flom Jan 06 '12 at 10:56
  • 2
    You should create a compound unique index : http://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql – aleroot Jan 06 '12 at 10:58
3

You can use this also.

INSERT INTO `tableName` ( `field1`, `field2`,`field3`)
SELECT `field1`, `field2`,`field3` FROM `tableName`
WHERE NOT EXISTS (SELECT 1
    FROM `tableName`
    WHERE 
    `field1` = 'value' AND `field2` = 'value'  AND `field3` = 'value'
    );
Nanhe Kumar
  • 15,498
  • 5
  • 79
  • 71
  • 1
    How could I do with dynamic value?, instead of SELECT 1, something like select * WHERE –  Jun 01 '18 at 14:07
-2

Make the three columns composite key.

Ankur
  • 2,171
  • 23
  • 29
Acn
  • 1,010
  • 2
  • 11
  • 22
  • @ypercubeᵀᴹ This has been fixed as of 20 June 2013. However, this answer was never needed as Lennart's answer on 6 Jan 2012 10:52 UTC took care of this already! – ReinstateMonica3167040 Jul 12 '18 at 23:25