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.
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
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.
Method 2
To make unique index for text types you can use
CREATE UNIQUE INDEX indexname ON `tablename` (columnname (100));
(100) here after columnname tells mysql to check only first 100 bytes as unique, if you specify (4) as length then both
hello hello world
are same as index (both are actually hell in index tree) and mysql does not allow to insert them to table.
See here http://dev.mysql.com/doc/refman/5.0/en/create-index.html
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0