github.com/dolthub/go-mysql-server@v0.18.0/enginetest/queries/foreign_key_queries.go (about) 1 // Copyright 2022 Dolthub, Inc. 2 // 3 // Licensed under the Apache License, Version 2.0 (the "License"); 4 // you may not use this file except in compliance with the License. 5 // You may obtain a copy of the License at 6 // 7 // http://www.apache.org/licenses/LICENSE-2.0 8 // 9 // Unless required by applicable law or agreed to in writing, software 10 // distributed under the License is distributed on an "AS IS" BASIS, 11 // WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 12 // See the License for the specific language governing permissions and 13 // limitations under the License. 14 15 package queries 16 17 import ( 18 "github.com/dolthub/go-mysql-server/sql" 19 "github.com/dolthub/go-mysql-server/sql/plan" 20 "github.com/dolthub/go-mysql-server/sql/types" 21 ) 22 23 // ForeignKeyTests will run the following statements BEFORE the SetUpScript: 24 // CREATE TABLE parent (id INT PRIMARY KEY, v1 INT, v2 INT, INDEX v1 (v1), INDEX v2 (v2)); 25 // CREATE TABLE child (id INT PRIMARY KEY, v1 INT, v2 INT); 26 var ForeignKeyTests = []ScriptTest{ 27 { 28 Name: "ALTER TABLE Single Named FOREIGN KEY", 29 SetUpScript: []string{ 30 "ALTER TABLE child ADD CONSTRAINT fk_named FOREIGN KEY (v1) REFERENCES parent(v1);", 31 }, 32 Assertions: []ScriptTestAssertion{ 33 { 34 Query: "SHOW CREATE TABLE child;", 35 Expected: []sql.Row{{"child", "CREATE TABLE `child` (\n `id` int NOT NULL,\n `v1` int,\n `v2` int,\n PRIMARY KEY (`id`),\n KEY `v1` (`v1`),\n CONSTRAINT `fk_named` FOREIGN KEY (`v1`) REFERENCES `parent` (`v1`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 36 }, 37 }, 38 }, 39 { 40 Name: "CREATE TABLE Single Named FOREIGN KEY", 41 SetUpScript: []string{ 42 "CREATE TABLE sibling (id int PRIMARY KEY, v1 int, CONSTRAINT fk_named FOREIGN KEY (v1) REFERENCES parent(v1));", 43 }, 44 Assertions: []ScriptTestAssertion{ 45 { 46 Query: "SHOW CREATE TABLE sibling;", 47 Expected: []sql.Row{{"sibling", "CREATE TABLE `sibling` (\n `id` int NOT NULL,\n `v1` int,\n PRIMARY KEY (`id`),\n KEY `v1` (`v1`),\n CONSTRAINT `fk_named` FOREIGN KEY (`v1`) REFERENCES `parent` (`v1`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 48 }, 49 }, 50 }, 51 { 52 Name: "Parent table index required", 53 Assertions: []ScriptTestAssertion{ 54 { 55 Query: "ALTER TABLE child ADD CONSTRAINT fk1 FOREIGN KEY (v1,v2) REFERENCES parent(v1,v2);", 56 ExpectedErr: sql.ErrForeignKeyMissingReferenceIndex, 57 }, 58 { 59 Query: "ALTER TABLE child ADD CONSTRAINT fk_id FOREIGN KEY (v1) REFERENCES parent(id);", 60 Expected: []sql.Row{{types.NewOkResult(0)}}, 61 }, 62 }, 63 }, 64 { 65 Name: "indexes with prefix lengths are ignored for foreign keys", 66 SetUpScript: []string{ 67 "create table prefixParent(v varchar(100), index(v(1)))", 68 }, 69 Assertions: []ScriptTestAssertion{ 70 { 71 Query: "create table prefixChild(v varchar(100), foreign key (v) references prefixParent(v))", 72 ExpectedErr: sql.ErrForeignKeyMissingReferenceIndex, 73 }, 74 }, 75 }, 76 { 77 Name: "CREATE TABLE Name Collision", 78 Assertions: []ScriptTestAssertion{ 79 { 80 Query: "CREATE TABLE child2 (id INT PRIMARY KEY, v1 INT, CONSTRAINT fk_name FOREIGN KEY (v1) REFERENCES parent(v1), CONSTRAINT fk_name FOREIGN KEY (v1) REFERENCES parent(v1));", 81 ExpectedErr: sql.ErrForeignKeyDuplicateName, 82 }, 83 }, 84 }, 85 { 86 Name: "CREATE TABLE Type Mismatch", 87 SetUpScript: []string{ 88 "CREATE TABLE sibling (pk INT PRIMARY KEY, v1 TIME);", 89 }, 90 Assertions: []ScriptTestAssertion{ 91 { 92 Query: "ALTER TABLE sibling ADD CONSTRAINT fk1 FOREIGN KEY (v1) REFERENCES parent(v1);", 93 ExpectedErr: sql.ErrForeignKeyColumnTypeMismatch, 94 }, 95 }, 96 }, 97 { 98 Name: "CREATE TABLE Type Mismatch special case for strings", 99 SetUpScript: []string{ 100 "CREATE TABLE parent1 (pk BIGINT PRIMARY KEY, v1 CHAR(20), INDEX (v1));", 101 "CREATE TABLE parent2 (pk BIGINT PRIMARY KEY, v1 VARCHAR(20), INDEX (v1));", 102 "CREATE TABLE parent3 (pk BIGINT PRIMARY KEY, v1 BINARY(20), INDEX (v1));", 103 "CREATE TABLE parent4 (pk BIGINT PRIMARY KEY, v1 VARBINARY(20), INDEX (v1));", 104 }, 105 Assertions: []ScriptTestAssertion{ 106 { 107 Query: "CREATE TABLE child1 (pk BIGINT PRIMARY KEY, v1 CHAR(30), CONSTRAINT fk_child1 FOREIGN KEY (v1) REFERENCES parent1 (v1));", 108 Expected: []sql.Row{{types.NewOkResult(0)}}, 109 }, 110 { 111 Query: "CREATE TABLE child2 (pk BIGINT PRIMARY KEY, v1 VARCHAR(30), CONSTRAINT fk_child2 FOREIGN KEY (v1) REFERENCES parent2 (v1));", 112 Expected: []sql.Row{{types.NewOkResult(0)}}, 113 }, 114 { 115 Query: "CREATE TABLE child3 (pk BIGINT PRIMARY KEY, v1 BINARY(30), CONSTRAINT fk_child3 FOREIGN KEY (v1) REFERENCES parent3 (v1));", 116 Expected: []sql.Row{{types.NewOkResult(0)}}, 117 }, { 118 Query: "CREATE TABLE child4 (pk BIGINT PRIMARY KEY, v1 VARBINARY(30), CONSTRAINT fk_child4 FOREIGN KEY (v1) REFERENCES parent4 (v1));", 119 Expected: []sql.Row{{types.NewOkResult(0)}}, 120 }, 121 }, 122 }, 123 { 124 Name: "CREATE TABLE Key Count Mismatch", 125 Assertions: []ScriptTestAssertion{ 126 { 127 Query: "ALTER TABLE child ADD CONSTRAINT fk1 FOREIGN KEY (v1) REFERENCES parent(v1, v2);", 128 ExpectedErr: sql.ErrForeignKeyColumnCountMismatch, 129 }, 130 { 131 Query: "ALTER TABLE child ADD CONSTRAINT fk1 FOREIGN KEY (v1, v2) REFERENCES parent(v1);", 132 ExpectedErr: sql.ErrForeignKeyColumnCountMismatch, 133 }, 134 }, 135 }, 136 { 137 Name: "SET DEFAULT not supported", 138 Assertions: []ScriptTestAssertion{ 139 { 140 Query: "ALTER TABLE child ADD CONSTRAINT fk1 FOREIGN KEY (v1) REFERENCES parent(v1) ON DELETE SET DEFAULT;", 141 ExpectedErr: sql.ErrForeignKeySetDefault, 142 }, 143 { 144 Query: "ALTER TABLE child ADD CONSTRAINT fk1 FOREIGN KEY (v1) REFERENCES parent(v1) ON UPDATE SET DEFAULT;", 145 ExpectedErr: sql.ErrForeignKeySetDefault, 146 }, 147 { 148 Query: "ALTER TABLE child ADD CONSTRAINT fk1 FOREIGN KEY (v1) REFERENCES parent(v1) ON UPDATE SET DEFAULT ON DELETE SET DEFAULT;", 149 ExpectedErr: sql.ErrForeignKeySetDefault, 150 }, 151 }, 152 }, 153 { 154 Name: "CREATE TABLE Disallow TEXT/BLOB", 155 SetUpScript: []string{ 156 "CREATE TABLE parent1 (id INT PRIMARY KEY, v1 TINYTEXT, v2 TEXT, v3 MEDIUMTEXT, v4 LONGTEXT);", 157 "CREATE TABLE parent2 (id INT PRIMARY KEY, v1 TINYBLOB, v2 BLOB, v3 MEDIUMBLOB, v4 LONGBLOB);", 158 }, 159 Assertions: []ScriptTestAssertion{ 160 { 161 Query: "CREATE TABLE child11 (id INT PRIMARY KEY, parent_v1 TINYTEXT, FOREIGN KEY (parent_v1) REFERENCES parent1(v1));", 162 ExpectedErr: sql.ErrForeignKeyTextBlob, 163 }, 164 { 165 Query: "CREATE TABLE child12 (id INT PRIMARY KEY, parent_v2 TEXT, FOREIGN KEY (parent_v2) REFERENCES parent1(v2));", 166 ExpectedErr: sql.ErrForeignKeyTextBlob, 167 }, 168 { 169 Query: "CREATE TABLE child13 (id INT PRIMARY KEY, parent_v3 MEDIUMTEXT, FOREIGN KEY (parent_v3) REFERENCES parent1(v3));", 170 ExpectedErr: sql.ErrForeignKeyTextBlob, 171 }, 172 { 173 Query: "CREATE TABLE child14 (id INT PRIMARY KEY, parent_v4 LONGTEXT, FOREIGN KEY (parent_v4) REFERENCES parent1(v4));", 174 ExpectedErr: sql.ErrForeignKeyTextBlob, 175 }, 176 { 177 Query: "CREATE TABLE child21 (id INT PRIMARY KEY, parent_v1 TINYBLOB, FOREIGN KEY (parent_v1) REFERENCES parent2(v1));", 178 ExpectedErr: sql.ErrForeignKeyTextBlob, 179 }, 180 { 181 Query: "CREATE TABLE child22 (id INT PRIMARY KEY, parent_v2 BLOB, FOREIGN KEY (parent_v2) REFERENCES parent2(v2));", 182 ExpectedErr: sql.ErrForeignKeyTextBlob, 183 }, 184 { 185 Query: "CREATE TABLE child23 (id INT PRIMARY KEY, parent_v3 MEDIUMBLOB, FOREIGN KEY (parent_v3) REFERENCES parent2(v3));", 186 ExpectedErr: sql.ErrForeignKeyTextBlob, 187 }, 188 { 189 Query: "CREATE TABLE child24 (id INT PRIMARY KEY, parent_v4 LONGBLOB, FOREIGN KEY (parent_v4) REFERENCES parent2(v4));", 190 ExpectedErr: sql.ErrForeignKeyTextBlob, 191 }, 192 }, 193 }, 194 { 195 Name: "CREATE TABLE Non-existent Table", 196 Assertions: []ScriptTestAssertion{ 197 { 198 Query: "ALTER TABLE child ADD CONSTRAINT fk1 FOREIGN KEY (v1) REFERENCES father(v1);", 199 ExpectedErr: sql.ErrTableNotFound, 200 }, 201 }, 202 }, 203 { 204 Name: "CREATE TABLE Non-existent Columns", 205 Assertions: []ScriptTestAssertion{ 206 { 207 Query: "ALTER TABLE child ADD CONSTRAINT fk1 FOREIGN KEY (random) REFERENCES parent(v1);", 208 ExpectedErr: sql.ErrTableColumnNotFound, 209 }, 210 { 211 Query: "ALTER TABLE child ADD CONSTRAINT fk1 FOREIGN KEY (v1) REFERENCES parent(random);", 212 ExpectedErr: sql.ErrTableColumnNotFound, 213 }, 214 }, 215 }, 216 { 217 Name: "ALTER TABLE Foreign Key Name Collision", 218 SetUpScript: []string{ 219 "ALTER TABLE child ADD CONSTRAINT fk_name FOREIGN KEY (v1) REFERENCES parent(v1);", 220 }, 221 Assertions: []ScriptTestAssertion{ 222 { 223 Query: "ALTER TABLE child ADD CONSTRAINT fk_name FOREIGN KEY (v1) REFERENCES parent(v1);", 224 ExpectedErr: sql.ErrForeignKeyDuplicateName, 225 }, 226 }, 227 }, 228 { 229 Name: "ALTER TABLE DROP FOREIGN KEY", 230 SetUpScript: []string{ 231 "ALTER TABLE child ADD CONSTRAINT fk_name FOREIGN KEY (v1) REFERENCES parent(v1);", 232 }, 233 Assertions: []ScriptTestAssertion{ 234 { 235 Query: "SHOW CREATE TABLE child;", 236 Expected: []sql.Row{{"child", "CREATE TABLE `child` (\n `id` int NOT NULL,\n `v1` int,\n `v2` int,\n PRIMARY KEY (`id`),\n KEY `v1` (`v1`),\n CONSTRAINT `fk_name` FOREIGN KEY (`v1`) REFERENCES `parent` (`v1`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 237 }, 238 { 239 Query: "ALTER TABLE child DROP FOREIGN KEY fk_name;", 240 Expected: []sql.Row{{types.NewOkResult(0)}}, 241 }, 242 { 243 Query: "SHOW CREATE TABLE child;", 244 Expected: []sql.Row{{"child", "CREATE TABLE `child` (\n `id` int NOT NULL,\n `v1` int,\n `v2` int,\n PRIMARY KEY (`id`),\n KEY `v1` (`v1`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 245 }, 246 { 247 Query: "ALTER TABLE child DROP FOREIGN KEY fk_name;", 248 ExpectedErr: sql.ErrForeignKeyNotFound, 249 }, 250 }, 251 }, 252 { 253 Name: "ALTER TABLE SET NULL on non-nullable column", 254 SetUpScript: []string{ 255 "ALTER TABLE child MODIFY v1 int NOT NULL;", 256 }, 257 Assertions: []ScriptTestAssertion{ 258 { 259 Query: "ALTER TABLE child ADD CONSTRAINT fk1 FOREIGN KEY (v1) REFERENCES parent(v1) ON DELETE SET NULL;", 260 ExpectedErr: sql.ErrForeignKeySetNullNonNullable, 261 }, 262 { 263 Query: "ALTER TABLE child ADD CONSTRAINT fk1 FOREIGN KEY (v1) REFERENCES parent(v1) ON UPDATE SET NULL;", 264 ExpectedErr: sql.ErrForeignKeySetNullNonNullable, 265 }, 266 { 267 Query: "ALTER TABLE child ADD CONSTRAINT fk1 FOREIGN KEY (v1) REFERENCES parent(v1) ON DELETE SET NULL ON UPDATE SET NULL;", 268 ExpectedErr: sql.ErrForeignKeySetNullNonNullable, 269 }, 270 }, 271 }, 272 { 273 Name: "ADD FOREIGN KEY fails on existing table when data would cause violation", 274 SetUpScript: []string{ 275 "INSERT INTO parent VALUES (1, 1, 1), (2, 2, 2);", 276 "INSERT INTO child VALUES (1, 1, 1), (2, 3, 2);", 277 }, 278 Assertions: []ScriptTestAssertion{ 279 { 280 Query: "ALTER TABLE child ADD CONSTRAINT fk_name FOREIGN KEY (v1) REFERENCES parent(v1)", 281 ExpectedErr: sql.ErrForeignKeyChildViolation, 282 }, 283 }, 284 }, 285 { 286 Name: "RENAME TABLE", 287 SetUpScript: []string{ 288 "ALTER TABLE child ADD CONSTRAINT fk_name FOREIGN KEY (v1) REFERENCES parent(v1);", 289 "RENAME TABLE parent TO new_parent;", 290 }, 291 Assertions: []ScriptTestAssertion{ 292 { 293 Query: "SHOW CREATE TABLE child;", 294 Expected: []sql.Row{{"child", "CREATE TABLE `child` (\n `id` int NOT NULL,\n `v1` int,\n `v2` int,\n PRIMARY KEY (`id`),\n KEY `v1` (`v1`),\n CONSTRAINT `fk_name` FOREIGN KEY (`v1`) REFERENCES `new_parent` (`v1`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 295 }, 296 { 297 Query: "RENAME TABLE child TO new_child;", 298 Expected: []sql.Row{{types.NewOkResult(0)}}, 299 }, 300 { 301 Query: "SHOW CREATE TABLE new_child;", 302 Expected: []sql.Row{{"new_child", "CREATE TABLE `new_child` (\n `id` int NOT NULL,\n `v1` int,\n `v2` int,\n PRIMARY KEY (`id`),\n KEY `v1` (`v1`),\n CONSTRAINT `fk_name` FOREIGN KEY (`v1`) REFERENCES `new_parent` (`v1`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 303 }, 304 }, 305 }, 306 { 307 Name: "RENAME TABLE with primary key indexes", 308 SetUpScript: []string{ 309 "CREATE TABLE parent1 (pk BIGINT PRIMARY KEY);", 310 "CREATE TABLE child1 (pk BIGINT PRIMARY KEY, CONSTRAINT `fk` FOREIGN KEY (pk) REFERENCES parent1(pk))", 311 "RENAME TABLE parent1 TO new_parent1;", 312 }, 313 Assertions: []ScriptTestAssertion{ 314 { 315 Query: "SHOW CREATE TABLE child1;", 316 Expected: []sql.Row{{"child1", "CREATE TABLE `child1` (\n `pk` bigint NOT NULL,\n PRIMARY KEY (`pk`),\n CONSTRAINT `fk` FOREIGN KEY (`pk`) REFERENCES `new_parent1` (`pk`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 317 }, 318 { 319 Query: "RENAME TABLE child1 TO new_child1;", 320 Expected: []sql.Row{{types.NewOkResult(0)}}, 321 }, 322 { 323 Query: "SHOW CREATE TABLE new_child1;", 324 Expected: []sql.Row{{"new_child1", "CREATE TABLE `new_child1` (\n `pk` bigint NOT NULL,\n PRIMARY KEY (`pk`),\n CONSTRAINT `fk` FOREIGN KEY (`pk`) REFERENCES `new_parent1` (`pk`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 325 }, 326 }, 327 }, 328 { 329 Name: "DROP TABLE", 330 SetUpScript: []string{ 331 "ALTER TABLE child ADD CONSTRAINT fk_name FOREIGN KEY (v1) REFERENCES parent(v1);", 332 }, 333 Assertions: []ScriptTestAssertion{ 334 { 335 Query: "DROP TABLE parent;", 336 ExpectedErr: sql.ErrForeignKeyDropTable, 337 }, 338 { 339 Query: "DROP TABLE child;", 340 Expected: []sql.Row{{types.NewOkResult(0)}}, 341 }, 342 { 343 Query: "DROP TABLE parent;", 344 Expected: []sql.Row{{types.NewOkResult(0)}}, 345 }, 346 }, 347 }, 348 { 349 Name: "DROP SELF REFERENCED TABLE", 350 SetUpScript: []string{ 351 "create table t ( i int primary key, j int, index(j), foreign key (j) references t(i));", 352 }, 353 Assertions: []ScriptTestAssertion{ 354 { 355 Query: "DROP TABLE t;", 356 Expected: []sql.Row{{types.NewOkResult(0)}}, 357 }, 358 }, 359 }, 360 { 361 Name: "Indexes used by foreign keys can't be dropped", 362 SetUpScript: []string{ 363 "ALTER TABLE child ADD INDEX v1 (v1);", 364 "ALTER TABLE child ADD CONSTRAINT fk_name FOREIGN KEY (v1) REFERENCES parent(v1);", 365 }, 366 Assertions: []ScriptTestAssertion{ 367 { 368 Query: "ALTER TABLE child DROP INDEX v1;", 369 ExpectedErr: sql.ErrForeignKeyDropIndex, 370 }, 371 { 372 Query: "ALTER TABLE parent DROP INDEX v1;", 373 ExpectedErr: sql.ErrForeignKeyDropIndex, 374 }, 375 { 376 Query: "ALTER TABLE child DROP FOREIGN KEY fk_name;", 377 Expected: []sql.Row{{types.NewOkResult(0)}}, 378 }, 379 { 380 Query: "ALTER TABLE child DROP INDEX v1;", 381 Expected: []sql.Row{{types.NewOkResult(0)}}, 382 }, 383 { 384 Query: "ALTER TABLE parent DROP INDEX v1;", 385 Expected: []sql.Row{{types.NewOkResult(0)}}, 386 }, 387 }, 388 }, 389 { 390 Name: "ALTER TABLE RENAME COLUMN", 391 SetUpScript: []string{ 392 "ALTER TABLE child ADD CONSTRAINT fk1 FOREIGN KEY (v1) REFERENCES parent(v1);", 393 "ALTER TABLE parent RENAME COLUMN v1 TO v1_new;", 394 "ALTER TABLE child RENAME COLUMN v1 TO v1_new;", 395 }, 396 Assertions: []ScriptTestAssertion{ 397 { 398 Query: "SHOW CREATE TABLE child;", 399 Expected: []sql.Row{{"child", "CREATE TABLE `child` (\n `id` int NOT NULL,\n `v1_new` int,\n `v2` int,\n PRIMARY KEY (`id`),\n KEY `v1` (`v1_new`),\n CONSTRAINT `fk1` FOREIGN KEY (`v1_new`) REFERENCES `parent` (`v1_new`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 400 }, 401 }, 402 }, 403 { 404 Name: "ALTER TABLE MODIFY COLUMN type change not allowed", 405 SetUpScript: []string{ 406 "ALTER TABLE child ADD CONSTRAINT fk1 FOREIGN KEY (v1) REFERENCES parent(v1);", 407 }, 408 Assertions: []ScriptTestAssertion{ 409 { 410 Query: "ALTER TABLE parent MODIFY v1 MEDIUMINT;", 411 ExpectedErr: sql.ErrForeignKeyTypeChange, 412 }, 413 { 414 Query: "ALTER TABLE child MODIFY v1 MEDIUMINT;", 415 ExpectedErr: sql.ErrForeignKeyTypeChange, 416 }, 417 }, 418 }, 419 { 420 Name: "ALTER TABLE MODIFY COLUMN type change allowed when lengthening string", 421 SetUpScript: []string{ 422 "CREATE TABLE parent1 (pk BIGINT PRIMARY KEY, v1 CHAR(20), INDEX (v1));", 423 "CREATE TABLE parent2 (pk BIGINT PRIMARY KEY, v1 VARCHAR(20), INDEX (v1));", 424 "CREATE TABLE parent3 (pk BIGINT PRIMARY KEY, v1 BINARY(20), INDEX (v1));", 425 "CREATE TABLE parent4 (pk BIGINT PRIMARY KEY, v1 VARBINARY(20), INDEX (v1));", 426 "CREATE TABLE child1 (pk BIGINT PRIMARY KEY, v1 CHAR(20), CONSTRAINT fk_child1 FOREIGN KEY (v1) REFERENCES parent1 (v1));", 427 "CREATE TABLE child2 (pk BIGINT PRIMARY KEY, v1 VARCHAR(20), CONSTRAINT fk_child2 FOREIGN KEY (v1) REFERENCES parent2 (v1));", 428 "CREATE TABLE child3 (pk BIGINT PRIMARY KEY, v1 BINARY(20), CONSTRAINT fk_child3 FOREIGN KEY (v1) REFERENCES parent3 (v1));", 429 "CREATE TABLE child4 (pk BIGINT PRIMARY KEY, v1 VARBINARY(20), CONSTRAINT fk_child4 FOREIGN KEY (v1) REFERENCES parent4 (v1));", 430 "INSERT INTO parent2 VALUES (1, 'aa'), (2, 'bb');", 431 "INSERT INTO child2 VALUES (1, 'aa');", 432 }, 433 Assertions: []ScriptTestAssertion{ 434 { 435 Query: "ALTER TABLE parent1 MODIFY v1 CHAR(10);", 436 ExpectedErr: sql.ErrForeignKeyTypeChange, 437 }, 438 { 439 Query: "ALTER TABLE child1 MODIFY v1 CHAR(10);", 440 ExpectedErr: sql.ErrForeignKeyTypeChange, 441 }, 442 { 443 Query: "ALTER TABLE parent2 MODIFY v1 VARCHAR(10);", 444 ExpectedErr: sql.ErrForeignKeyTypeChange, 445 }, 446 { 447 Query: "ALTER TABLE child2 MODIFY v1 VARCHAR(10);", 448 ExpectedErr: sql.ErrForeignKeyTypeChange, 449 }, 450 { 451 Query: "ALTER TABLE parent3 MODIFY v1 BINARY(10);", 452 ExpectedErr: sql.ErrForeignKeyTypeChange, 453 }, 454 { 455 Query: "ALTER TABLE child3 MODIFY v1 BINARY(10);", 456 ExpectedErr: sql.ErrForeignKeyTypeChange, 457 }, 458 { 459 Query: "ALTER TABLE parent4 MODIFY v1 VARBINARY(10);", 460 ExpectedErr: sql.ErrForeignKeyTypeChange, 461 }, 462 { 463 Query: "ALTER TABLE child4 MODIFY v1 VARBINARY(10);", 464 ExpectedErr: sql.ErrForeignKeyTypeChange, 465 }, 466 { 467 Query: "ALTER TABLE parent1 MODIFY v1 CHAR(30);", 468 Expected: []sql.Row{{types.NewOkResult(0)}}, 469 }, 470 { 471 Query: "ALTER TABLE child1 MODIFY v1 CHAR(30);", 472 Expected: []sql.Row{{types.NewOkResult(0)}}, 473 }, 474 { 475 Query: "ALTER TABLE parent2 MODIFY v1 VARCHAR(30);", 476 Expected: []sql.Row{{types.NewOkResult(0)}}, 477 }, 478 { 479 Query: "ALTER TABLE child2 MODIFY v1 VARCHAR(30);", 480 Expected: []sql.Row{{types.NewOkResult(0)}}, 481 }, 482 { 483 Query: "ALTER TABLE parent3 MODIFY v1 BINARY(30);", 484 Expected: []sql.Row{{types.NewOkResult(0)}}, 485 }, 486 { 487 Query: "ALTER TABLE child3 MODIFY v1 BINARY(30);", 488 Expected: []sql.Row{{types.NewOkResult(0)}}, 489 }, 490 { 491 Query: "ALTER TABLE parent4 MODIFY v1 VARBINARY(30);", 492 Expected: []sql.Row{{types.NewOkResult(0)}}, 493 }, 494 { 495 Query: "ALTER TABLE child4 MODIFY v1 VARBINARY(30);", 496 Expected: []sql.Row{{types.NewOkResult(0)}}, 497 }, 498 { // Make sure the type change didn't cause INSERTs to break or some other strange behavior 499 Query: "INSERT INTO child2 VALUES (2, 'bb');", 500 Expected: []sql.Row{{types.NewOkResult(1)}}, 501 }, 502 { 503 Query: "INSERT INTO child2 VALUES (3, 'cc');", 504 ExpectedErr: sql.ErrForeignKeyChildViolation, 505 }, 506 }, 507 }, 508 { 509 Name: "ALTER TABLE MODIFY COLUMN type change only cares about foreign key columns", 510 SetUpScript: []string{ 511 "CREATE TABLE parent1 (pk INT PRIMARY KEY, v1 INT UNSIGNED, v2 INT UNSIGNED, INDEX (v1));", 512 "CREATE TABLE child1 (pk INT PRIMARY KEY, v1 INT UNSIGNED, v2 INT UNSIGNED, CONSTRAINT fk_name FOREIGN KEY (v1) REFERENCES parent1(v1));", 513 "INSERT INTO parent1 VALUES (1, 2, 3), (4, 5, 6);", 514 "INSERT INTO child1 VALUES (7, 2, 9);", 515 }, 516 Assertions: []ScriptTestAssertion{ 517 { 518 Query: "ALTER TABLE parent1 MODIFY v1 BIGINT;", 519 ExpectedErr: sql.ErrForeignKeyTypeChange, 520 }, 521 { 522 Query: "ALTER TABLE child1 MODIFY v1 BIGINT;", 523 ExpectedErr: sql.ErrForeignKeyTypeChange, 524 }, 525 { 526 Query: "ALTER TABLE parent1 MODIFY v2 BIGINT;", 527 Expected: []sql.Row{{types.NewOkResult(0)}}, 528 }, 529 { 530 Query: "ALTER TABLE child1 MODIFY v2 BIGINT;", 531 Expected: []sql.Row{{types.NewOkResult(0)}}, 532 }, 533 }, 534 }, 535 { 536 Name: "DROP COLUMN parent", 537 SetUpScript: []string{ 538 "ALTER TABLE child ADD CONSTRAINT fk_name FOREIGN KEY (v1) REFERENCES parent(v1);", 539 }, 540 Assertions: []ScriptTestAssertion{ 541 { 542 Query: "ALTER TABLE parent DROP COLUMN v1;", 543 ExpectedErr: sql.ErrForeignKeyDropColumn, 544 }, 545 { 546 Query: "ALTER TABLE child DROP FOREIGN KEY fk_name;", 547 Expected: []sql.Row{{types.NewOkResult(0)}}, 548 }, 549 { 550 Query: "ALTER TABLE parent DROP COLUMN v1;", 551 Expected: []sql.Row{{types.NewOkResult(0)}}, 552 }, 553 }, 554 }, 555 { 556 Name: "DROP COLUMN child", 557 SetUpScript: []string{ 558 "ALTER TABLE child ADD CONSTRAINT fk_name FOREIGN KEY (v1) REFERENCES parent(v1);", 559 }, 560 Assertions: []ScriptTestAssertion{ 561 { 562 Query: "ALTER TABLE child DROP COLUMN v1;", 563 ExpectedErr: sql.ErrForeignKeyDropColumn, 564 }, 565 { 566 Query: "ALTER TABLE child DROP FOREIGN KEY fk_name;", 567 Expected: []sql.Row{{types.NewOkResult(0)}}, 568 }, 569 { 570 Query: "ALTER TABLE child DROP COLUMN v1;", 571 Expected: []sql.Row{{types.NewOkResult(0)}}, 572 }, 573 }, 574 }, 575 { 576 Name: "Disallow change column to nullable with ON UPDATE SET NULL", 577 SetUpScript: []string{ 578 "ALTER TABLE child ADD CONSTRAINT fk_name FOREIGN KEY (v1) REFERENCES parent(v1) ON UPDATE SET NULL", 579 }, 580 Assertions: []ScriptTestAssertion{ 581 { 582 Query: "ALTER TABLE child CHANGE COLUMN v1 v1 INT NOT NULL;", 583 ExpectedErr: sql.ErrForeignKeyTypeChangeSetNull, 584 }, 585 }, 586 }, 587 { 588 Name: "Disallow change column to nullable with ON DELETE SET NULL", 589 SetUpScript: []string{ 590 "ALTER TABLE child ADD CONSTRAINT fk_name FOREIGN KEY (v1) REFERENCES parent(v1) ON DELETE SET NULL", 591 }, 592 Assertions: []ScriptTestAssertion{ 593 { 594 Query: "ALTER TABLE child CHANGE COLUMN v1 v1 INT NOT NULL;", 595 ExpectedErr: sql.ErrForeignKeyTypeChangeSetNull, 596 }, 597 }, 598 }, 599 { 600 Name: "SQL CASCADE", 601 SetUpScript: []string{ 602 "CREATE TABLE one (pk BIGINT PRIMARY KEY, v1 BIGINT, v2 BIGINT, INDEX v1 (v1));", 603 "CREATE TABLE two (pk BIGINT PRIMARY KEY, v1 BIGINT, v2 BIGINT, INDEX v1v2 (v1, v2), CONSTRAINT fk_name_1 FOREIGN KEY (v1) REFERENCES one(v1) ON DELETE CASCADE ON UPDATE CASCADE);", 604 "CREATE TABLE three (pk BIGINT PRIMARY KEY, v1 BIGINT, v2 BIGINT, CONSTRAINT fk_name_2 FOREIGN KEY (v1, v2) REFERENCES two(v1, v2) ON DELETE CASCADE ON UPDATE CASCADE);", 605 "INSERT INTO one VALUES (1, 1, 4), (2, 2, 5), (3, 3, 6), (4, 4, 5);", 606 "INSERT INTO two VALUES (2, 1, 1), (3, 2, 2), (4, 3, 3), (5, 4, 4);", 607 "INSERT INTO three VALUES (3, 1, 1), (4, 2, 2), (5, 3, 3), (6, 4, 4);", 608 "UPDATE one SET v1 = v1 + v2;", 609 "DELETE one FROM one WHERE pk = 3;", 610 "UPDATE two SET v2 = v1 - 2;", 611 }, 612 Assertions: []ScriptTestAssertion{ 613 { 614 Query: "SELECT * FROM one;", 615 Expected: []sql.Row{{1, 5, 4}, {2, 7, 5}, {4, 9, 5}}, 616 }, 617 { 618 Query: "SELECT * FROM two;", 619 Expected: []sql.Row{{2, 5, 3}, {3, 7, 5}}, 620 }, 621 { 622 Query: "SELECT * FROM three;", 623 Expected: []sql.Row{{3, 5, 3}, {4, 7, 5}}, 624 }, 625 }, 626 }, 627 { 628 Name: "SQL SET NULL", 629 SetUpScript: []string{ 630 "CREATE TABLE one (pk BIGINT PRIMARY KEY, v1 BIGINT, v2 BIGINT, INDEX v1 (v1));", 631 "CREATE TABLE two (pk BIGINT PRIMARY KEY, v1 BIGINT, v2 BIGINT, CONSTRAINT fk_name_1 FOREIGN KEY (v1) REFERENCES one(v1) ON DELETE SET NULL ON UPDATE SET NULL);", 632 "INSERT INTO one VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3);", 633 "INSERT INTO two VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3);", 634 "UPDATE one SET v1 = v1 * v2;", 635 "INSERT INTO one VALUES (4, 4, 4);", 636 "INSERT INTO two VALUES (4, 4, 4);", 637 "UPDATE one SET v2 = v1 * v2;", 638 }, 639 Assertions: []ScriptTestAssertion{ 640 { 641 Query: "SELECT * FROM one;", 642 Expected: []sql.Row{{1, 1, 1}, {2, 4, 8}, {3, 9, 27}, {4, 4, 16}}, 643 }, 644 { 645 Query: "SELECT * FROM two;", 646 Expected: []sql.Row{{1, 1, 1}, {2, nil, 2}, {3, nil, 3}, {4, 4, 4}}, 647 }, 648 { 649 Query: "DELETE one FROM one inner join two on one.pk=two.pk;", 650 Expected: []sql.Row{{types.NewOkResult(4)}}, 651 }, 652 { 653 Query: "select * from two;", 654 Expected: []sql.Row{{1, nil, 1}, {2, nil, 2}, {3, nil, 3}, {4, nil, 4}}, 655 }, 656 }, 657 }, 658 { 659 Name: "SQL RESTRICT", 660 SetUpScript: []string{ 661 "CREATE TABLE one (pk BIGINT PRIMARY KEY, v1 BIGINT, v2 BIGINT, INDEX v1 (v1));", 662 "CREATE TABLE two (pk BIGINT PRIMARY KEY, v1 BIGINT, v2 BIGINT, CONSTRAINT fk_name_1 FOREIGN KEY (v1) REFERENCES one(v1) ON DELETE RESTRICT ON UPDATE RESTRICT);", 663 "INSERT INTO one VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3);", 664 "INSERT INTO two VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3);", 665 }, 666 Assertions: []ScriptTestAssertion{ 667 { 668 Query: "UPDATE one SET v1 = v1 + v2;", 669 ExpectedErr: sql.ErrForeignKeyParentViolation, 670 }, 671 { 672 Query: "UPDATE one SET v1 = v1;", 673 Expected: []sql.Row{{types.OkResult{Info: plan.UpdateInfo{Matched: 3}}}}, 674 }, 675 { 676 Query: "DELETE FROM one;", 677 ExpectedErr: sql.ErrForeignKeyParentViolation, 678 }, 679 { 680 Query: "DELETE one FROM one inner join two on one.pk=two.pk;", 681 ExpectedErr: sql.ErrForeignKeyParentViolation, 682 }, 683 { 684 Query: "DELETE one, two FROM one inner join two on one.pk=two.pk;", 685 ExpectedErr: sql.ErrForeignKeyParentViolation, 686 }, 687 }, 688 }, 689 { 690 Name: "Multi-table DELETE FROM JOIN with multiple foreign keys", 691 SetUpScript: []string{ 692 "CREATE TABLE one (pk int PRIMARY KEY);", 693 "CREATE TABLE two (pk int PRIMARY KEY);", 694 "CREATE TABLE three (pk int PRIMARY KEY, fk3 int, CONSTRAINT fk_3 FOREIGN KEY (fk3) REFERENCES one(pk) ON DELETE CASCADE);", 695 "CREATE TABLE four (pk int PRIMARY KEY, fk4 int, CONSTRAINT fk_4 FOREIGN KEY (fk4) REFERENCES two(pk) ON DELETE CASCADE);", 696 "INSERT INTO one VALUES (1), (2), (3);", 697 "INSERT INTO two VALUES (1), (2), (3);", 698 "INSERT INTO three VALUES (1, 1), (2, 2), (3, 3);", 699 "INSERT INTO four VALUES (1, 1), (2, 2), (3, 3);", 700 "DELETE one, two FROM one inner join two on one.pk=two.pk", 701 }, 702 Assertions: []ScriptTestAssertion{ 703 { 704 Query: "SELECT * from three union all select * from four;", 705 Expected: []sql.Row{}, 706 }, 707 }, 708 }, 709 { 710 Name: "Single-table DELETE FROM JOIN with multiple foreign keys", 711 SetUpScript: []string{ 712 "CREATE TABLE one (pk int PRIMARY KEY);", 713 "CREATE TABLE two (pk int PRIMARY KEY);", 714 "CREATE TABLE three (pk int PRIMARY KEY, fk3 int, CONSTRAINT fk_3 FOREIGN KEY (fk3) REFERENCES one(pk) ON DELETE CASCADE);", 715 "CREATE TABLE four (pk int PRIMARY KEY, fk4 int, CONSTRAINT fk_4 FOREIGN KEY (fk4) REFERENCES two(pk) ON DELETE CASCADE);", 716 "INSERT INTO one VALUES (1), (2), (3);", 717 "INSERT INTO two VALUES (1), (2), (3);", 718 "INSERT INTO three VALUES (1, 1), (2, 2), (3, 3);", 719 "INSERT INTO four VALUES (1, 1), (2, 2), (3, 3);", 720 "DELETE t1 FROM one t1 inner join two t2 on t1.pk=t2.pk", 721 }, 722 Assertions: []ScriptTestAssertion{ 723 { 724 Query: "SELECT * from three;", 725 Expected: []sql.Row{}, 726 }, 727 { 728 Query: "select * from four;", 729 Expected: []sql.Row{{1, 1}, {2, 2}, {3, 3}}, 730 }, 731 }, 732 }, 733 { 734 Name: "SQL no reference options", 735 SetUpScript: []string{ 736 "CREATE TABLE one (pk BIGINT PRIMARY KEY, v1 BIGINT, v2 BIGINT, INDEX v1 (v1));", 737 "CREATE TABLE two (pk BIGINT PRIMARY KEY, v1 BIGINT, v2 BIGINT, CONSTRAINT fk_name_1 FOREIGN KEY (v1) REFERENCES one(v1));", 738 "INSERT INTO one VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3);", 739 "INSERT INTO two VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3);", 740 }, 741 Assertions: []ScriptTestAssertion{ 742 { 743 Query: "UPDATE one SET v1 = v1 + v2;", 744 ExpectedErr: sql.ErrForeignKeyParentViolation, 745 }, 746 { 747 Query: "UPDATE one SET v1 = v1;", 748 Expected: []sql.Row{{types.OkResult{Info: plan.UpdateInfo{Matched: 3}}}}, 749 }, 750 { 751 Query: "DELETE FROM one;", 752 ExpectedErr: sql.ErrForeignKeyParentViolation, 753 }, 754 }, 755 }, 756 { 757 Name: "SQL INSERT multiple keys violates only one", 758 SetUpScript: []string{ 759 "CREATE TABLE one (pk BIGINT PRIMARY KEY, v1 BIGINT, v2 BIGINT, INDEX v1 (v1), INDEX v2 (v2));", 760 "CREATE TABLE two (pk BIGINT PRIMARY KEY, v1 BIGINT, v2 BIGINT, CONSTRAINT fk_name_1 FOREIGN KEY (v1) REFERENCES one(v1), CONSTRAINT fk_name_2 FOREIGN KEY (v2) REFERENCES one(v2));", 761 "INSERT INTO one VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3);", 762 "INSERT INTO two VALUES (1, NULL, 1);", 763 }, 764 Assertions: []ScriptTestAssertion{ 765 { 766 Query: "INSERT INTO two VALUES (2, NULL, 4);", 767 ExpectedErr: sql.ErrForeignKeyChildViolation, 768 }, 769 { 770 Query: "INSERT INTO two VALUES (3, 4, NULL);", 771 ExpectedErr: sql.ErrForeignKeyChildViolation, 772 }, 773 { 774 Query: "INSERT INTO two VALUES (4, NULL, NULL);", 775 Expected: []sql.Row{{types.NewOkResult(1)}}, 776 }, 777 }, 778 }, 779 { 780 // We differ from MySQL here as we do not allow duplicate indexes (required in MySQL to reference the same 781 // column in self-referential) but we do reuse existing indexes (MySQL requires unique indexes for parent and 782 // child rows). 783 Name: "Self-referential same column(s)", 784 SetUpScript: []string{ 785 "CREATE INDEX v1v2 ON parent(v1, v2);", 786 "CREATE TABLE parent2 (id INT PRIMARY KEY, v1 INT, v2 INT, INDEX v1v2 (v1, v2));", 787 }, 788 Assertions: []ScriptTestAssertion{ 789 { 790 Query: "ALTER TABLE parent ADD CONSTRAINT fk_name1 FOREIGN KEY (v1) REFERENCES parent(v1);", 791 Expected: []sql.Row{{types.NewOkResult(0)}}, 792 }, 793 { 794 Query: "ALTER TABLE parent ADD CONSTRAINT fk_name2 FOREIGN KEY (v1, v2) REFERENCES parent(v1, v2);", 795 Expected: []sql.Row{{types.NewOkResult(0)}}, 796 }, 797 }, 798 }, 799 { 800 Name: "Self-referential child column follows parent RESTRICT", 801 SetUpScript: []string{ 802 "ALTER TABLE parent ADD CONSTRAINT fk_named FOREIGN KEY (v2) REFERENCES parent(v1);", // default reference option is RESTRICT 803 "INSERT INTO parent VALUES (1, 1, 1), (2, 2, 1), (3, 3, NULL);", 804 "UPDATE parent SET v1 = 1 WHERE id = 1;", 805 "UPDATE parent SET v1 = 4 WHERE id = 3;", 806 "DELETE FROM parent WHERE id = 3;", 807 }, 808 Assertions: []ScriptTestAssertion{ 809 { 810 Query: "SELECT * FROM parent;", 811 Expected: []sql.Row{{1, 1, 1}, {2, 2, 1}}, 812 }, 813 { 814 Query: "DELETE FROM parent WHERE v1 = 1;", 815 ExpectedErr: sql.ErrForeignKeyParentViolation, 816 }, 817 { 818 Query: "UPDATE parent SET v1 = 2;", 819 ExpectedErr: sql.ErrForeignKeyParentViolation, 820 }, 821 { 822 Query: "REPLACE INTO parent VALUES (1, 1, 1);", 823 ExpectedErr: sql.ErrForeignKeyParentViolation, 824 }, 825 }, 826 }, 827 { 828 Name: "Self-referential child column follows parent CASCADE", 829 SetUpScript: []string{ 830 "ALTER TABLE parent ADD CONSTRAINT fk_named FOREIGN KEY (v2) REFERENCES parent(v1) ON UPDATE CASCADE ON DELETE CASCADE;", 831 "INSERT INTO parent VALUES (1, 1, 1), (2, 2, 1), (3, 3, NULL);", 832 "UPDATE parent SET v1 = 1 WHERE id = 1;", 833 "UPDATE parent SET v1 = 4 WHERE id = 3;", 834 "DELETE FROM parent WHERE id = 3;", 835 }, 836 Assertions: []ScriptTestAssertion{ 837 { 838 Query: "UPDATE parent SET v1 = 2;", 839 ExpectedErr: sql.ErrForeignKeyParentViolation, 840 }, 841 { 842 Query: "REPLACE INTO parent VALUES (1, 1, 1), (2, 2, 2);", 843 Expected: []sql.Row{{types.NewOkResult(3)}}, 844 }, 845 { 846 Query: "SELECT * FROM parent;", 847 Expected: []sql.Row{{1, 1, 1}, {2, 2, 2}}, 848 }, 849 { 850 Query: "UPDATE parent SET v1 = 2;", 851 ExpectedErr: sql.ErrForeignKeyParentViolation, 852 }, 853 { 854 Query: "SELECT * FROM parent order by v1;", 855 Expected: []sql.Row{{1, 1, 1}, {2, 2, 2}}, 856 }, 857 { 858 Query: "UPDATE parent SET v1 = 2 WHERE id = 1;", 859 ExpectedErr: sql.ErrForeignKeyParentViolation, 860 }, 861 { 862 Query: "SELECT * FROM parent order by v1;", 863 Expected: []sql.Row{{1, 1, 1}, {2, 2, 2}}, 864 }, 865 { 866 Query: "REPLACE INTO parent VALUES (1, 1, 2), (2, 2, 1);", 867 ExpectedErr: sql.ErrForeignKeyChildViolation, 868 }, 869 { 870 Query: "SELECT * FROM parent order by v1;", 871 Expected: []sql.Row{{1, 1, 1}, {2, 2, 2}}, 872 }, 873 { 874 Query: "UPDATE parent SET v2 = 2 WHERE id = 1;", 875 Expected: []sql.Row{{types.OkResult{RowsAffected: 1, Info: plan.UpdateInfo{Matched: 1, Updated: 1}}}}, 876 }, 877 { 878 Query: "UPDATE parent SET v2 = 1 WHERE id = 2;", 879 Expected: []sql.Row{{types.OkResult{RowsAffected: 1, Info: plan.UpdateInfo{Matched: 1, Updated: 1}}}}, 880 }, 881 { 882 Query: "SELECT * FROM parent order by v1;", 883 Expected: []sql.Row{{1, 1, 2}, {2, 2, 1}}, 884 }, 885 { 886 Query: "UPDATE parent SET v1 = 2;", 887 ExpectedErr: sql.ErrForeignKeyParentViolation, 888 }, 889 { 890 Query: "UPDATE parent SET v1 = 2 WHERE id = 1;", 891 ExpectedErr: sql.ErrForeignKeyParentViolation, 892 }, 893 { 894 Query: "DELETE FROM parent WHERE v1 = 1;", 895 Expected: []sql.Row{{types.NewOkResult(1)}}, 896 }, 897 { 898 Query: "SELECT * FROM parent;", 899 Expected: []sql.Row{}, 900 }, 901 }, 902 }, 903 { 904 Name: "Self-referential child column follows parent SET NULL", 905 SetUpScript: []string{ 906 "ALTER TABLE parent ADD CONSTRAINT fk_named FOREIGN KEY (v2) REFERENCES parent(v1) ON UPDATE SET NULL ON DELETE SET NULL;", 907 "INSERT INTO parent VALUES (1,1,1), (2, 2, 1), (3, 3, NULL);", 908 "UPDATE parent SET v1 = 1 WHERE id = 1;", 909 "UPDATE parent SET v1 = 4 WHERE id = 3;", 910 "DELETE FROM parent WHERE id = 3;", 911 }, 912 Assertions: []ScriptTestAssertion{ 913 { 914 Query: "UPDATE parent SET v1 = 2;", 915 ExpectedErr: sql.ErrForeignKeyParentViolation, 916 }, 917 { 918 Query: "REPLACE INTO parent VALUES (1, 1, 1), (2, 2, 2);", 919 Expected: []sql.Row{{types.NewOkResult(4)}}, 920 }, 921 { 922 Query: "SELECT * FROM parent;", 923 Expected: []sql.Row{{1, 1, 1}, {2, 2, 2}}, 924 }, 925 { 926 Query: "UPDATE parent SET v1 = 2;", 927 ExpectedErr: sql.ErrForeignKeyParentViolation, 928 }, 929 { 930 Query: "UPDATE parent SET v1 = 2 WHERE id = 1;", 931 ExpectedErr: sql.ErrForeignKeyParentViolation, 932 }, 933 { 934 Query: "REPLACE INTO parent VALUES (1,1,2), (2,2,1);", 935 Expected: []sql.Row{{types.NewOkResult(4)}}, 936 }, 937 { 938 Query: "SELECT * FROM parent;", 939 Expected: []sql.Row{{1, 1, nil}, {2, 2, 1}}, 940 }, 941 { 942 Query: "UPDATE parent SET v2 = 2 WHERE id = 1;", 943 Expected: []sql.Row{{types.OkResult{RowsAffected: 1, Info: plan.UpdateInfo{Matched: 1, Updated: 1}}}}, 944 }, 945 { 946 Query: "UPDATE parent SET v2 = 1 WHERE id = 2;", 947 Expected: []sql.Row{{types.OkResult{RowsAffected: 0, Info: plan.UpdateInfo{Matched: 1}}}}, 948 }, 949 { 950 Query: "SELECT * FROM parent;", 951 Expected: []sql.Row{{1, 1, 2}, {2, 2, 1}}, 952 }, 953 { 954 Query: "UPDATE parent SET v1 = 2;", 955 ExpectedErr: sql.ErrForeignKeyParentViolation, 956 }, 957 { 958 Query: "UPDATE parent SET v1 = 2 WHERE id = 1;", 959 ExpectedErr: sql.ErrForeignKeyParentViolation, 960 }, 961 { 962 Query: "DELETE FROM parent WHERE v1 = 1;", 963 Expected: []sql.Row{{types.NewOkResult(1)}}, 964 }, 965 { 966 Query: "SELECT * FROM parent;", 967 Expected: []sql.Row{{2, 2, nil}}, 968 }, 969 }, 970 }, 971 { 972 // Self-referential foreign key analysis time used to take an exponential amount of time, roughly equivalent to: 973 // number_of_foreign_keys ^ 15, so this verifies that it no longer does this (as the test would take years to run) 974 Name: "Multiple self-referential foreign keys without data", 975 SetUpScript: []string{ 976 "CREATE TABLE test (pk BIGINT PRIMARY KEY, v1 BIGINT UNIQUE, v2 BIGINT UNIQUE, v3 BIGINT UNIQUE, v4 BIGINT UNIQUE," + 977 "v5 BIGINT UNIQUE, v6 BIGINT UNIQUE, v7 BIGINT UNIQUE," + 978 "CONSTRAINT fk1 FOREIGN KEY (v1) REFERENCES test (pk)," + 979 "CONSTRAINT fk2 FOREIGN KEY (v2) REFERENCES test (pk)," + 980 "CONSTRAINT fk3 FOREIGN KEY (v3) REFERENCES test (pk)," + 981 "CONSTRAINT fk4 FOREIGN KEY (v4) REFERENCES test (pk)," + 982 "CONSTRAINT fk5 FOREIGN KEY (v5) REFERENCES test (pk)," + 983 "CONSTRAINT fk6 FOREIGN KEY (v6) REFERENCES test (pk)," + 984 "CONSTRAINT fk7 FOREIGN KEY (v7) REFERENCES test (pk));", 985 }, 986 Assertions: []ScriptTestAssertion{ 987 { 988 Query: `UPDATE test SET v1 = NULL, v2 = NULL WHERE test.pk = 0;`, 989 Expected: []sql.Row{{types.OkResult{ 990 RowsAffected: 0, 991 InsertID: 0, 992 Info: plan.UpdateInfo{ 993 Matched: 0, 994 Updated: 0, 995 Warnings: 0, 996 }, 997 }}}, 998 }, 999 }, 1000 }, 1001 { 1002 Name: "Self-referential delete cascade depth limit", 1003 SetUpScript: []string{ 1004 "CREATE TABLE under_limit(pk BIGINT PRIMARY KEY, v1 BIGINT, INDEX idx_v1(v1));", 1005 "CREATE TABLE over_limit(pk BIGINT PRIMARY KEY, v1 BIGINT, INDEX idx_v1(v1));", 1006 "INSERT INTO under_limit VALUES (1,2),(2,3),(3,4),(4,5),(5,6),(6,7),(7,8),(8,9),(9,10),(10,11),(11,12),(12,13),(13,14),(14,1);", 1007 "INSERT INTO over_limit VALUES (1,2),(2,3),(3,4),(4,5),(5,6),(6,7),(7,8),(8,9),(9,10),(10,11),(11,12),(12,13),(13,14),(14,15),(15,1);", 1008 "ALTER TABLE under_limit ADD CONSTRAINT fk_under FOREIGN KEY (v1) REFERENCES under_limit(pk) ON UPDATE CASCADE ON DELETE CASCADE;", 1009 "ALTER TABLE over_limit ADD CONSTRAINT fk_over FOREIGN KEY (v1) REFERENCES over_limit(pk) ON UPDATE CASCADE ON DELETE CASCADE;", 1010 }, 1011 Assertions: []ScriptTestAssertion{ 1012 { 1013 Query: "DELETE FROM under_limit WHERE pk = 1;", 1014 Expected: []sql.Row{{types.NewOkResult(1)}}, 1015 }, 1016 { 1017 Query: "DELETE FROM over_limit WHERE pk = 1;", 1018 ExpectedErr: sql.ErrForeignKeyDepthLimit, 1019 }, 1020 { 1021 Query: "DELETE FROM over_limit WHERE pk = 0;", 1022 Expected: []sql.Row{{types.NewOkResult(0)}}, 1023 }, 1024 { 1025 Query: "UPDATE over_limit SET pk = 1 WHERE pk = 1;", 1026 Expected: []sql.Row{{types.OkResult{ 1027 RowsAffected: 0, 1028 InsertID: 0, 1029 Info: plan.UpdateInfo{ 1030 Matched: 1, 1031 Updated: 0, 1032 Warnings: 0, 1033 }, 1034 }}}, 1035 }, 1036 { 1037 Query: "UPDATE over_limit SET pk = 2 WHERE pk = 1;", 1038 ExpectedErr: sql.ErrForeignKeyParentViolation, 1039 }, 1040 }, 1041 }, 1042 { 1043 Name: "Cyclic 2-table delete cascade depth limit", 1044 SetUpScript: []string{ 1045 "CREATE TABLE under_cycle1(pk BIGINT PRIMARY KEY, v1 BIGINT UNIQUE);", 1046 "CREATE TABLE under_cycle2(pk BIGINT PRIMARY KEY, v1 BIGINT UNIQUE);", 1047 "INSERT INTO under_cycle1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7);", 1048 "INSERT INTO under_cycle2 VALUES (1,2),(2,3),(3,4),(4,5),(5,6),(6,7),(7,1);", 1049 "ALTER TABLE under_cycle1 ADD CONSTRAINT fk1 FOREIGN KEY (v1) REFERENCES under_cycle2(pk) ON UPDATE CASCADE ON DELETE CASCADE;", 1050 "ALTER TABLE under_cycle2 ADD CONSTRAINT fk2 FOREIGN KEY (v1) REFERENCES under_cycle1(pk) ON UPDATE CASCADE ON DELETE CASCADE;", 1051 "CREATE TABLE over_cycle1(pk BIGINT PRIMARY KEY, v1 BIGINT UNIQUE);", 1052 "CREATE TABLE over_cycle2(pk BIGINT PRIMARY KEY, v1 BIGINT UNIQUE);", 1053 "INSERT INTO over_cycle1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);", 1054 "INSERT INTO over_cycle2 VALUES (1,2),(2,3),(3,4),(4,5),(5,6),(6,7),(7,8),(8,1);", 1055 "ALTER TABLE over_cycle1 ADD CONSTRAINT fk3 FOREIGN KEY (v1) REFERENCES over_cycle2(pk) ON UPDATE CASCADE ON DELETE CASCADE;", 1056 "ALTER TABLE over_cycle2 ADD CONSTRAINT fk4 FOREIGN KEY (v1) REFERENCES over_cycle1(pk) ON UPDATE CASCADE ON DELETE CASCADE;", 1057 }, 1058 Assertions: []ScriptTestAssertion{ 1059 { 1060 Query: "DELETE FROM under_cycle1 WHERE pk = 1;", 1061 Expected: []sql.Row{{types.NewOkResult(1)}}, 1062 }, 1063 { 1064 Query: "DELETE FROM over_cycle1 WHERE pk = 1;", 1065 ExpectedErr: sql.ErrForeignKeyDepthLimit, 1066 }, 1067 }, 1068 }, 1069 { 1070 Name: "Cyclic 3-table delete cascade depth limit", 1071 SetUpScript: []string{ 1072 "CREATE TABLE under_cycle1(pk BIGINT PRIMARY KEY, v1 BIGINT UNIQUE);", 1073 "CREATE TABLE under_cycle2(pk BIGINT PRIMARY KEY, v1 BIGINT UNIQUE);", 1074 "CREATE TABLE under_cycle3(pk BIGINT PRIMARY KEY, v1 BIGINT UNIQUE);", 1075 "INSERT INTO under_cycle1 VALUES (1,1),(2,2),(3,3),(4,4);", 1076 "INSERT INTO under_cycle2 VALUES (1,1),(2,2),(3,3),(4,4);", 1077 "INSERT INTO under_cycle3 VALUES (1,2),(2,3),(3,4),(4,1);", 1078 "ALTER TABLE under_cycle1 ADD CONSTRAINT fk1 FOREIGN KEY (v1) REFERENCES under_cycle2(pk) ON UPDATE CASCADE ON DELETE CASCADE;", 1079 "ALTER TABLE under_cycle2 ADD CONSTRAINT fk2 FOREIGN KEY (v1) REFERENCES under_cycle3(pk) ON UPDATE CASCADE ON DELETE CASCADE;", 1080 "ALTER TABLE under_cycle3 ADD CONSTRAINT fk3 FOREIGN KEY (v1) REFERENCES under_cycle1(pk) ON UPDATE CASCADE ON DELETE CASCADE;", 1081 "CREATE TABLE over_cycle1(pk BIGINT PRIMARY KEY, v1 BIGINT UNIQUE);", 1082 "CREATE TABLE over_cycle2(pk BIGINT PRIMARY KEY, v1 BIGINT UNIQUE);", 1083 "CREATE TABLE over_cycle3(pk BIGINT PRIMARY KEY, v1 BIGINT UNIQUE);", 1084 "INSERT INTO over_cycle1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5);", 1085 "INSERT INTO over_cycle2 VALUES (1,1),(2,2),(3,3),(4,4),(5,5);", 1086 "INSERT INTO over_cycle3 VALUES (1,2),(2,3),(3,4),(4,5),(5,1);", 1087 "ALTER TABLE over_cycle1 ADD CONSTRAINT fk4 FOREIGN KEY (v1) REFERENCES over_cycle2(pk) ON UPDATE CASCADE ON DELETE CASCADE;", 1088 "ALTER TABLE over_cycle2 ADD CONSTRAINT fk5 FOREIGN KEY (v1) REFERENCES over_cycle3(pk) ON UPDATE CASCADE ON DELETE CASCADE;", 1089 "ALTER TABLE over_cycle3 ADD CONSTRAINT fk6 FOREIGN KEY (v1) REFERENCES over_cycle1(pk) ON UPDATE CASCADE ON DELETE CASCADE;", 1090 }, 1091 Assertions: []ScriptTestAssertion{ 1092 { 1093 Query: "DELETE FROM under_cycle1 WHERE pk = 1;", 1094 Expected: []sql.Row{{types.NewOkResult(1)}}, 1095 }, 1096 { 1097 Query: "DELETE FROM over_cycle1 WHERE pk = 1;", 1098 ExpectedErr: sql.ErrForeignKeyDepthLimit, 1099 }, 1100 }, 1101 }, 1102 { 1103 Name: "Acyclic delete cascade depth limit", 1104 SetUpScript: []string{ 1105 "CREATE TABLE t1(pk BIGINT PRIMARY KEY);", 1106 "CREATE TABLE t2(pk BIGINT PRIMARY KEY, CONSTRAINT fk1 FOREIGN KEY (pk) REFERENCES t1(pk) ON UPDATE CASCADE ON DELETE CASCADE);", 1107 "CREATE TABLE t3(pk BIGINT PRIMARY KEY, CONSTRAINT fk2 FOREIGN KEY (pk) REFERENCES t2(pk) ON UPDATE CASCADE ON DELETE CASCADE);", 1108 "CREATE TABLE t4(pk BIGINT PRIMARY KEY, CONSTRAINT fk3 FOREIGN KEY (pk) REFERENCES t3(pk) ON UPDATE CASCADE ON DELETE CASCADE);", 1109 "CREATE TABLE t5(pk BIGINT PRIMARY KEY, CONSTRAINT fk4 FOREIGN KEY (pk) REFERENCES t4(pk) ON UPDATE CASCADE ON DELETE CASCADE);", 1110 "CREATE TABLE t6(pk BIGINT PRIMARY KEY, CONSTRAINT fk5 FOREIGN KEY (pk) REFERENCES t5(pk) ON UPDATE CASCADE ON DELETE CASCADE);", 1111 "CREATE TABLE t7(pk BIGINT PRIMARY KEY, CONSTRAINT fk6 FOREIGN KEY (pk) REFERENCES t6(pk) ON UPDATE CASCADE ON DELETE CASCADE);", 1112 "CREATE TABLE t8(pk BIGINT PRIMARY KEY, CONSTRAINT fk7 FOREIGN KEY (pk) REFERENCES t7(pk) ON UPDATE CASCADE ON DELETE CASCADE);", 1113 "CREATE TABLE t9(pk BIGINT PRIMARY KEY, CONSTRAINT fk8 FOREIGN KEY (pk) REFERENCES t8(pk) ON UPDATE CASCADE ON DELETE CASCADE);", 1114 "CREATE TABLE t10(pk BIGINT PRIMARY KEY, CONSTRAINT fk9 FOREIGN KEY (pk) REFERENCES t9(pk) ON UPDATE CASCADE ON DELETE CASCADE);", 1115 "CREATE TABLE t11(pk BIGINT PRIMARY KEY, CONSTRAINT fk10 FOREIGN KEY (pk) REFERENCES t10(pk) ON UPDATE CASCADE ON DELETE CASCADE);", 1116 "CREATE TABLE t12(pk BIGINT PRIMARY KEY, CONSTRAINT fk11 FOREIGN KEY (pk) REFERENCES t11(pk) ON UPDATE CASCADE ON DELETE CASCADE);", 1117 "CREATE TABLE t13(pk BIGINT PRIMARY KEY, CONSTRAINT fk12 FOREIGN KEY (pk) REFERENCES t12(pk) ON UPDATE CASCADE ON DELETE CASCADE);", 1118 "CREATE TABLE t14(pk BIGINT PRIMARY KEY, CONSTRAINT fk13 FOREIGN KEY (pk) REFERENCES t13(pk) ON UPDATE CASCADE ON DELETE CASCADE);", 1119 "CREATE TABLE t15(pk BIGINT PRIMARY KEY, CONSTRAINT fk14 FOREIGN KEY (pk) REFERENCES t14(pk) ON UPDATE CASCADE ON DELETE CASCADE);", 1120 "CREATE TABLE t16(pk BIGINT PRIMARY KEY, CONSTRAINT fk15 FOREIGN KEY (pk) REFERENCES t15(pk) ON UPDATE CASCADE ON DELETE CASCADE);", 1121 "INSERT INTO t1 VALUES (1);", 1122 "INSERT INTO t2 VALUES (1);", 1123 "INSERT INTO t3 VALUES (1);", 1124 "INSERT INTO t4 VALUES (1);", 1125 "INSERT INTO t5 VALUES (1);", 1126 "INSERT INTO t6 VALUES (1);", 1127 "INSERT INTO t7 VALUES (1);", 1128 "INSERT INTO t8 VALUES (1);", 1129 "INSERT INTO t9 VALUES (1);", 1130 "INSERT INTO t10 VALUES (1);", 1131 "INSERT INTO t11 VALUES (1);", 1132 "INSERT INTO t12 VALUES (1);", 1133 "INSERT INTO t13 VALUES (1);", 1134 "INSERT INTO t14 VALUES (1);", 1135 "INSERT INTO t15 VALUES (1);", 1136 "INSERT INTO t16 VALUES (1);", 1137 }, 1138 Assertions: []ScriptTestAssertion{ 1139 { 1140 Query: "DELETE FROM t1;", 1141 ExpectedErr: sql.ErrForeignKeyDepthLimit, 1142 }, 1143 { 1144 Query: "DELETE FROM t16;", 1145 Expected: []sql.Row{{types.NewOkResult(1)}}, 1146 }, 1147 { 1148 Query: "DELETE FROM t1;", 1149 Expected: []sql.Row{{types.NewOkResult(1)}}, 1150 }, 1151 }, 1152 }, 1153 { 1154 Name: "Acyclic update cascade depth limit", 1155 SetUpScript: []string{ 1156 "CREATE TABLE t1(pk BIGINT PRIMARY KEY);", 1157 "CREATE TABLE t2(pk BIGINT PRIMARY KEY, CONSTRAINT fk1 FOREIGN KEY (pk) REFERENCES t1(pk) ON UPDATE CASCADE ON DELETE CASCADE);", 1158 "CREATE TABLE t3(pk BIGINT PRIMARY KEY, CONSTRAINT fk2 FOREIGN KEY (pk) REFERENCES t2(pk) ON UPDATE CASCADE ON DELETE CASCADE);", 1159 "CREATE TABLE t4(pk BIGINT PRIMARY KEY, CONSTRAINT fk3 FOREIGN KEY (pk) REFERENCES t3(pk) ON UPDATE CASCADE ON DELETE CASCADE);", 1160 "CREATE TABLE t5(pk BIGINT PRIMARY KEY, CONSTRAINT fk4 FOREIGN KEY (pk) REFERENCES t4(pk) ON UPDATE CASCADE ON DELETE CASCADE);", 1161 "CREATE TABLE t6(pk BIGINT PRIMARY KEY, CONSTRAINT fk5 FOREIGN KEY (pk) REFERENCES t5(pk) ON UPDATE CASCADE ON DELETE CASCADE);", 1162 "CREATE TABLE t7(pk BIGINT PRIMARY KEY, CONSTRAINT fk6 FOREIGN KEY (pk) REFERENCES t6(pk) ON UPDATE CASCADE ON DELETE CASCADE);", 1163 "CREATE TABLE t8(pk BIGINT PRIMARY KEY, CONSTRAINT fk7 FOREIGN KEY (pk) REFERENCES t7(pk) ON UPDATE CASCADE ON DELETE CASCADE);", 1164 "CREATE TABLE t9(pk BIGINT PRIMARY KEY, CONSTRAINT fk8 FOREIGN KEY (pk) REFERENCES t8(pk) ON UPDATE CASCADE ON DELETE CASCADE);", 1165 "CREATE TABLE t10(pk BIGINT PRIMARY KEY, CONSTRAINT fk9 FOREIGN KEY (pk) REFERENCES t9(pk) ON UPDATE CASCADE ON DELETE CASCADE);", 1166 "CREATE TABLE t11(pk BIGINT PRIMARY KEY, CONSTRAINT fk10 FOREIGN KEY (pk) REFERENCES t10(pk) ON UPDATE CASCADE ON DELETE CASCADE);", 1167 "CREATE TABLE t12(pk BIGINT PRIMARY KEY, CONSTRAINT fk11 FOREIGN KEY (pk) REFERENCES t11(pk) ON UPDATE CASCADE ON DELETE CASCADE);", 1168 "CREATE TABLE t13(pk BIGINT PRIMARY KEY, CONSTRAINT fk12 FOREIGN KEY (pk) REFERENCES t12(pk) ON UPDATE CASCADE ON DELETE CASCADE);", 1169 "CREATE TABLE t14(pk BIGINT PRIMARY KEY, CONSTRAINT fk13 FOREIGN KEY (pk) REFERENCES t13(pk) ON UPDATE CASCADE ON DELETE CASCADE);", 1170 "CREATE TABLE t15(pk BIGINT PRIMARY KEY, CONSTRAINT fk14 FOREIGN KEY (pk) REFERENCES t14(pk) ON UPDATE CASCADE ON DELETE CASCADE);", 1171 "CREATE TABLE t16(pk BIGINT PRIMARY KEY, CONSTRAINT fk15 FOREIGN KEY (pk) REFERENCES t15(pk) ON UPDATE CASCADE ON DELETE CASCADE);", 1172 "INSERT INTO t1 VALUES (1);", 1173 "INSERT INTO t2 VALUES (1);", 1174 "INSERT INTO t3 VALUES (1);", 1175 "INSERT INTO t4 VALUES (1);", 1176 "INSERT INTO t5 VALUES (1);", 1177 "INSERT INTO t6 VALUES (1);", 1178 "INSERT INTO t7 VALUES (1);", 1179 "INSERT INTO t8 VALUES (1);", 1180 "INSERT INTO t9 VALUES (1);", 1181 "INSERT INTO t10 VALUES (1);", 1182 "INSERT INTO t11 VALUES (1);", 1183 "INSERT INTO t12 VALUES (1);", 1184 "INSERT INTO t13 VALUES (1);", 1185 "INSERT INTO t14 VALUES (1);", 1186 "INSERT INTO t15 VALUES (1);", 1187 "INSERT INTO t16 VALUES (1);", 1188 }, 1189 Assertions: []ScriptTestAssertion{ 1190 { 1191 Query: "UPDATE t1 SET pk = 2;", 1192 ExpectedErr: sql.ErrForeignKeyDepthLimit, 1193 }, 1194 { 1195 Query: "DELETE FROM t16;", 1196 Expected: []sql.Row{{types.NewOkResult(1)}}, 1197 }, 1198 { 1199 Query: "UPDATE t1 SET pk = 2;", 1200 Expected: []sql.Row{{types.OkResult{ 1201 RowsAffected: 1, 1202 InsertID: 0, 1203 Info: plan.UpdateInfo{ 1204 Matched: 1, 1205 Updated: 1, 1206 Warnings: 0, 1207 }, 1208 }}}, 1209 }, 1210 }, 1211 }, 1212 { 1213 Name: "VARCHAR child violation detection", 1214 SetUpScript: []string{ 1215 "CREATE TABLE colors (id INT NOT NULL, color VARCHAR(32) NOT NULL, PRIMARY KEY (id), INDEX color_index(color));", 1216 "CREATE TABLE objects (id INT NOT NULL, name VARCHAR(64) NOT NULL, color VARCHAR(32), PRIMARY KEY(id), CONSTRAINT color_fk FOREIGN KEY (color) REFERENCES colors(color));", 1217 "INSERT INTO colors (id, color) VALUES (1, 'red'), (2, 'green'), (3, 'blue'), (4, 'purple');", 1218 "INSERT INTO objects (id, name, color) VALUES (1, 'truck', 'red'), (2, 'ball', 'green'), (3, 'shoe', 'blue');", 1219 }, 1220 Assertions: []ScriptTestAssertion{ 1221 { 1222 Query: "DELETE FROM colors where color='green';", 1223 ExpectedErr: sql.ErrForeignKeyParentViolation, 1224 }, 1225 { 1226 Query: "SELECT * FROM colors;", 1227 Expected: []sql.Row{{1, "red"}, {2, "green"}, {3, "blue"}, {4, "purple"}}, 1228 }, 1229 }, 1230 }, 1231 { 1232 Name: "INSERT IGNORE INTO works correctly with foreign key violations", 1233 SetUpScript: []string{ 1234 "CREATE TABLE colors (id INT NOT NULL, color VARCHAR(32) NOT NULL, PRIMARY KEY (id), INDEX color_index(color));", 1235 "CREATE TABLE objects (id INT NOT NULL, name VARCHAR(64) NOT NULL, color VARCHAR(32), PRIMARY KEY(id), CONSTRAINT color_fk FOREIGN KEY (color) REFERENCES colors(color));", 1236 "INSERT INTO colors (id, color) VALUES (1, 'red'), (2, 'green'), (3, 'blue'), (4, 'purple');", 1237 "INSERT INTO objects (id, name, color) VALUES (1, 'truck', 'red'), (2, 'ball', 'green'), (3, 'shoe', 'blue');", 1238 }, 1239 Assertions: []ScriptTestAssertion{ 1240 { 1241 Query: "INSERT IGNORE INTO objects (id, name, color) VALUES (5, 'hi', 'yellow');", 1242 Expected: []sql.Row{{types.NewOkResult(0)}}, 1243 }, 1244 { 1245 Query: "SELECT * FROM objects;", 1246 Expected: []sql.Row{{1, "truck", "red"}, {2, "ball", "green"}, {3, "shoe", "blue"}}, 1247 }, 1248 }, 1249 }, 1250 { 1251 Name: "Delayed foreign key resolution: update", 1252 SetUpScript: []string{ 1253 "set foreign_key_checks=0;", 1254 "create table delayed_parent(pk int primary key);", 1255 "create table delayed_child(pk int primary key, foreign key(pk) references delayed_parent(pk));", 1256 "insert into delayed_parent values (10), (20);", 1257 "insert into delayed_child values (1), (20);", 1258 "set foreign_key_checks=1;", 1259 }, 1260 Assertions: []ScriptTestAssertion{ 1261 { 1262 // No-op update bad to bad should not cause constraint violation 1263 Skip: true, 1264 Query: "update delayed_child set pk=1 where pk=1;", 1265 Expected: []sql.Row{ 1266 {types.OkResult{RowsAffected: 0, Info: plan.UpdateInfo{Matched: 1, Updated: 0}}}, 1267 }, 1268 }, 1269 { 1270 // Update on non-existent row should not cause constraint violation 1271 Query: "update delayed_child set pk=3 where pk=3;", 1272 Expected: []sql.Row{ 1273 {types.OkResult{RowsAffected: 0, Info: plan.UpdateInfo{Matched: 0, Updated: 0}}}, 1274 }, 1275 }, 1276 { 1277 // No-op update good to good should not cause constraint violation 1278 Query: "update delayed_child set pk=20 where pk=20;", 1279 Expected: []sql.Row{ 1280 {types.OkResult{RowsAffected: 0, Info: plan.UpdateInfo{Matched: 1, Updated: 0}}}, 1281 }, 1282 }, 1283 { 1284 // Updating bad value to good value still fails 1285 Query: "update delayed_child set pk=10 where pk=1;", 1286 Expected: []sql.Row{ 1287 {types.OkResult{RowsAffected: 1, Info: plan.UpdateInfo{Matched: 1, Updated: 1}}}, 1288 }, 1289 }, 1290 }, 1291 }, 1292 { 1293 Name: "Delayed foreign key resolution: delete", 1294 SetUpScript: []string{ 1295 "set foreign_key_checks=0;", 1296 "create table delayed_parent(pk int primary key);", 1297 "create table delayed_child(pk int primary key, foreign key(pk) references delayed_parent(pk));", 1298 "insert into delayed_parent values (10), (20);", 1299 "insert into delayed_child values (1), (20);", 1300 "set foreign_key_checks=1;", 1301 }, 1302 Assertions: []ScriptTestAssertion{ 1303 { 1304 // No-op update good to good should not cause constraint violation 1305 Query: "delete from delayed_child where false;", 1306 Expected: []sql.Row{ 1307 {types.OkResult{RowsAffected: 0}}, 1308 }, 1309 }, 1310 { 1311 Query: "delete from delayed_child where pk = 20;", 1312 Expected: []sql.Row{ 1313 {types.OkResult{RowsAffected: 1}}, 1314 }, 1315 }, 1316 { 1317 Query: "delete from delayed_child where pk = 1;", 1318 Expected: []sql.Row{ 1319 {types.OkResult{RowsAffected: 1}}, 1320 }, 1321 }, 1322 }, 1323 }, 1324 { 1325 Name: "Delayed foreign key resolution insert", 1326 SetUpScript: []string{ 1327 "SET FOREIGN_KEY_CHECKS=0;", 1328 "CREATE TABLE delayed_child (pk INT PRIMARY KEY, v1 INT, CONSTRAINT fk_delayed FOREIGN KEY (v1) REFERENCES delayed_parent(v1));", 1329 "CREATE TABLE delayed_parent (pk INT PRIMARY KEY, v1 INT, INDEX (v1));", 1330 "INSERT INTO delayed_child VALUES (1, 2);", 1331 "SET FOREIGN_KEY_CHECKS=1;", 1332 }, 1333 Assertions: []ScriptTestAssertion{ 1334 { 1335 Query: "SHOW CREATE TABLE delayed_child;", 1336 Expected: []sql.Row{{"delayed_child", "CREATE TABLE `delayed_child` (\n `pk` int NOT NULL,\n `v1` int,\n PRIMARY KEY (`pk`),\n KEY `v1` (`v1`),\n CONSTRAINT `fk_delayed` FOREIGN KEY (`v1`) REFERENCES `delayed_parent` (`v1`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 1337 }, 1338 { 1339 Query: "SELECT * FROM delayed_parent;", 1340 Expected: []sql.Row{}, 1341 }, 1342 { 1343 Query: "SELECT * FROM delayed_child;", 1344 Expected: []sql.Row{{1, 2}}, 1345 }, 1346 { 1347 Query: "INSERT INTO delayed_child VALUES (2, 3);", 1348 ExpectedErr: sql.ErrForeignKeyNotResolved, 1349 }, 1350 { 1351 Query: "INSERT INTO delayed_parent VALUES (1, 2), (2, 3);", 1352 Expected: []sql.Row{{types.NewOkResult(2)}}, 1353 }, 1354 { 1355 Query: "INSERT INTO delayed_child VALUES (2, 3);", 1356 Expected: []sql.Row{{types.NewOkResult(1)}}, 1357 }, 1358 { 1359 Query: "SELECT * FROM delayed_child;", 1360 Expected: []sql.Row{{1, 2}, {2, 3}}, 1361 }, 1362 }, 1363 }, 1364 { 1365 Name: "Delayed foreign key still does some validation", 1366 SetUpScript: []string{ 1367 "SET FOREIGN_KEY_CHECKS=0;", 1368 "CREATE TABLE valid_delayed_child (i INT, CONSTRAINT valid_fk FOREIGN KEY (i) REFERENCES delayed_parent(i))", 1369 }, 1370 Assertions: []ScriptTestAssertion{ 1371 { 1372 Query: "CREATE TABLE delayed_child1(i int, CONSTRAINT fk_delayed1 FOREIGN KEY (badcolumn) REFERENCES delayed_parent(i));", 1373 ExpectedErr: sql.ErrTableColumnNotFound, 1374 }, 1375 { 1376 Query: "CREATE TABLE delayed_child2(i int, CONSTRAINT fk_delayed2 FOREIGN KEY (i) REFERENCES delayed_parent(c1, c2, c3));", 1377 ExpectedErr: sql.ErrForeignKeyColumnCountMismatch, 1378 }, 1379 { 1380 Query: "CREATE TABLE delayed_child3(i int, j int, CONSTRAINT fk_i FOREIGN KEY (i) REFERENCES delayed_parent(i), CONSTRAINT fk_i FOREIGN KEY (j) REFERENCES delayed_parent(j));", 1381 ExpectedErr: sql.ErrForeignKeyDuplicateName, 1382 }, 1383 { 1384 Query: "CREATE TABLE delayed_child4(i int, CONSTRAINT fk_delayed4 FOREIGN KEY (i,i,i) REFERENCES delayed_parent(c1, c2, c3));", 1385 ExpectedErr: sql.ErrAddForeignKeyDuplicateColumn, 1386 }, 1387 { 1388 Query: "ALTER TABLE valid_delayed_child drop index i", 1389 ExpectedErr: sql.ErrForeignKeyDropIndex, 1390 }, 1391 }, 1392 }, 1393 { 1394 Name: "Delayed foreign key resolution resetting FOREIGN_KEY_CHECKS", 1395 SetUpScript: []string{ 1396 "SET FOREIGN_KEY_CHECKS=0;", 1397 "CREATE TABLE delayed_child (pk INT PRIMARY KEY, v1 INT, CONSTRAINT fk_delayed FOREIGN KEY (v1) REFERENCES delayed_parent(v1));", 1398 "INSERT INTO delayed_child VALUES (1, 2);", 1399 "SET FOREIGN_KEY_CHECKS=1;", 1400 }, 1401 Assertions: []ScriptTestAssertion{ 1402 { 1403 Query: "SHOW CREATE TABLE delayed_child;", 1404 Expected: []sql.Row{{"delayed_child", "CREATE TABLE `delayed_child` (\n `pk` int NOT NULL,\n `v1` int,\n PRIMARY KEY (`pk`),\n KEY `v1` (`v1`),\n CONSTRAINT `fk_delayed` FOREIGN KEY (`v1`) REFERENCES `delayed_parent` (`v1`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 1405 }, 1406 { 1407 Query: "SELECT * FROM delayed_child;", 1408 Expected: []sql.Row{{1, 2}}, 1409 }, 1410 { 1411 Query: "INSERT INTO delayed_child VALUES (2, 3);", 1412 ExpectedErr: sql.ErrForeignKeyNotResolved, 1413 }, 1414 { 1415 Query: "CREATE TABLE delayed_parent (pk INT PRIMARY KEY, v1 INT, INDEX (v1));", 1416 Expected: []sql.Row{{types.NewOkResult(0)}}, 1417 }, 1418 { 1419 Query: "INSERT INTO delayed_parent VALUES (1, 2), (2, 3);", 1420 Expected: []sql.Row{{types.NewOkResult(2)}}, 1421 }, 1422 { 1423 Query: "INSERT INTO delayed_child VALUES (2, 3);", 1424 Expected: []sql.Row{{types.NewOkResult(1)}}, 1425 }, 1426 { 1427 Query: "SELECT * FROM delayed_child;", 1428 Expected: []sql.Row{{1, 2}, {2, 3}}, 1429 }, 1430 }, 1431 }, 1432 { 1433 Name: "DROP TABLE with FOREIGN_KEY_CHECKS=0", 1434 SetUpScript: []string{ 1435 "ALTER TABLE child ADD CONSTRAINT fk_dropped FOREIGN KEY (v1) REFERENCES parent(v1);", 1436 }, 1437 Assertions: []ScriptTestAssertion{ 1438 { 1439 Query: "TRUNCATE parent;", 1440 ExpectedErr: sql.ErrTruncateReferencedFromForeignKey, 1441 }, 1442 { 1443 Query: "DROP TABLE parent;", 1444 ExpectedErr: sql.ErrForeignKeyDropTable, 1445 }, 1446 { 1447 Query: "SET FOREIGN_KEY_CHECKS=0;", 1448 Expected: []sql.Row{{}}, 1449 }, 1450 { 1451 Query: "TRUNCATE parent;", 1452 Expected: []sql.Row{{types.NewOkResult(0)}}, 1453 }, 1454 { 1455 Query: "DROP TABLE parent;", 1456 Expected: []sql.Row{{types.NewOkResult(0)}}, 1457 }, 1458 { 1459 Query: "SET FOREIGN_KEY_CHECKS=1;", 1460 Expected: []sql.Row{{}}, 1461 }, 1462 { 1463 Query: "INSERT INTO child VALUES (4, 5, 6);", 1464 ExpectedErr: sql.ErrForeignKeyNotResolved, 1465 }, 1466 { 1467 Query: "CREATE TABLE parent (pk INT PRIMARY KEY, v1 INT, INDEX (v1));", 1468 Expected: []sql.Row{{types.NewOkResult(0)}}, 1469 }, 1470 { 1471 Query: "INSERT INTO parent VALUES (1, 5);", 1472 Expected: []sql.Row{{types.NewOkResult(1)}}, 1473 }, 1474 { 1475 Query: "INSERT INTO child VALUES (4, 5, 6);", 1476 Expected: []sql.Row{{types.NewOkResult(1)}}, 1477 }, 1478 { 1479 Query: "SELECT * FROM parent;", 1480 Expected: []sql.Row{{1, 5}}, 1481 }, 1482 { 1483 Query: "SELECT * FROM child;", 1484 Expected: []sql.Row{{4, 5, 6}}, 1485 }, 1486 }, 1487 }, 1488 { 1489 Name: "ALTER TABLE ADD CONSTRAINT for different database", 1490 SetUpScript: []string{ 1491 "CREATE DATABASE public;", 1492 "CREATE TABLE public.cities (pk INT PRIMARY KEY, city VARCHAR(255), state VARCHAR(2));", 1493 "CREATE TABLE public.states (state_id INT PRIMARY KEY, state VARCHAR(2));", 1494 }, 1495 Assertions: []ScriptTestAssertion{ 1496 { 1497 Query: "ALTER TABLE public.cities ADD CONSTRAINT foreign_key1 FOREIGN KEY (state) REFERENCES public.states(state);", 1498 ExpectedErr: sql.ErrForeignKeyMissingReferenceIndex, 1499 }, 1500 { 1501 Query: "CREATE INDEX foreign_key1 ON public.states(state);", 1502 Expected: []sql.Row{{types.NewOkResult(0)}}, 1503 }, 1504 { 1505 Query: "ALTER TABLE public.cities ADD CONSTRAINT foreign_key1 FOREIGN KEY (state) REFERENCES public.states(state);", 1506 Expected: []sql.Row{{types.NewOkResult(0)}}, 1507 }, 1508 }, 1509 }, 1510 { 1511 Name: "Creating a foreign key on a table with an unsupported type works", 1512 SetUpScript: []string{ 1513 "CREATE TABLE IF NOT EXISTS restaurants (id INT PRIMARY KEY, coordinate POINT);", 1514 "CREATE TABLE IF NOT EXISTS hours (restaurant_id INT PRIMARY KEY AUTO_INCREMENT, CONSTRAINT fk_name FOREIGN KEY (restaurant_id) REFERENCES restaurants(id));", 1515 }, 1516 Assertions: []ScriptTestAssertion{ 1517 { 1518 Query: "SHOW CREATE TABLE hours;", 1519 Expected: []sql.Row{{"hours", "CREATE TABLE `hours` (\n `restaurant_id` int NOT NULL AUTO_INCREMENT,\n PRIMARY KEY (`restaurant_id`),\n CONSTRAINT `fk_name` FOREIGN KEY (`restaurant_id`) REFERENCES `restaurants` (`id`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}}, 1520 }, 1521 }, 1522 }, 1523 { 1524 Name: "Create foreign key onto primary key", 1525 SetUpScript: []string{ 1526 "DROP TABLE child;", 1527 "DROP TABLE parent;", 1528 "CREATE TABLE parent (a INT, b INT, c INT, PRIMARY KEY (b, a));", 1529 "CREATE TABLE child (a INT PRIMARY KEY, b INT);", 1530 }, 1531 Assertions: []ScriptTestAssertion{ 1532 { 1533 Query: "ALTER TABLE child ADD CONSTRAINT fk1 FOREIGN KEY (b) REFERENCES parent (b);", 1534 Expected: []sql.Row{{types.NewOkResult(0)}}, 1535 }, 1536 { 1537 Query: "ALTER TABLE child ADD CONSTRAINT fk2 FOREIGN KEY (a) REFERENCES parent (b);", 1538 Expected: []sql.Row{{types.NewOkResult(0)}}, 1539 }, 1540 { 1541 Query: "ALTER TABLE child ADD CONSTRAINT fk3 FOREIGN KEY (a, b) REFERENCES parent (a, b);", 1542 ExpectedErr: sql.ErrForeignKeyMissingReferenceIndex, 1543 }, 1544 { 1545 Query: "ALTER TABLE child ADD CONSTRAINT fk4 FOREIGN KEY (b, a) REFERENCES parent (b, a);", 1546 Expected: []sql.Row{{types.NewOkResult(0)}}, 1547 }, 1548 }, 1549 }, 1550 { 1551 Name: "Reordered foreign key columns do match", 1552 SetUpScript: []string{ 1553 "DROP TABLE child;", 1554 "DROP TABLE parent;", 1555 "CREATE TABLE parent(fk1 int, fk2 int, primary key(fk1, fk2));", 1556 }, 1557 Assertions: []ScriptTestAssertion{ 1558 { 1559 Query: "CREATE TABLE child(id int unique, fk1 int, fk2 int, primary key(fk2, fk1, id), constraint `fk` foreign key(fk1, fk2) references parent (fk1, fk2));", 1560 Expected: []sql.Row{{types.NewOkResult(0)}}, 1561 }, 1562 { 1563 Query: "Show create table child;", 1564 Expected: []sql.Row{ 1565 {"child", "CREATE TABLE `child` (\n" + 1566 " `id` int NOT NULL,\n" + 1567 " `fk1` int NOT NULL,\n" + 1568 " `fk2` int NOT NULL,\n" + 1569 " PRIMARY KEY (`fk2`,`fk1`,`id`),\n" + 1570 " KEY `fk1fk2` (`fk1`,`fk2`),\n" + 1571 " UNIQUE KEY `id` (`id`),\n" + 1572 " CONSTRAINT `fk` FOREIGN KEY (`fk1`,`fk2`) REFERENCES `parent` (`fk1`,`fk2`)\n" + 1573 ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}, 1574 }, 1575 }, 1576 }, 1577 }, 1578 { 1579 Name: "Reordered foreign key columns do not match", 1580 SetUpScript: []string{ 1581 "DROP TABLE child;", 1582 "DROP TABLE parent;", 1583 "CREATE TABLE parent(pk DOUBLE PRIMARY KEY, v1 BIGINT, v2 BIGINT, INDEX(v1, v2, pk));", 1584 }, 1585 Assertions: []ScriptTestAssertion{ 1586 { 1587 Query: "CREATE TABLE child(pk BIGINT PRIMARY KEY, v1 BIGINT, v2 BIGINT, CONSTRAINT fk_child FOREIGN KEY (v2, v1) REFERENCES parent(v2, v1));", 1588 ExpectedErr: sql.ErrForeignKeyMissingReferenceIndex, 1589 }, 1590 }, 1591 }, 1592 { 1593 Name: "Reordered foreign key columns match an index's prefix, ALTER TABLE ADD FOREIGN KEY fails check", 1594 SetUpScript: []string{ 1595 "DROP TABLE child;", 1596 "DROP TABLE parent;", 1597 "CREATE TABLE parent(pk DOUBLE PRIMARY KEY, v1 BIGINT, v2 BIGINT, INDEX(v1, v2, pk));", 1598 "INSERT INTO parent VALUES (1, 1, 1), (2, 1, 2);", 1599 "CREATE TABLE child(pk BIGINT PRIMARY KEY, v1 BIGINT, v2 BIGINT);", 1600 "INSERT INTO child VALUES (1, 2, 1);", 1601 }, 1602 Assertions: []ScriptTestAssertion{ 1603 { 1604 Query: "ALTER TABLE child ADD CONSTRAINT fk_child FOREIGN KEY (v2, v1) REFERENCES parent(v2, v1);", 1605 ExpectedErr: sql.ErrForeignKeyMissingReferenceIndex, 1606 }, 1607 }, 1608 }, 1609 { 1610 Name: "Self-referential deletion with ON UPDATE CASCADE", 1611 SetUpScript: []string{ 1612 "CREATE TABLE self(pk BIGINT PRIMARY KEY, v1 BIGINT, v2 BIGINT, INDEX(v1), CONSTRAINT fk_self FOREIGN KEY(v2) REFERENCES self(v1) ON UPDATE CASCADE);", 1613 "INSERT INTO self VALUES (0, 1, 1), (1, 2, 1);", 1614 }, 1615 Assertions: []ScriptTestAssertion{ 1616 { 1617 Query: "DELETE FROM self WHERE v1 = 1;", 1618 ExpectedErr: sql.ErrForeignKeyParentViolation, 1619 }, 1620 { 1621 Query: "DELETE FROM self WHERE v1 = 2;", 1622 Expected: []sql.Row{{types.NewOkResult(1)}}, 1623 }, 1624 }, 1625 }, 1626 { 1627 Name: "Self-referential deletion with ON DELETE CASCADE", 1628 SetUpScript: []string{ 1629 "CREATE TABLE self(pk BIGINT PRIMARY KEY, v1 BIGINT, v2 BIGINT, INDEX(v1), CONSTRAINT fk_self FOREIGN KEY(v2) REFERENCES self(v1) ON DELETE CASCADE);", 1630 "INSERT INTO self VALUES (0, 1, 1), (1, 2, 1);", 1631 }, 1632 Assertions: []ScriptTestAssertion{ 1633 { 1634 Query: "DELETE FROM self WHERE v1 = 1;", 1635 Expected: []sql.Row{{types.NewOkResult(1)}}, // Cascading deletions do not count 1636 }, 1637 { 1638 Query: "SELECT * FROM self;", 1639 Expected: []sql.Row{}, 1640 }, 1641 }, 1642 }, 1643 { 1644 Name: "Cascaded DELETE becomes cascading UPDATE after first child, using ON DELETE for second child", 1645 SetUpScript: []string{ 1646 "DROP TABLE child;", 1647 "DROP TABLE parent;", 1648 "CREATE TABLE parent (pk BIGINT PRIMARY KEY, v1 BIGINT, v2 BIGINT, INDEX (v1), INDEX (v2), INDEX (v1, v2));", 1649 "CREATE TABLE child (pk BIGINT PRIMARY KEY, v1 BIGINT, v2 BIGINT, CONSTRAINT fk_child FOREIGN KEY (v1, v2) REFERENCES parent (v1, v2) ON DELETE SET NULL);", 1650 "CREATE TABLE child2 (pk BIGINT PRIMARY KEY, v1 BIGINT, v2 BIGINT, CONSTRAINT fk_child2 FOREIGN KEY (v1, v2) REFERENCES child (v1, v2) ON DELETE SET NULL);", 1651 "INSERT INTO parent VALUES (1,1,1), (2,2,2), (3,3,3);", 1652 "INSERT INTO child VALUES (1,1,1), (2,2,2), (3,3,3);", 1653 "INSERT INTO child2 VALUES (1,1,1), (2,2,2), (3,3,3);", 1654 }, 1655 Assertions: []ScriptTestAssertion{ 1656 { 1657 Query: "DELETE FROM parent WHERE pk = 1;", 1658 ExpectedErr: sql.ErrForeignKeyParentViolation, 1659 }, 1660 }, 1661 }, 1662 { 1663 Name: "Cascaded DELETE becomes cascading UPDATE after first child, using ON UPDATE for second child", 1664 SetUpScript: []string{ 1665 "DROP TABLE child;", 1666 "DROP TABLE parent;", 1667 "CREATE TABLE parent (pk BIGINT PRIMARY KEY, v1 BIGINT, v2 BIGINT, INDEX (v1), INDEX (v2), INDEX (v1, v2));", 1668 "CREATE TABLE child (pk BIGINT PRIMARY KEY, v1 BIGINT, v2 BIGINT, CONSTRAINT fk_child FOREIGN KEY (v1, v2) REFERENCES parent (v1, v2) ON DELETE SET NULL);", 1669 "CREATE TABLE child2 (pk BIGINT PRIMARY KEY, v1 BIGINT, v2 BIGINT, CONSTRAINT fk_child2 FOREIGN KEY (v1, v2) REFERENCES child (v1, v2) ON UPDATE CASCADE);", 1670 "INSERT INTO parent VALUES (1,1,1), (2,2,2), (3,3,3);", 1671 "INSERT INTO child VALUES (1,1,1), (2,2,2), (3,3,3);", 1672 "INSERT INTO child2 VALUES (1,1,1), (2,2,2), (3,3,3);", 1673 }, 1674 Assertions: []ScriptTestAssertion{ 1675 { 1676 Query: "DELETE FROM parent WHERE pk = 1;", 1677 Expected: []sql.Row{{types.NewOkResult(1)}}, 1678 }, 1679 { 1680 Query: "SELECT * FROM parent;", 1681 Expected: []sql.Row{{2, 2, 2}, {3, 3, 3}}, 1682 }, 1683 { 1684 Query: "SELECT * FROM child;", 1685 Expected: []sql.Row{{1, nil, nil}, {2, 2, 2}, {3, 3, 3}}, 1686 }, 1687 { 1688 Query: "SELECT * FROM child2;", 1689 Expected: []sql.Row{{1, nil, nil}, {2, 2, 2}, {3, 3, 3}}, 1690 }, 1691 }, 1692 }, 1693 { 1694 Name: "INSERT on DUPLICATE correctly works with FKs", 1695 SetUpScript: []string{ 1696 "INSERT INTO parent values (1,1,1),(2,2,2),(3,3,3)", 1697 "ALTER TABLE child ADD CONSTRAINT fk_named FOREIGN KEY (v1) REFERENCES parent(v1);", 1698 "INSERT into child values (1, 1, 1)", 1699 "CREATE TABLE one (pk BIGINT PRIMARY KEY, v1 BIGINT, v2 BIGINT, INDEX v1 (v1));", 1700 "CREATE TABLE two (pk BIGINT PRIMARY KEY, v1 BIGINT, v2 BIGINT, INDEX v1v2 (v1, v2), CONSTRAINT fk_name_1 FOREIGN KEY (v1) REFERENCES one(v1) ON DELETE CASCADE ON UPDATE CASCADE);", 1701 "INSERT INTO one VALUES (1, 1, 4), (2, 2, 5), (3, 3, 6), (4, 4, 5);", 1702 "INSERT INTO two VALUES (2, 1, 1), (3, 2, 2), (4, 3, 3), (5, 4, 4);", 1703 }, 1704 Assertions: []ScriptTestAssertion{ 1705 { 1706 Query: "INSERT INTO parent VALUES (1,200,1) ON DUPLICATE KEY UPDATE v1 = values(v1)", 1707 ExpectedErr: sql.ErrForeignKeyParentViolation, 1708 }, 1709 { 1710 Query: "INSERT INTO one VALUES (1, 2, 4) on duplicate key update v1 = VALUES(v1)", 1711 Expected: []sql.Row{{types.NewOkResult(2)}}, 1712 }, 1713 { 1714 Query: "SELECT * FROM two where pk = 2", 1715 Expected: []sql.Row{{2, 2, 1}}, 1716 }, 1717 }, 1718 }, 1719 { 1720 Name: "Referencing Primary Key", 1721 SetUpScript: []string{ 1722 "CREATE table parent1 (pk BIGINT PRIMARY KEY, v1 BIGINT);", 1723 "CREATE table child1 (pk BIGINT PRIMARY KEY, v1 BIGINT, FOREIGN KEY (v1) REFERENCES parent1(pk) ON UPDATE CASCADE ON DELETE CASCADE);", 1724 "INSERT INTO parent1 VALUES (1, 1);", 1725 }, 1726 Assertions: []ScriptTestAssertion{ 1727 { 1728 Query: "INSERT INTO child1 VALUES (1, 1);", 1729 Expected: []sql.Row{{types.NewOkResult(1)}}, 1730 }, 1731 { 1732 Query: "SELECT * FROM child1;", 1733 Expected: []sql.Row{{1, 1}}, 1734 }, 1735 { 1736 Query: "UPDATE parent1 SET pk = 2;", 1737 Expected: []sql.Row{{types.OkResult{RowsAffected: 1, Info: plan.UpdateInfo{Matched: 1, Updated: 1}}}}, 1738 }, 1739 { 1740 Query: "SELECT * FROM child1;", 1741 Expected: []sql.Row{{1, 2}}, 1742 }, 1743 }, 1744 }, 1745 { 1746 Name: "Referencing Composite Primary Key", 1747 SetUpScript: []string{ 1748 "CREATE table parent1 (pk1 BIGINT, pk2 BIGINT, v1 BIGINT, PRIMARY KEY(pk1, pk2));", 1749 "CREATE table child1 (pk BIGINT PRIMARY KEY, v1 BIGINT, v2 BIGINT, FOREIGN KEY (v1, v2) REFERENCES parent1(pk1, pk2) ON UPDATE CASCADE ON DELETE CASCADE);", 1750 "INSERT INTO parent1 VALUES (1, 2, 3), (4, 5, 6);", 1751 }, 1752 Assertions: []ScriptTestAssertion{ 1753 { 1754 Query: "INSERT INTO child1 VALUES (1, 1, 2), (2, 4, 5);", 1755 Expected: []sql.Row{{types.NewOkResult(2)}}, 1756 }, 1757 { 1758 Query: "SELECT * FROM child1;", 1759 Expected: []sql.Row{{1, 1, 2}, {2, 4, 5}}, 1760 }, 1761 { 1762 Query: "UPDATE parent1 SET pk2 = pk1 + pk2;", 1763 Expected: []sql.Row{{types.OkResult{RowsAffected: 2, Info: plan.UpdateInfo{Matched: 2, Updated: 2}}}}, 1764 }, 1765 { 1766 Query: "SELECT * FROM child1;", 1767 Expected: []sql.Row{{1, 1, 3}, {2, 4, 9}}, 1768 }, 1769 }, 1770 }, 1771 { 1772 Name: "Keyless CASCADE deleting all rows", 1773 SetUpScript: []string{ 1774 "CREATE TABLE one (v0 BIGINT, v1 BIGINT, INDEX one_v0 (v0), INDEX one_v1 (v1));", 1775 "CREATE TABLE two (v1 BIGINT, CONSTRAINT fk_name_1 FOREIGN KEY (v1) REFERENCES one(v1) ON DELETE CASCADE ON UPDATE CASCADE);", 1776 "INSERT INTO one VALUES (1, 2);", 1777 "INSERT INTO two VALUES (2);", 1778 "UPDATE one SET v1 = v0 + v1;", 1779 "DELETE FROM one WHERE v0 = 1;", 1780 }, 1781 Assertions: []ScriptTestAssertion{ 1782 { 1783 Query: "SELECT * FROM one;", 1784 Expected: []sql.Row{}, 1785 }, 1786 { 1787 Query: "SELECT * FROM two;", 1788 Expected: []sql.Row{}, 1789 }, 1790 }, 1791 }, 1792 { 1793 Name: "Keyless CASCADE over three tables", 1794 SetUpScript: []string{ 1795 "CREATE TABLE one (v0 BIGINT, v1 BIGINT, v2 BIGINT, INDEX idx (v0));", 1796 "ALTER TABLE one ADD INDEX v1 (v1);", 1797 "CREATE TABLE two (v0 BIGINT, v1 BIGINT, v2 BIGINT, INDEX idx (v0), CONSTRAINT fk_name_1 FOREIGN KEY (v1) REFERENCES one(v1) ON DELETE CASCADE ON UPDATE CASCADE);", 1798 "ALTER TABLE two ADD INDEX v1v2 (v1, v2);", 1799 "CREATE TABLE three (v0 BIGINT, v1 BIGINT, v2 BIGINT, INDEX idx (v0), CONSTRAINT fk_name_2 FOREIGN KEY (v1, v2) REFERENCES two(v1, v2) ON DELETE CASCADE ON UPDATE CASCADE);", 1800 "INSERT INTO one VALUES (1, 1, 4), (2, 2, 5), (3, 3, 6), (4, 4, 5);", 1801 "INSERT INTO two VALUES (2, 1, 1), (3, 2, 2), (4, 3, 3), (5, 4, 4);", 1802 "INSERT INTO three VALUES (3, 1, 1), (4, 2, 2), (5, 3, 3), (6, 4, 4);", 1803 "UPDATE one SET v1 = v1 + v2;", 1804 "DELETE FROM one WHERE v0 = 3;", 1805 "UPDATE two SET v2 = v1 - 2;", 1806 }, 1807 Assertions: []ScriptTestAssertion{ 1808 { 1809 Query: "SELECT * FROM one;", 1810 Expected: []sql.Row{{1, 5, 4}, {2, 7, 5}, {4, 9, 5}}, 1811 }, 1812 { 1813 Query: "SELECT * FROM two;", 1814 Expected: []sql.Row{{2, 5, 3}, {3, 7, 5}}, 1815 }, 1816 { 1817 Query: "SELECT * FROM three;", 1818 Expected: []sql.Row{{3, 5, 3}, {4, 7, 5}}, 1819 }, 1820 }, 1821 }, 1822 { 1823 Name: "Table with inverted primary key referencing another table can insert rows", 1824 SetUpScript: []string{ 1825 "create table a (x int, y int, primary key (x,y), INDEX `a_y_idx` (y));", 1826 "create table b (x int, y int, primary key (y,x), foreign key (y) references a(y) on update cascade on delete cascade);", 1827 }, 1828 Assertions: []ScriptTestAssertion{ 1829 { 1830 Query: "INSERT into a (x, y) VALUES (1, 3);", 1831 Expected: []sql.Row{{types.NewOkResult(1)}}, 1832 }, 1833 { 1834 Query: "INSERT into b (x, y) VALUES (2, 3);", 1835 Expected: []sql.Row{{types.NewOkResult(1)}}, 1836 }, 1837 { 1838 Query: "SELECT x, y from a;", 1839 Expected: []sql.Row{{1, 3}}, 1840 }, 1841 { 1842 Query: "SELECT x, y from b;", 1843 Expected: []sql.Row{{2, 3}}, 1844 }, 1845 { 1846 Query: "INSERT into b (x, y) VALUES (3, 5);", 1847 ExpectedErr: sql.ErrForeignKeyChildViolation, 1848 }, 1849 }, 1850 }, 1851 { 1852 Name: "Table with inverted primary key referencing another table with inverted primary keys can be inserted", 1853 SetUpScript: []string{ 1854 "create table a (x int, y int, primary key (y,x));", 1855 "create table b (x int, y int, primary key (y,x), foreign key (y) references a(y) on update cascade on delete cascade);", 1856 }, 1857 Assertions: []ScriptTestAssertion{ 1858 { 1859 Query: "INSERT into a (x, y) VALUES (1, 3);", 1860 Expected: []sql.Row{{types.NewOkResult(1)}}, 1861 }, 1862 { 1863 Query: "INSERT into b (x, y) VALUES (2, 3);", 1864 Expected: []sql.Row{{types.NewOkResult(1)}}, 1865 }, 1866 { 1867 Query: "SELECT x, y from a;", 1868 Expected: []sql.Row{{1, 3}}, 1869 }, 1870 { 1871 Query: "SELECT x, y from b;", 1872 Expected: []sql.Row{{2, 3}}, 1873 }, 1874 { 1875 Query: "INSERT into b (x, y) VALUES (3, 5);", 1876 ExpectedErr: sql.ErrForeignKeyChildViolation, 1877 }, 1878 }, 1879 }, 1880 { 1881 Name: "Table with inverted primary key referencing another table can be updated", 1882 SetUpScript: []string{ 1883 "create table a (x int, y int, primary key (x,y), INDEX `a_y_idx` (y));", 1884 "create table b (x int, y int, primary key (y,x), foreign key (y) references a(y) on update cascade on delete cascade);", 1885 "INSERT into a VALUES (1, 3);", 1886 "INSERT into b VALUES (2, 3);", 1887 }, 1888 Assertions: []ScriptTestAssertion{ 1889 { 1890 Query: "UPDATE a SET y = 4 where y = 3;", 1891 Expected: []sql.Row{{types.OkResult{RowsAffected: 1, Info: plan.UpdateInfo{Matched: 1, Updated: 1}}}}, 1892 }, 1893 { 1894 Query: "SELECT x, y from a;", 1895 Expected: []sql.Row{{1, 4}}, 1896 }, 1897 { 1898 Query: "SELECT x, y from b;", 1899 Expected: []sql.Row{{2, 4}}, 1900 }, 1901 }, 1902 }, 1903 { 1904 Name: "Table with inverted primary key referencing another table with inverted primary keys can be updated", 1905 SetUpScript: []string{ 1906 "create table a (x int, y int, primary key (y,x));", 1907 "create table b (x int, y int, primary key (y,x), foreign key (y) references a(y) on update cascade on delete cascade);", 1908 "INSERT into a VALUES (1, 3)", 1909 "INSERT into b VALUES (2, 3)", 1910 }, 1911 Assertions: []ScriptTestAssertion{ 1912 { 1913 Query: "UPDATE a SET y = 4 where y = 3;", 1914 Expected: []sql.Row{{types.OkResult{RowsAffected: 1, Info: plan.UpdateInfo{Matched: 1, Updated: 1}}}}, 1915 }, 1916 { 1917 Query: "SELECT x, y from a;", 1918 Expected: []sql.Row{{1, 4}}, 1919 }, 1920 { 1921 Query: "SELECT x, y from b;", 1922 Expected: []sql.Row{{2, 4}}, 1923 }, 1924 }, 1925 }, 1926 { 1927 Name: "Table with inverted primary key referencing another table can be deleted", 1928 SetUpScript: []string{ 1929 "create table a (x int, y int, primary key (x,y), INDEX `a_y_idx` (y));", 1930 "create table b (x int, y int, primary key (y,x), foreign key (y) references a(y) on update cascade on delete cascade);", 1931 "INSERT into a VALUES (1, 3);", 1932 "INSERT into b VALUES (2, 3);", 1933 }, 1934 Assertions: []ScriptTestAssertion{ 1935 { 1936 Query: "DELETE from a where x = 1 AND y = 3;", 1937 Expected: []sql.Row{{types.NewOkResult(1)}}, 1938 }, 1939 { 1940 Query: "SELECT * from a;", 1941 Expected: []sql.Row{}, 1942 }, 1943 { 1944 Query: "SELECT * from b;", 1945 Expected: []sql.Row{}, 1946 }, 1947 }, 1948 }, 1949 { 1950 Name: "Table with inverted primary key referencing another table with inverted primary keys can be deleted", 1951 SetUpScript: []string{ 1952 "create table a (x int, y int, primary key (y,x));", 1953 "create table b (x int, y int, primary key (y,x), foreign key (y) references a(y) on update cascade on delete cascade);", 1954 "INSERT into a VALUES (1, 3)", 1955 "INSERT into b VALUES (2, 3)", 1956 }, 1957 Assertions: []ScriptTestAssertion{ 1958 { 1959 Query: "DELETE from a where x = 1 AND y = 3;", 1960 Expected: []sql.Row{{types.NewOkResult(1)}}, 1961 }, 1962 { 1963 Query: "SELECT * from a;", 1964 Expected: []sql.Row{}, 1965 }, 1966 { 1967 Query: "SELECT * from b;", 1968 Expected: []sql.Row{}, 1969 }, 1970 }, 1971 }, 1972 { 1973 Name: "May use different collations as long as the character sets are equivalent", 1974 SetUpScript: []string{ 1975 "CREATE TABLE t1 (pk char(32) COLLATE utf8mb4_0900_ai_ci PRIMARY KEY);", 1976 }, 1977 Assertions: []ScriptTestAssertion{ 1978 { 1979 Query: "CREATE TABLE t2 (pk char(32) COLLATE utf8mb4_0900_bin PRIMARY KEY, CONSTRAINT fk_1 FOREIGN KEY (pk) REFERENCES t1 (pk));", 1980 Expected: []sql.Row{{types.NewOkResult(0)}}, 1981 }, 1982 }, 1983 }, 1984 { 1985 Name: "Referenced index includes implicit primary key columns", 1986 SetUpScript: []string{ 1987 "create table parent1 (fk1 int, pk1 int, pk2 int, pk3 int, primary key(pk1, pk2, pk3), index (fk1, pk2));", 1988 "insert into parent1 values (0, 1, 2, 3);", 1989 "create table child1 (fk1 int, pk1 int, pk2 int, pk3 int, primary key (pk1, pk2, pk3));", 1990 "create table child2 (fk1 int, pk1 int, pk2 int, pk3 int, primary key (pk1, pk2, pk3));", 1991 "create table child3 (fk1 int, pk1 int, pk2 int, pk3 int, primary key (pk1, pk2, pk3));", 1992 "create table child4 (fk1 int, pk1 int, pk2 int, pk3 int, primary key (pk1, pk2, pk3));", 1993 "create index idx4 on child4 (fk1, pk2);", 1994 }, 1995 Assertions: []ScriptTestAssertion{ 1996 { 1997 Query: "alter table child1 add foreign key (fk1, pk1) references parent1 (fk1, pk1);", 1998 ExpectedErr: sql.ErrForeignKeyMissingReferenceIndex, 1999 }, 2000 { 2001 Query: "alter table child1 add foreign key (fk1, pk1, pk2) references parent1 (fk1, pk1, pk2);", 2002 ExpectedErr: sql.ErrForeignKeyMissingReferenceIndex, 2003 }, 2004 { 2005 Query: "alter table child1 add foreign key (fk1, pk2, pk3, pk1) references parent1 (fk1, pk2, pk3, pk1);", 2006 ExpectedErr: sql.ErrForeignKeyMissingReferenceIndex, 2007 }, 2008 { 2009 Query: "alter table child1 add constraint fk1 foreign key (fk1, pk2) references parent1 (fk1, pk2);", 2010 Expected: []sql.Row{ 2011 {types.NewOkResult(0)}, 2012 }, 2013 }, 2014 { 2015 Query: "show create table child1", 2016 Expected: []sql.Row{ 2017 {"child1", "CREATE TABLE `child1` (\n" + 2018 " `fk1` int,\n" + 2019 " `pk1` int NOT NULL,\n" + 2020 " `pk2` int NOT NULL,\n" + 2021 " `pk3` int NOT NULL,\n" + 2022 " PRIMARY KEY (`pk1`,`pk2`,`pk3`),\n" + 2023 " KEY `fk1pk2` (`fk1`,`pk2`),\n" + 2024 " CONSTRAINT `fk1` FOREIGN KEY (`fk1`,`pk2`) REFERENCES `parent1` (`fk1`,`pk2`)\n" + 2025 ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}, 2026 }, 2027 }, 2028 { 2029 Query: "insert into child1 values (0, 1, 2, 3);", 2030 Expected: []sql.Row{ 2031 {types.NewOkResult(1)}, 2032 }, 2033 }, 2034 { 2035 Query: "insert into child1 values (0, 99, 2, 99);", 2036 Expected: []sql.Row{ 2037 {types.NewOkResult(1)}, 2038 }, 2039 }, 2040 { 2041 Query: "insert into child1 values (0, 99, 99, 99);", 2042 ExpectedErr: sql.ErrForeignKeyChildViolation, 2043 }, 2044 { 2045 Query: "alter table child2 add constraint fk2 foreign key (fk1, pk2, pk1) references parent1 (fk1, pk2, pk1);", 2046 Expected: []sql.Row{ 2047 {types.NewOkResult(0)}, 2048 }, 2049 }, 2050 { 2051 Query: "show create table child2", 2052 Expected: []sql.Row{ 2053 {"child2", "CREATE TABLE `child2` (\n" + 2054 " `fk1` int,\n" + 2055 " `pk1` int NOT NULL,\n" + 2056 " `pk2` int NOT NULL,\n" + 2057 " `pk3` int NOT NULL,\n" + 2058 " PRIMARY KEY (`pk1`,`pk2`,`pk3`),\n" + 2059 " KEY `fk1pk2pk1` (`fk1`,`pk2`,`pk1`),\n" + 2060 " CONSTRAINT `fk2` FOREIGN KEY (`fk1`,`pk2`,`pk1`) REFERENCES `parent1` (`fk1`,`pk2`,`pk1`)\n" + 2061 ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}, 2062 }, 2063 }, 2064 { 2065 Query: "insert into child2 values (0, 1, 2, 3);", 2066 Expected: []sql.Row{ 2067 {types.NewOkResult(1)}, 2068 }, 2069 }, 2070 { 2071 Query: "insert into child2 values (0, 1, 2, 99);", 2072 Expected: []sql.Row{ 2073 {types.NewOkResult(1)}, 2074 }, 2075 }, 2076 { 2077 Query: "insert into child2 values (0, 99, 2, 99);", 2078 ExpectedErr: sql.ErrForeignKeyChildViolation, 2079 }, 2080 { 2081 Query: "alter table child3 add constraint fk3 foreign key (fk1, pk2, pk1, pk3) references parent1 (fk1, pk2, pk1, pk3);", 2082 Expected: []sql.Row{ 2083 {types.NewOkResult(0)}, 2084 }, 2085 }, 2086 { 2087 Query: "insert into child3 values (0, 1, 2, 3);", 2088 Expected: []sql.Row{ 2089 {types.NewOkResult(1)}, 2090 }, 2091 }, 2092 { 2093 Query: "show create table child3", 2094 Expected: []sql.Row{ 2095 {"child3", "CREATE TABLE `child3` (\n" + 2096 " `fk1` int,\n" + 2097 " `pk1` int NOT NULL,\n" + 2098 " `pk2` int NOT NULL,\n" + 2099 " `pk3` int NOT NULL,\n" + 2100 " PRIMARY KEY (`pk1`,`pk2`,`pk3`),\n" + 2101 " KEY `fk1pk2pk1pk3` (`fk1`,`pk2`,`pk1`,`pk3`),\n" + 2102 " CONSTRAINT `fk3` FOREIGN KEY (`fk1`,`pk2`,`pk1`,`pk3`) REFERENCES `parent1` (`fk1`,`pk2`,`pk1`,`pk3`)\n" + 2103 ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}, 2104 }, 2105 }, 2106 { 2107 Query: "insert into child3 values (0, 1, 2, 99);", 2108 ExpectedErr: sql.ErrForeignKeyChildViolation, 2109 }, 2110 { // although idx4 would be a valid index, it is not used for the foreign key fk4 2111 Query: "alter table child4 add constraint fk4 foreign key (fk1, pk2, pk1, pk3) references parent1 (fk1, pk2, pk1, pk3);", 2112 Expected: []sql.Row{ 2113 {types.NewOkResult(0)}, 2114 }, 2115 }, 2116 { 2117 Query: "show create table child4", 2118 Expected: []sql.Row{ 2119 {"child4", "CREATE TABLE `child4` (\n" + 2120 " `fk1` int,\n" + 2121 " `pk1` int NOT NULL,\n" + 2122 " `pk2` int NOT NULL,\n" + 2123 " `pk3` int NOT NULL,\n" + 2124 " PRIMARY KEY (`pk1`,`pk2`,`pk3`),\n" + 2125 " KEY `fk1pk2pk1pk3` (`fk1`,`pk2`,`pk1`,`pk3`),\n" + 2126 " KEY `idx4` (`fk1`,`pk2`),\n" + 2127 " CONSTRAINT `fk4` FOREIGN KEY (`fk1`,`pk2`,`pk1`,`pk3`) REFERENCES `parent1` (`fk1`,`pk2`,`pk1`,`pk3`)\n" + 2128 ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}, 2129 }, 2130 }, 2131 { // idx4 satisfies the foreign key fk5 2132 Query: "alter table child4 add constraint fk5 foreign key (fk1) references parent1 (fk1);", 2133 Expected: []sql.Row{ 2134 {types.NewOkResult(0)}, 2135 }, 2136 }, 2137 { 2138 Query: "show create table child4", 2139 Expected: []sql.Row{ 2140 {"child4", "CREATE TABLE `child4` (\n" + 2141 " `fk1` int,\n" + 2142 " `pk1` int NOT NULL,\n" + 2143 " `pk2` int NOT NULL,\n" + 2144 " `pk3` int NOT NULL,\n" + 2145 " PRIMARY KEY (`pk1`,`pk2`,`pk3`),\n" + 2146 " KEY `fk1pk2pk1pk3` (`fk1`,`pk2`,`pk1`,`pk3`),\n" + 2147 " KEY `idx4` (`fk1`,`pk2`),\n" + 2148 " CONSTRAINT `fk4` FOREIGN KEY (`fk1`,`pk2`,`pk1`,`pk3`) REFERENCES `parent1` (`fk1`,`pk2`,`pk1`,`pk3`),\n" + 2149 " CONSTRAINT `fk5` FOREIGN KEY (`fk1`) REFERENCES `parent1` (`fk1`)\n" + 2150 ") ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin"}, 2151 }, 2152 }, 2153 }, 2154 }, 2155 } 2156 2157 var CreateForeignKeyTests = []ScriptTest{ 2158 { 2159 Name: "basic create foreign key tests", 2160 SetUpScript: []string{ 2161 "CREATE TABLE parent(a INTEGER PRIMARY KEY, b INTEGER)", 2162 "ALTER TABLE parent ADD INDEX pb (b)", 2163 `CREATE TABLE child(c INTEGER PRIMARY KEY, d INTEGER, 2164 CONSTRAINT fk1 FOREIGN KEY (D) REFERENCES parent(B) ON DELETE CASCADE 2165 )`, 2166 "ALTER TABLE child ADD CONSTRAINT fk4 FOREIGN KEY (D) REFERENCES child(C)", 2167 "CREATE TABLE child2(e INTEGER PRIMARY KEY, f INTEGER)", 2168 "ALTER TABLE child2 ADD CONSTRAINT fk2 FOREIGN KEY (f) REFERENCES parent(b) ON DELETE RESTRICT", 2169 "ALTER TABLE child2 ADD CONSTRAINT fk3 FOREIGN KEY (f) REFERENCES child(d) ON UPDATE SET NULL", 2170 }, 2171 Assertions: []ScriptTestAssertion{ 2172 { 2173 Query: `SELECT RC.CONSTRAINT_NAME, RC.CONSTRAINT_SCHEMA, RC.TABLE_NAME, KCU.COLUMN_NAME, 2174 KCU.REFERENCED_TABLE_SCHEMA, KCU.REFERENCED_TABLE_NAME, KCU.REFERENCED_COLUMN_NAME, RC.UPDATE_RULE, RC.DELETE_RULE 2175 FROM information_schema.REFERENTIAL_CONSTRAINTS RC, information_schema.KEY_COLUMN_USAGE KCU 2176 WHERE RC.TABLE_NAME = 'child' AND RC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME AND 2177 RC.TABLE_NAME = KCU.TABLE_NAME AND RC.REFERENCED_TABLE_NAME = KCU.REFERENCED_TABLE_NAME;`, 2178 Expected: []sql.Row{ 2179 {"fk1", "mydb", "child", "d", "mydb", "parent", "b", "NO ACTION", "CASCADE"}, 2180 {"fk4", "mydb", "child", "d", "mydb", "child", "c", "NO ACTION", "NO ACTION"}, 2181 }, 2182 }, 2183 { 2184 Query: `SELECT RC.CONSTRAINT_NAME, RC.CONSTRAINT_SCHEMA, RC.TABLE_NAME, KCU.COLUMN_NAME, 2185 KCU.REFERENCED_TABLE_SCHEMA, KCU.REFERENCED_TABLE_NAME, KCU.REFERENCED_COLUMN_NAME, RC.UPDATE_RULE, RC.DELETE_RULE 2186 FROM information_schema.REFERENTIAL_CONSTRAINTS RC, information_schema.KEY_COLUMN_USAGE KCU 2187 WHERE RC.TABLE_NAME = 'child2' AND RC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME AND 2188 RC.TABLE_NAME = KCU.TABLE_NAME AND RC.REFERENCED_TABLE_NAME = KCU.REFERENCED_TABLE_NAME;`, 2189 Expected: []sql.Row{ 2190 {"fk2", "mydb", "child2", "f", "mydb", "parent", "b", "NO ACTION", "RESTRICT"}, 2191 {"fk3", "mydb", "child2", "f", "mydb", "child", "d", "SET NULL", "NO ACTION"}, 2192 }, 2193 }, 2194 }, 2195 }, 2196 { 2197 Name: "error cases", 2198 Assertions: []ScriptTestAssertion{ 2199 { 2200 Query: "ALTER TABLE child2 ADD CONSTRAINT fk3 FOREIGN KEY (f) REFERENCES dne(d) ON UPDATE SET NULL", 2201 ExpectedErr: sql.ErrTableNotFound, 2202 }, 2203 { 2204 Query: "ALTER TABLE dne ADD CONSTRAINT fk4 FOREIGN KEY (f) REFERENCES child(d) ON UPDATE SET NULL", 2205 ExpectedErr: sql.ErrTableNotFound, 2206 }, 2207 { 2208 Query: "ALTER TABLE child2 ADD CONSTRAINT fk5 FOREIGN KEY (f) REFERENCES child(dne) ON UPDATE SET NULL", 2209 ExpectedErr: sql.ErrTableColumnNotFound, 2210 }, 2211 }, 2212 }, 2213 { 2214 Name: "Add a column then immediately add a foreign key", 2215 SetUpScript: []string{ 2216 "CREATE TABLE parent3 (pk BIGINT PRIMARY KEY, v1 BIGINT, INDEX (v1))", 2217 "CREATE TABLE child3 (pk BIGINT PRIMARY KEY);", 2218 }, 2219 Assertions: []ScriptTestAssertion{ 2220 { 2221 Query: "ALTER TABLE child3 ADD COLUMN v1 BIGINT NULL, ADD CONSTRAINT fk_child3 FOREIGN KEY (v1) REFERENCES parent3(v1);", 2222 Expected: []sql.Row{{types.NewOkResult(0)}}, 2223 }, 2224 }, 2225 }, 2226 { 2227 Name: "Do not validate foreign keys if FOREIGN_KEY_CHECKS is set to zero", 2228 Assertions: []ScriptTestAssertion{ 2229 { 2230 Query: "SET FOREIGN_KEY_CHECKS=0;", 2231 Expected: []sql.Row{{}}, 2232 }, 2233 { 2234 Query: "CREATE TABLE child4 (pk BIGINT PRIMARY KEY, CONSTRAINT fk_child4 FOREIGN KEY (pk) REFERENCES delayed_parent4 (pk))", 2235 Expected: []sql.Row{{types.NewOkResult(0)}}, 2236 }, 2237 { 2238 Query: "CREATE TABLE delayed_parent4 (pk BIGINT PRIMARY KEY)", 2239 Expected: []sql.Row{{types.NewOkResult(0)}}, 2240 }, 2241 }, 2242 }, 2243 } 2244 2245 var DropForeignKeyTests = []ScriptTest{ 2246 { 2247 Name: "basic drop foreign key tests", 2248 SetUpScript: []string{ 2249 "CREATE TABLE parent(a INTEGER PRIMARY KEY, b INTEGER)", 2250 "ALTER TABLE parent ADD INDEX pb (b)", 2251 `CREATE TABLE child(c INTEGER PRIMARY KEY, d INTEGER, 2252 CONSTRAINT fk1 FOREIGN KEY (D) REFERENCES parent(B) ON DELETE CASCADE 2253 )`, 2254 "CREATE TABLE child2(e INTEGER PRIMARY KEY, f INTEGER)", 2255 `ALTER TABLE child2 ADD CONSTRAINT fk2 FOREIGN KEY (f) REFERENCES parent(b) ON DELETE RESTRICT, 2256 ADD CONSTRAINT fk3 FOREIGN KEY (f) REFERENCES child(d) ON UPDATE SET NULL`, 2257 }, 2258 Assertions: []ScriptTestAssertion{ 2259 { 2260 Query: "ALTER TABLE child2 DROP CONSTRAINT fk2", 2261 Expected: []sql.Row{{types.NewOkResult(0)}}, 2262 }, 2263 { 2264 Query: `SELECT RC.CONSTRAINT_NAME, RC.CONSTRAINT_SCHEMA, RC.TABLE_NAME, KCU.COLUMN_NAME, 2265 KCU.REFERENCED_TABLE_SCHEMA, KCU.REFERENCED_TABLE_NAME, KCU.REFERENCED_COLUMN_NAME, RC.UPDATE_RULE, RC.DELETE_RULE 2266 FROM information_schema.REFERENTIAL_CONSTRAINTS RC, information_schema.KEY_COLUMN_USAGE KCU 2267 WHERE RC.TABLE_NAME = 'child2' AND RC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME AND 2268 RC.TABLE_NAME = KCU.TABLE_NAME AND RC.REFERENCED_TABLE_NAME = KCU.REFERENCED_TABLE_NAME;`, 2269 Expected: []sql.Row{ 2270 {"fk3", "mydb", "child2", "f", "mydb", "child", "d", "SET NULL", "NO ACTION"}, 2271 }, 2272 }, 2273 { 2274 Query: "ALTER TABLE child2 DROP CONSTRAINT fk3", 2275 Expected: []sql.Row{{types.NewOkResult(0)}}, 2276 }, 2277 { 2278 Query: `SELECT RC.CONSTRAINT_NAME, RC.CONSTRAINT_SCHEMA, RC.TABLE_NAME, KCU.COLUMN_NAME, 2279 KCU.REFERENCED_TABLE_SCHEMA, KCU.REFERENCED_TABLE_NAME, KCU.REFERENCED_COLUMN_NAME, RC.UPDATE_RULE, RC.DELETE_RULE 2280 FROM information_schema.REFERENTIAL_CONSTRAINTS RC, information_schema.KEY_COLUMN_USAGE KCU 2281 WHERE RC.TABLE_NAME = 'child2' AND RC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME AND 2282 RC.TABLE_NAME = KCU.TABLE_NAME AND RC.REFERENCED_TABLE_NAME = KCU.REFERENCED_TABLE_NAME;`, 2283 Expected: []sql.Row{}, 2284 }, 2285 }, 2286 }, 2287 { 2288 Name: "error cases", 2289 Assertions: []ScriptTestAssertion{ 2290 { 2291 Query: "ALTER TABLE child3 DROP CONSTRAINT dne", 2292 ExpectedErr: sql.ErrTableNotFound, 2293 }, 2294 { 2295 Query: "ALTER TABLE child2 DROP CONSTRAINT fk3", 2296 ExpectedErr: sql.ErrUnknownConstraint, 2297 }, 2298 { 2299 Query: "ALTER TABLE child2 DROP FOREIGN KEY fk3", 2300 ExpectedErr: sql.ErrForeignKeyNotFound, 2301 }, 2302 }, 2303 }, 2304 }