0

I believe this line of code will help me create a unique id based on three columns, while ignoring any time the combination of these three columns is duplicated, yet ignore seems depreciated with mysql and I can not find another way to do this?

ALTER IGNORE TABLE `x` ADD UNIQUE INDEX `id`('student', 'class', 'session');
Nick
  • 138,499
  • 22
  • 57
  • 95
Mattattack
  • 179
  • 6
  • 21
  • This behaviour was removed in 5.7.4 (see [this](https://stackoverflow.com/questions/5456520/mysql-alter-ignore-table-add-unique-what-will-be-truncated)) – Nick Nov 04 '19 at 11:20
  • @Nick that seems to explain how ignore works, not an alternative method? – Mattattack Nov 04 '19 at 11:53
  • There isn't an alternate any more, you need to remove duplicates before you can create a unique key. In the older versions of MySQL it would do that for you when you used an `ALTER IGNORE` command. – Nick Nov 04 '19 at 11:59
  • Is there a way I can use an old mysql version to perform this statement? – Mattattack Nov 04 '19 at 12:01
  • You just need to emulate the action of the old statement by removing all the duplicates manually. There are *many* answers on stackoverflow that tell how to do that. – Nick Nov 04 '19 at 12:02
  • Please can you link me to one of these answers? I have been searching around for two weeks and am yet to find one that has worked well (hence why I have asked a question), I have around 30000 records and I am very unfamiliar with mysql. – Mattattack Nov 04 '19 at 12:20
  • https://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql – Nick Nov 04 '19 at 12:21
  • @Nick the problem I had with this, is that none of the rows are identical, however, in a case where three of the columns are the same as another row, then I would need to delete one of these. I finally thought I had found a solution with IGNORE. I have tried expressing this problem with another question, but I am still very stuck. https://stackoverflow.com/questions/58500201/select-the-first-time-three-unique-values-appear-with-sql/58503639#58503639 – Mattattack Nov 04 '19 at 12:24

0 Answers0