If you have a UNIQUE INDEX then you don’t also need the INDEX – it would be redundant. A UNIQUE INDEX is both a unique constraint and an index that can be used like any other index. From the documentation: Note: The preferred way to add a unique constraint to a table is ALTER TABLE … Read more
If you have duplicates in your table and you use ALTER TABLE mytable ADD UNIQUE INDEX myindex (A, B, C, D); the query will fail with Error 1062 (duplicate key). But if you use IGNORE — (only works before MySQL 5.7.4) ALTER IGNORE TABLE mytable ADD UNIQUE INDEX myindex (A, B, C, D); the duplicates … Read more
This MSDN article comparing the two is for SQL Server 2000: http://msdn.microsoft.com/en-us/library/aa224827(SQL.80).aspx For most purposes, there’s no difference – the constraint is implemented as an index under the covers. And though there’s the ability to disable the constraint, it doesn’t actually work in SQL Server. It only matters if you want to tweak things like … Read more
Add Unique Index on your table: ALTER IGNORE TABLE `TableA` ADD UNIQUE INDEX (`member_id`, `quiz_num`, `question_num`, `answer_num`); Another way to do this would be: Add primary key in your table then you can easily remove duplicates from your table using the following query: DELETE FROM member WHERE id IN (SELECT * FROM (SELECT id FROM … Read more
The unique piece is not where the difference lies. The index and key are not the same thing, and are not comparable. A key is a data column, or several columns, that are forced to be unique with a constraint, either primary key or explicitly defined unique constraint. Whereas an index is a structure for … Read more
Make a small function that returns random letters for a given length: <?php function generate_random_letters($length) { $random = ”; for ($i = 0; $i < $length; $i++) { $random .= chr(rand(ord(‘a’), ord(‘z’))); } return $random; } Then you’ll want to call that until it’s unique, in pseudo-code depending on where you’d store that information: do … Read more
It’s not that the index already exists, but that there are duplicate values of the TopicShortName field in the table itself. According to the error message the duplicate value is an empty string (it might just be a facet of posting I guess). Such duplicates prevent the creation of a UNIQUE index. You could run … Read more