When updating rows, Dolt requires all values in the row to satisfy constraints, not just the values changed

[VinaiRachakonda]

Dolt currently represents partial updates as updating the entire row at once. Essentially we are evaluating violations in the case of the entire row instead of just referenced rows.

We should support updates of referenced rows. Example below:

mysql> select * from parent;
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> show create table child;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
                                                                                                                    |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| child | CREATE TABLE `child` (
  `a` int NOT NULL,
  `b` int DEFAULT NULL,
  `c` int DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `child_b` (`b`),
  KEY `child_c` (`c`),
  CONSTRAINT `child_ibfk_1` FOREIGN KEY (`b`) REFERENCES `parent` (`a`),
  CONSTRAINT `child_ibfk_2` FOREIGN KEY (`c`) REFERENCES `parent` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> set foreign_key_checks = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from child;
+-----+------+------+
| a   | b    | c    |
+-----+------+------+
| 100 |    1 |    1 |
| 101 |    2 |    2 |
+-----+------+------+
2 rows in set (0.00 sec)

mysql> update child set b = 1 where a = 101;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

[VinaiRachakonda]

Skipped test here #4014

[timsehn]

Just to be clear the example above is against MySQL. We fail the last update statement.

test-unique-reorder $ dolt sql <<SQL
> DROP TABLE IF EXISTS parent;
> DROP TABLE IF EXISTS child;
> CREATE TABLE parent(
>   a int PRIMARY KEY
> );
> CREATE TABLE child (
>   a int NOT NULL,
>   b int DEFAULT NULL,
>   c int DEFAULT NULL,
>   PRIMARY KEY (a),
>   KEY child_b (b),
>   KEY child_c (c),
>   CONSTRAINT child_ibfk_1 FOREIGN KEY (b) REFERENCES parent (a),
>   CONSTRAINT child_ibfk_2 FOREIGN KEY (c) REFERENCES parent (a)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
> INSERT INTO parent VALUES (1);
> SET FOREIGN_KEY_CHECKS=0;
> INSERT INTO child values (100, 1, 1), (101, 2, 2);
> SET FOREIGN_KEY_CHECKS=1;
> SQL
Query OK, 1 row affected
Query OK, 2 rows affected
test-unique-reorder $ dolt sql -q "update child set b = 1 where a = 101;"
error executing query on line 1: cannot add or update a child row - Foreign key violation on fk: `child_ibfk_2`, table: `child`, referenced table: `parent`, key: `[2]`

[timsehn]

This on the other hand works:

test-unique-reorder $ dolt sql -q "update child set b = 1, c=1 where a = 101;"
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

Because all constraints on the row are now satisfied.