Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-26096

Make the Unique index ONLY evaluate immediately before the commit (NOT after each UPDATE)

    XMLWordPrintable

    Details

      Description

      Make the Unique index ONLY evaluate immediately before the commit and NOT after each UPDATE ("Deferred Constraints Check" )

      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)
      

      upstream feature request: https://bugs.mysql.com/bug.php?id=68360

      Also this feature should be implemented as a optional and not by default so if anyone wants to use the old way, they can.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              marko Marko Mäkelä
              Reporter:
              niljoshi Nilnandan Joshi
              Votes:
              1 Vote for this issue
              Watchers:
              8 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.