Adding unique two-column index with already not unique data

Question

I have a rails app and need to add a unique constraint, so that a :record never has the same (:user, :hour) combination.

I imagine the best way to do this is by adding a unique index:

add_index :records, [:user_id, :hour], :unique => true

The problem is, the migration I wrote to do that fails, because my database already has non-unique combinations. How do I find those combinations?

This answer suggests "check with GROUP BY and COUNT" but I'm a total newbie, and I would love some help interpreting that.

Do I write a helper method to do that? Where in my app would that go? It's too complex to do it in the console, right? Or should I be looking at some sort of a script?

Thank you!

Answer 1

1. Run this query in your database console: SELECT *, COUNT(*) as n FROM table_name group by column_name HAVING n>1

2. Fix the duplicate rows

3. Re-run your migration

IMHO, you should edit your duplicate data manually so that you can be sure the data is correctly fixed.

Update:

OP didn't mention he/she is using Postgres and I gave a solution for MySQL.

For Postgres:

Based on this solution: Find duplicate rows with PostgreSQL

Run this query:

SELECT * FROM (
  SELECT id,
  ROW_NUMBER() OVER(PARTITION BY merchant_Id, url ORDER BY id asc) AS Row
  FROM Photos
) dups
WHERE 
dups.Row > 1

More explanation:

In order for you to execute the migration and add unique constraint to your columns, you need to fix the current data first. Usually, there's no automatic step for this in order to make sure you won't end up with incorrect data.

That's why you need to manually find the duplicate rows and fix it. The given query will show you which rows are duplicates. So, from there, fix the data and you should be able to run the migration.

Mooore update:

The duplicated rows do not get marked. For an example, if you get this kindo of result:

ID  ROW
235 2
236 3
2   2
3   3

You should select the row with id=235 and then select every row with the same column value as id=235. From there, you'll see every id which are duplicates from id=235. Then, just edit them one by one.