php/mysql prevent duplicate entries over multiple columns
duplicates, mysql, php
Solution
You can simply create a multiple-column index on these columns and enforce uniqueness: see the MySQL manual at http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html.
For example, in a table with columns `id` (unique primary key), `colA` and `colB`, you run:
ALTER TABLE table ADD UNIQUE KEY (colA,colB)
This is it: any INSERTs leading to a duplicate entry in these two columns combined will now return a MySQL error instead of going through. If you use `INSERT IGNORE`, no MySQL error will be thrown if executing it would violate this unique constraint, and your `INSERT` statement would be quietly disregarded.
Problem
I would like to come up with a standard practice to prevent any tables from having duplicates where it matters. In most cases duplicates are a combination of variables rather than one. My primary keys are just the unique ids for each field so I cannot use them. What I have been doing is querying the table first and then if the number of rows for the combination in question is 0, making the insert. However, I have read it should be possible to set up a unique key over multiple fields to enforce uniqueness. INSERT IGNORE sounds like a good possibility, however, I would need it to ignore on more than one column. As an example, with the fields followers and followed, there can be multiple followers and followeds in a table but should only be one combination of both. Can anyone suggest syntax first to create the unique keys over multiple fields and then to do a SQL insert query that prevents dupes? Many thanks.