Bug #92027 Consider validating constraint violations at the end of each statement

• Submitted: 15 Aug 2018 19:37
• Reporter: Federico Razzoli (OCA)
• Status: Open
• Category: MySQL Server
• Severity: S4 (Feature request)
• OS: Any
• CPU Architecture: Any
• Tags: unique

[15 Aug 2018 19:37] Federico Razzoli

Description:
In most DBMSs, check violations are checked at the end of a statement by default. And they are optionally deferrable at transaction commit.

MySQL validates constraints after modifying each row. A couple of comments:

* In some cases, this forces us to add an ORDER BY clause to UPDATE, which sounds weird to whoever doesn't know MySQL (and to me, to be honest). And I can even show cases where ORDER BY won't help much.

* Is there any case in which the current behaviour is desirable?

I suggest moving the validation at the end of each statement.

Note that setting unique_checks to 0 doesn't solve the problem (try it with the snippets below) and is not really expected to.

How to repeat:
mysql> create table ti (id int auto_increment primary key, a int unique);
Query OK, 0 rows affected (0.12 sec)

mysql> insert into ti (a) values (3), (2), (1);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> UPDATE ti SET a = id;
ERROR 1062 (23000): Duplicate entry '1' for key 'a'