Mysql temporarily suppress unique index

Question

I have a table with unique index on two columns, id_parent and sort_order to be precise

+----+-----------+------------+-------------+-------------+-------------+
| id | id_parent | sort_order | some_data   | other_data  | more_data   |
+----+-----------+------------+-------------+-------------+-------------+
| 1  |         1 |          1 | lorem ipsum | lorem ipsum | lorem ipsum |
| 2  |         1 |          2 | lorem ipsum | lorem ipsum | lorem ipsum |
| 3  |         1 |          3 | lorem ipsum | lorem ipsum | lorem ipsum |
+----+-----------+------------+-------------+-------------+-------------+

Now I want to update them, their data and their sort_order in one-go. sort_order would change from 1 - 2 - 3 to, for example 2 - 3 - 1.

But when I start running update statements, unique index block me, just as expected, saying that I can't have two rows with id_parent = 1 and sort_order = 2. Well, I could set it 4 for now, update other rows in correct order, and then set this one. But then, I would have to run an extra statement, and most probably add additional logic to my scripting language to determine correct order of updates. I also use ORM, and it becomes even more inconvinient.

My question now, is there some method to make mysql temporarily ignore this index? Like starting a special transaction, in which indexes would be calculated only right before commiting it?

Drop the unique key: https://stackoverflow.com/a/9172188/5381704. Later add it back. — Bishwas Mishra, Nov 10 '18 at 09:59

score 6 · Accepted Answer · answered Dec 07 '11 at 05:43

6

As far as I know that isn't possible.

The only time I've seen anything like that is that you can disable non unique keys on myisam tables. But not on InnoDB and not on unique keys.

However, to save you an update or two, there is no need to have the exact number 1, 2 and 3. You could as well have 4, 5 and 6. Right? You would use it in a order by and nothing else so the exact numbers aren't important. It will even save you an update if you're clever. From your example

update table set sort_order = 4 where sort_order = 1 and id = 1 and id_parent = 1;

New sort order is 2, 3, 1. And in just one update.

answered Dec 07 '11 at 05:43

Andreas Wederbrand

38,065
11
68
78

1

Shame on me, using 4-5-6 was so obivious. – Nameless Dec 07 '11 at 06:29
Agree about that being impossible with multiple updates. However updating them in a single statement might work. Either way, it's +1 for a solution that is clear and simple. – Andriy M Dec 07 '11 at 06:41

Simon East · Answer 2 · 2021-09-08T00:02:00.063

5

MyISAM

For MyISAM tables, you can simply add this line at the start of your script:

SET UNIQUE_CHECKS=0;

It's common to use this in conjunction with:

SET FOREIGN_KEY_CHECKS=0;

The UNIQUE_CHECKS variable is mentioned in the docs here:
http://dev.mysql.com/doc/refman/5.0/en/converting-tables-to-innodb.html

InnoDB

There seem to be reports that the above commands don't work with the InnoDB engine. If so, then you can try dropping the UNIQUE index temporarily and then adding it back.

See: How to remove unique key from mysql table

Feel free to edit this post and improve it with a code example if you have one.

edited Sep 08 '21 at 00:02

answered Jun 10 '14 at 03:09

Simon East

55,742
17
139
133

3

Just tried this on 5.6, does NOT work with InnoDB. Duplicate key still threw an error. – bluecollarcoder Jan 23 '15 at 06:55
@bluecollarcoder Did you try setting *both* `UNIQUE_CHECKS` and `FOREIGN_KEY_CHECKS` to zero? And are you sure you are not duplicating a *primary key* which is probably still not allowed? – Simon East Jan 28 '15 at 01:44
1

The unique key is on a column that does not reference any other table, so `FOREIGN_KEY_CHECKS` is irrelevant here. And the column with unique key is definitely not a part of the primary key. In what situation were you able to get `UNIQUE_CHECKS=0` to work as expected in this case? – bluecollarcoder Jan 28 '15 at 07:53
2

Quote from Mysql documentation: `unique_checks permits but does not require storage engines to ignore duplicate keys.` It would seem that Innodb does not support this feature. – bluecollarcoder Jan 28 '15 at 07:56
@Simon, Doesn't work for me too (windows 8.1, mysql 5.6.22). How can we **force** the behavior of `unique_checks=0`? – Pacerier Feb 23 '15 at 15:05
1

SET UNIQUE_CHECKS=0;-will not help you to temporarily suppress unique key. If set to 0,storage engines are allowed to assume that duplicate keys are not present in input data. If you know for certain that your data does not contain uniqueness violations, you can set this to 0. Note that setting this variable to 0 does not require storage engines to ignore duplicate keys,it will still issue duplicate-key errors if any detects – SwR Mar 05 '16 at 10:54
The problem is often your unique index is on a field that's also a foreign key. if you remove the FK first, then setting UNIQUE_CHECKS may work. You can also remove FK first, then remove your unique constraint, then add them back in reverse order. That's what I do sometimes. – Overclocked Mar 16 '16 at 13:28
I get 1062 error even after I do `SET UNIQUE_CHECKS=0;` and `SET FOREIGN_KEY_CHECKS=0;`. – posfan12 Dec 14 '19 at 13:04
@bluecollarcoder A column with a unique index also can be a foreign key! – Sebi2020 Sep 07 '21 at 11:37

score 5 · Answer 3 · answered Dec 07 '11 at 06:35

5

‘But when I start running update statements…’ – I understand, you tried updating the values using multiple UPDATE statement, like in a loop. Is that so? How about updating them in one go? Like this, for example:

UPDATE atable
SET sort_order = CASE sort_order WHEN 3 THEN 1 ELSE sort_order + 1 END
WHERE id_parent = 1
  AND sort_order BETWEEN 1 AND 3

A single statement is atomic, so, by the time this update ends, the values of sort_order, although changed, remain unique.

I can't test this in MySQL, sorry, but it definitely works in SQL Server, and I believe the behaviour respects the standards.

answered Dec 07 '11 at 06:35

Andriy M

76,112
17
94
154

Have not thought of it. Would be pain to use with ORM, though. This is closer to correct answer on specified question then answer by Andreas, but I probably will end up using his advice. What should I do with selected answer in this case? Give it to answer I'm going to use, or to answer which is closer on topic? – Nameless Dec 07 '11 at 06:39
Well, in case when more than one answer matches the *question*, you should probably go with the one that you are going to apply to solve the *actual problem*. So it's probably Andreas's answer that you should choose. Other considerations might exist, though. I'm fine with your decision, anyway! – Andriy M Dec 07 '11 at 06:44
4

That won't work unless the rows are actually updated in the correct order. No time during execution may the unique key be violated. `update sorttest set sort = sort+1 order by sort desc;` won't work without the descending sort, the first row would violate the key, even if the second row is about to be moved out of place. – Andreas Wederbrand Dec 07 '11 at 08:48
@AndreasWederbrand: Thanks for the feedback. I can't say anything with regard to MySQL, but my query does work in SQL Server. Your example works too, only without ORDER BY, as SQL Server doesn't allow ORDER BY in an UPDATE statement. You are right in saying that the unique key on the column gets violated during the execution, but (in SQL Server) the constraint doesn't come into effect until after the statement is done updating. And since the final data do not contain any duplicates, no exception occurs, and, consequently, no roll-back either. – Andriy M Dec 07 '11 at 09:00
Updating in one go also works well in Oracle. I feel frustrated with mysql. – DylanYi Jun 07 '12 at 12:12

Mysql temporarily suppress unique index

3 Answers3

MyISAM

InnoDB

Linked