Constraints: support deferred constraints [WAS: Make the Unique index ONLY evaluate immediately before the commit (NOT after each UPDATE)]

MariaDB [test]> CREATE TABLE `node` (

    -> `id` int(11) NOT NULL AUTO_INCREMENT,

    -> `parentId` int(11) NULL DEFAULT NULL,

    -> `orderIndex` int(5) NOT NULL,

    -> `label` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,

    -> PRIMARY KEY (`id`) USING BTREE,

    -> INDEX `FKparent`(`parentId`) USING BTREE,

    -> CONSTRAINT `FKparent` FOREIGN KEY (`parentId`) REFERENCES `node` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT

    -> ) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

Query OK, 0 rows affected (0.038 sec)

MariaDB [test]> INSERT INTO `node` VALUES (1, NULL, 0, 'root1');

Query OK, 1 row affected (0.014 sec)

MariaDB [test]> INSERT INTO `node` VALUES (2, 1, 0, 'child1a');

Query OK, 1 row affected (0.008 sec)

MariaDB [test]> INSERT INTO `node` VALUES (3, 1, 1, 'child1b');

Query OK, 1 row affected (0.013 sec)

MariaDB [test]> INSERT INTO `node` VALUES (4, 1, 2, 'child1c');

Query OK, 1 row affected (0.004 sec)

MariaDB [test]> INSERT INTO `node` VALUES (5, NULL, 0, 'root2');

Query OK, 1 row affected (0.006 sec)

MariaDB [test]> INSERT INTO `node` VALUES (6, 2, 0, 'child2a');

Query OK, 1 row affected (0.003 sec)

MariaDB [test]> INSERT INTO `node` VALUES (7, 2, 1, 'child2b');

Query OK, 1 row affected (0.005 sec)

MariaDB [test]> INSERT INTO `node` VALUES (8, 2, 2, 'child2c');

Query OK, 1 row affected (0.004 sec)

MariaDB [test]> 

MariaDB [test]> START TRANSACTION;

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> update node set orderindex=2 where id = 3;

Query OK, 1 row affected (0.000 sec)

Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test]> update node set orderindex=1 where id = 4;

Query OK, 1 row affected (0.001 sec)

Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [test]> COMMIT;

Query OK, 0 rows affected (0.004 sec)

MariaDB [test]> ALTER TABLE `node`

    -> ADD UNIQUE INDEX `UXorderIndex`(`parentId`, `orderIndex`);

Query OK, 0 rows affected (0.025 sec)

Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> START TRANSACTION;

Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> update node set orderindex=1 where id = 3;

ERROR 1062 (23000): Duplicate entry '1-1' for key 'UXorderIndex'

MariaDB [test]> update node set orderindex=2 where id = 4;

ERROR 1062 (23000): Duplicate entry '1-2' for key 'UXorderIndex'

MariaDB [test]> COMMIT;

Query OK, 0 rows affected (0.004 sec)