Menu
i want to put unique index on two (or more) columns in a table, but i get "duplicate keys found". How to select those rows which cause duplication?
291
If two entities are connected via a many to many relationship, you create a link table, storing their foreign keys, so there is no table with 2 unique indexes. One instance can be used only once, so you must have 2 uniqie indices in a linked table (by each entity separately) to prevent use of instance already used.
A table can have more than one unique key unlike primary key. Unique key constraints can accept only one NULL value for column. Unique constraints are also referenced by the foreign key of another table.
A unique index ensures that the values in the index key columns are unique. A unique constraint also guarantees that no duplicate values can be inserted into the column(s) on which the constraint is created. When a unique constraint is created a corresponding unique index is automatically created on the column(s).
A unique index never has duplicate values.
You can use Group By and Having for this:
SELECT col1,
col2
FROM table
GROUP BY col1,
col2
HAVING Count(*) > 1
Basically - group the values, then filter for instances where there is more than one.
179
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
