Avoid inserting duplicate rows in mySQL

Question

I have a table with the identifier auto_inc (primary key). I am trying to avoid inserting duplicate rows.

Example of a repeating line:

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

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

Answer 1

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

 ALTER TABLE `table` ADD UNIQUE ( `a` , `b` , `c` ); 

Answer 2

You must use ON DUPLICATE KEY UPDATE and declare the fields unique.

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

Answer 3

You can also use this.

 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' ); 

Answer 4

Make a three-way compound key.