github.com/dolthub/dolt/go@v0.40.5-0.20240520175717-68db7794bea6/libraries/doltcore/doltdb/foreign_key_test.go (about) 1 // Copyright 2020 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 doltdb_test 16 17 import ( 18 "context" 19 "testing" 20 21 "github.com/stretchr/testify/assert" 22 "github.com/stretchr/testify/require" 23 24 "github.com/dolthub/dolt/go/cmd/dolt/cli" 25 "github.com/dolthub/dolt/go/cmd/dolt/commands" 26 "github.com/dolthub/dolt/go/libraries/doltcore/doltdb" 27 "github.com/dolthub/dolt/go/libraries/doltcore/dtestutils" 28 ) 29 30 func TestForeignKeyHashOf(t *testing.T) { 31 // Assert that we can get an expected hash for a simple Foreign Key 32 t.Run("HashOf for resolved foreign key", func(t *testing.T) { 33 fk := doltdb.ForeignKey{ 34 Name: "fk1", 35 TableName: "table1", 36 TableIndex: "i1", 37 TableColumns: []uint64{123}, 38 ReferencedTableName: "parentTable", 39 ReferencedTableIndex: "i2", 40 ReferencedTableColumns: []uint64{321}, 41 OnUpdate: 0, 42 OnDelete: 0, 43 UnresolvedFKDetails: doltdb.UnresolvedFKDetails{}, 44 } 45 hashOf, err := fk.HashOf() 46 assert.NoError(t, err) 47 assert.Equal(t, "65brfkb3fh6n7kgpv8d38mjb6krrc54r", hashOf.String()) 48 }) 49 50 // Assert that two unresolved Foreign Keys get unique hashes, when only their unresolved FK details are different 51 t.Run("HashOf for unresolved FK uses unresolved fields", func(t *testing.T) { 52 fk1 := doltdb.ForeignKey{ 53 Name: "", 54 TableName: "table1", 55 TableIndex: "i1", 56 TableColumns: nil, 57 ReferencedTableName: "parentTable", 58 ReferencedTableIndex: "i2", 59 ReferencedTableColumns: nil, 60 OnUpdate: 0, 61 OnDelete: 0, 62 UnresolvedFKDetails: doltdb.UnresolvedFKDetails{ 63 TableColumns: []string{"col1"}, 64 ReferencedTableColumns: []string{"col2"}, 65 }, 66 } 67 hash1, err := fk1.HashOf() 68 assert.NoError(t, err) 69 assert.Equal(t, "qiv9l4juuk20buqml2unlbohfvo95mcd", hash1.String()) 70 71 // Create a second FK that is identical to fk1, except for the unresolved FK details to 72 // assert that the UnresolvedFKDetails fields are used in the hash. 73 fk2 := doltdb.ForeignKey{ 74 Name: "", 75 TableName: "table1", 76 TableIndex: "i1", 77 TableColumns: nil, 78 ReferencedTableName: "parentTable", 79 ReferencedTableIndex: "i2", 80 ReferencedTableColumns: nil, 81 OnUpdate: 0, 82 OnDelete: 0, 83 UnresolvedFKDetails: doltdb.UnresolvedFKDetails{ 84 TableColumns: []string{"col2"}, 85 ReferencedTableColumns: []string{"col2"}, 86 }, 87 } 88 hash2, err := fk2.HashOf() 89 assert.NoError(t, err) 90 assert.Equal(t, "cdglg27qlu0dva6k87vriasnn11o2bnn", hash2.String()) 91 assert.NotEqual(t, hash1, hash2) 92 }) 93 } 94 95 func TestForeignKeys(t *testing.T) { 96 for _, test := range foreignKeyTests { 97 t.Run(test.name, func(t *testing.T) { 98 testForeignKeys(t, test) 99 }) 100 } 101 } 102 103 func TestForeignKeyErrors(t *testing.T) { 104 cmds := []testCommand{ 105 {commands.SqlCmd{}, []string{"-q", `CREATE TABLE test(pk BIGINT PRIMARY KEY, v1 BIGINT, INDEX (v1));`}}, 106 {commands.SqlCmd{}, []string{"-q", `CREATE TABLE test2(pk BIGINT PRIMARY KEY, v1 BIGINT, INDEX (v1),` + 107 `CONSTRAINT child_fk FOREIGN KEY (v1) REFERENCES test(v1));`}}, 108 } 109 110 ctx := context.Background() 111 dEnv := dtestutils.CreateTestEnv() 112 cliCtx, err := commands.NewArgFreeCliContext(ctx, dEnv) 113 require.NoError(t, err) 114 115 for _, c := range cmds { 116 exitCode := c.cmd.Exec(ctx, c.cmd.Name(), c.args, dEnv, cliCtx) 117 require.Equal(t, 0, exitCode) 118 } 119 120 exitCode := commands.SqlCmd{}.Exec(ctx, commands.SqlCmd{}.Name(), []string{"-q", `ALTER TABLE test MODIFY v1 INT;`}, dEnv, cliCtx) 121 require.Equal(t, 1, exitCode) 122 exitCode = commands.SqlCmd{}.Exec(ctx, commands.SqlCmd{}.Name(), []string{"-q", `ALTER TABLE test2 MODIFY v1 INT;`}, dEnv, cliCtx) 123 124 require.Equal(t, 1, exitCode) 125 } 126 127 type foreignKeyTest struct { 128 name string 129 setup []testCommand 130 fks []doltdb.ForeignKey 131 } 132 133 type testCommand struct { 134 cmd cli.Command 135 args []string 136 } 137 138 var fkSetupCommon = []testCommand{ 139 {commands.SqlCmd{}, []string{"-q", "create table parent (" + 140 "id int," + 141 "v1 int," + 142 "v2 int," + 143 "index v1_idx (v1)," + 144 "index v2_idx (v2)," + 145 "primary key(id));"}}, 146 {commands.SqlCmd{}, []string{"-q", "create table child (" + 147 "id int, " + 148 "v1 int," + 149 "v2 int," + 150 "primary key(id));"}}, 151 } 152 153 func testForeignKeys(t *testing.T, test foreignKeyTest) { 154 ctx := context.Background() 155 dEnv := dtestutils.CreateTestEnv() 156 157 cliCtx, verr := commands.NewArgFreeCliContext(ctx, dEnv) 158 require.NoError(t, verr) 159 160 for _, c := range fkSetupCommon { 161 exitCode := c.cmd.Exec(ctx, c.cmd.Name(), c.args, dEnv, cliCtx) 162 require.Equal(t, 0, exitCode) 163 } 164 for _, c := range test.setup { 165 exitCode := c.cmd.Exec(ctx, c.cmd.Name(), c.args, dEnv, cliCtx) 166 require.Equal(t, 0, exitCode) 167 } 168 169 root, err := dEnv.WorkingRoot(ctx) 170 require.NoError(t, err) 171 fkc, err := root.GetForeignKeyCollection(ctx) 172 require.NoError(t, err) 173 174 assert.Equal(t, test.fks, fkc.AllKeys()) 175 176 for _, fk := range test.fks { 177 // verify parent index 178 pt, _, ok, err := doltdb.GetTableInsensitive(ctx, root, fk.ReferencedTableName) 179 require.NoError(t, err) 180 require.True(t, ok) 181 ps, err := pt.GetSchema(ctx) 182 require.NoError(t, err) 183 pi, ok := ps.Indexes().GetByNameCaseInsensitive(fk.ReferencedTableIndex) 184 require.True(t, ok) 185 require.Equal(t, fk.ReferencedTableColumns, pi.IndexedColumnTags()) 186 187 // verify child index 188 ct, _, ok, err := doltdb.GetTableInsensitive(ctx, root, fk.TableName) 189 require.NoError(t, err) 190 require.True(t, ok) 191 cs, err := ct.GetSchema(ctx) 192 require.NoError(t, err) 193 ci, ok := cs.Indexes().GetByNameCaseInsensitive(fk.TableIndex) 194 require.True(t, ok) 195 require.Equal(t, fk.TableColumns, ci.IndexedColumnTags()) 196 } 197 } 198 199 var foreignKeyTests = []foreignKeyTest{ 200 { 201 name: "create foreign key", 202 setup: []testCommand{ 203 {commands.SqlCmd{}, []string{"-q", `alter table child add index v1_idx (v1)`}}, 204 {commands.SqlCmd{}, []string{"-q", `alter table child add 205 constraint child_fk foreign key (v1) references parent(v1)`}}, 206 }, 207 fks: []doltdb.ForeignKey{ 208 { 209 Name: "child_fk", 210 TableName: "child", 211 TableIndex: "v1_idx", 212 TableColumns: []uint64{1215}, 213 ReferencedTableName: "parent", 214 ReferencedTableIndex: "v1_idx", 215 ReferencedTableColumns: []uint64{6269}, 216 UnresolvedFKDetails: doltdb.UnresolvedFKDetails{ 217 TableColumns: []string{"v1"}, 218 ReferencedTableColumns: []string{"v1"}, 219 }, 220 }, 221 }, 222 }, 223 { 224 name: "create multi-column foreign key", 225 setup: []testCommand{ 226 {commands.SqlCmd{}, []string{"-q", `alter table parent add index v1v2_idx (v1, v2)`}}, 227 {commands.SqlCmd{}, []string{"-q", `alter table child add index v1v2_idx (v1, v2)`}}, 228 {commands.SqlCmd{}, []string{"-q", `alter table child add 229 constraint multi_col foreign key (v1, v2) references parent(v1, v2)`}}, 230 }, 231 fks: []doltdb.ForeignKey{ 232 { 233 Name: "multi_col", 234 TableName: "child", 235 TableIndex: "v1v2_idx", 236 TableColumns: []uint64{1215, 8734}, 237 ReferencedTableName: "parent", 238 ReferencedTableIndex: "v1v2_idx", 239 ReferencedTableColumns: []uint64{6269, 7947}, 240 UnresolvedFKDetails: doltdb.UnresolvedFKDetails{ 241 TableColumns: []string{"v1", "v2"}, 242 ReferencedTableColumns: []string{"v1", "v2"}, 243 }, 244 }, 245 }, 246 }, 247 { 248 name: "create multiple foreign keys", 249 setup: []testCommand{ 250 {commands.SqlCmd{}, []string{"-q", `alter table child add index v1_idx (v1)`}}, 251 {commands.SqlCmd{}, []string{"-q", `alter table child add index v2_idx (v2)`}}, 252 {commands.SqlCmd{}, []string{"-q", `alter table child 253 add constraint fk1 foreign key (v1) references parent(v1)`}}, 254 {commands.SqlCmd{}, []string{"-q", `alter table child 255 add constraint fk2 foreign key (v2) references parent(v2)`}}, 256 }, 257 fks: []doltdb.ForeignKey{ 258 { 259 Name: "fk1", 260 TableName: "child", 261 TableIndex: "v1_idx", 262 TableColumns: []uint64{1215}, 263 ReferencedTableName: "parent", 264 ReferencedTableIndex: "v1_idx", 265 ReferencedTableColumns: []uint64{6269}, 266 UnresolvedFKDetails: doltdb.UnresolvedFKDetails{ 267 TableColumns: []string{"v1"}, 268 ReferencedTableColumns: []string{"v1"}, 269 }, 270 }, 271 { 272 Name: "fk2", 273 TableName: "child", 274 TableIndex: "v2_idx", 275 TableColumns: []uint64{8734}, 276 ReferencedTableName: "parent", 277 ReferencedTableIndex: "v2_idx", 278 ReferencedTableColumns: []uint64{7947}, 279 UnresolvedFKDetails: doltdb.UnresolvedFKDetails{ 280 TableColumns: []string{"v2"}, 281 ReferencedTableColumns: []string{"v2"}, 282 }, 283 }, 284 }, 285 }, 286 { 287 name: "create table with foreign key", 288 setup: []testCommand{ 289 {commands.SqlCmd{}, []string{"-q", `create table new_table ( 290 id int, 291 v1 int, 292 constraint new_fk foreign key (v1) references parent(v1), 293 primary key(id));`}}, 294 }, 295 fks: []doltdb.ForeignKey{ 296 { 297 Name: "new_fk", 298 TableName: "new_table", 299 // unnamed indexes take the column name 300 TableIndex: "v1", 301 TableColumns: []uint64{7597}, 302 ReferencedTableName: "parent", 303 ReferencedTableIndex: "v1_idx", 304 ReferencedTableColumns: []uint64{6269}, 305 UnresolvedFKDetails: doltdb.UnresolvedFKDetails{ 306 TableColumns: []string{"v1"}, 307 ReferencedTableColumns: []string{"v1"}, 308 }, 309 }, 310 }, 311 }, 312 { 313 name: "create foreign keys with update or delete rules", 314 setup: []testCommand{ 315 {commands.SqlCmd{}, []string{"-q", `alter table child add index v1_idx (v1)`}}, 316 {commands.SqlCmd{}, []string{"-q", `alter table child add index v2_idx (v2)`}}, 317 {commands.SqlCmd{}, []string{"-q", `alter table child 318 add constraint fk1 foreign key (v1) references parent(v1) on update cascade`}}, 319 {commands.SqlCmd{}, []string{"-q", `alter table child 320 add constraint fk2 foreign key (v2) references parent(v2) on delete set null`}}, 321 }, 322 fks: []doltdb.ForeignKey{ 323 { 324 Name: "fk1", 325 TableName: "child", 326 TableIndex: "v1_idx", 327 TableColumns: []uint64{1215}, 328 ReferencedTableName: "parent", 329 ReferencedTableIndex: "v1_idx", 330 ReferencedTableColumns: []uint64{6269}, 331 OnUpdate: doltdb.ForeignKeyReferentialAction_Cascade, 332 UnresolvedFKDetails: doltdb.UnresolvedFKDetails{ 333 TableColumns: []string{"v1"}, 334 ReferencedTableColumns: []string{"v1"}, 335 }, 336 }, 337 { 338 Name: "fk2", 339 TableName: "child", 340 TableIndex: "v2_idx", 341 TableColumns: []uint64{8734}, 342 ReferencedTableName: "parent", 343 ReferencedTableIndex: "v2_idx", 344 ReferencedTableColumns: []uint64{7947}, 345 OnDelete: doltdb.ForeignKeyReferentialAction_SetNull, 346 UnresolvedFKDetails: doltdb.UnresolvedFKDetails{ 347 TableColumns: []string{"v2"}, 348 ReferencedTableColumns: []string{"v2"}, 349 }, 350 }, 351 }, 352 }, 353 { 354 name: "create single foreign key with update and delete rules", 355 setup: []testCommand{ 356 {commands.SqlCmd{}, []string{"-q", `alter table child add index v1_idx (v1)`}}, 357 {commands.SqlCmd{}, []string{"-q", `alter table child 358 add constraint child_fk foreign key (v1) references parent(v1) on update cascade on delete cascade`}}, 359 }, 360 fks: []doltdb.ForeignKey{ 361 { 362 Name: "child_fk", 363 TableName: "child", 364 TableIndex: "v1_idx", 365 TableColumns: []uint64{1215}, 366 ReferencedTableName: "parent", 367 ReferencedTableIndex: "v1_idx", 368 ReferencedTableColumns: []uint64{6269}, 369 OnUpdate: doltdb.ForeignKeyReferentialAction_Cascade, 370 OnDelete: doltdb.ForeignKeyReferentialAction_Cascade, 371 UnresolvedFKDetails: doltdb.UnresolvedFKDetails{ 372 TableColumns: []string{"v1"}, 373 ReferencedTableColumns: []string{"v1"}, 374 }, 375 }, 376 }, 377 }, 378 { 379 name: "create foreign keys with all update and delete rules", 380 setup: []testCommand{ 381 {commands.SqlCmd{}, []string{"-q", "alter table parent add column v3 int;"}}, 382 {commands.SqlCmd{}, []string{"-q", "alter table parent add column v4 int;"}}, 383 {commands.SqlCmd{}, []string{"-q", "alter table parent add column v5 int;"}}, 384 {commands.SqlCmd{}, []string{"-q", "alter table parent add index v3_idx (v3);"}}, 385 {commands.SqlCmd{}, []string{"-q", "alter table parent add index v4_idx (v4);"}}, 386 {commands.SqlCmd{}, []string{"-q", "alter table parent add index v5_idx (v5);"}}, 387 {commands.SqlCmd{}, []string{"-q", `create table sibling ( 388 id int, 389 v1 int, 390 v2 int, 391 v3 int, 392 v4 int, 393 v5 int, 394 constraint fk1 foreign key (v1) references parent(v1), 395 constraint fk2 foreign key (v2) references parent(v2) on delete restrict on update restrict, 396 constraint fk3 foreign key (v3) references parent(v3) on delete cascade on update cascade, 397 constraint fk4 foreign key (v4) references parent(v4) on delete set null on update set null, 398 constraint fk5 foreign key (v5) references parent(v5) on delete no action on update no action, 399 primary key (id));`}}, 400 }, 401 fks: []doltdb.ForeignKey{ 402 { 403 Name: "fk1", 404 TableName: "sibling", 405 TableIndex: "v1", 406 TableColumns: []uint64{16080}, 407 ReferencedTableName: "parent", 408 ReferencedTableIndex: "v1_idx", 409 ReferencedTableColumns: []uint64{6269}, 410 UnresolvedFKDetails: doltdb.UnresolvedFKDetails{ 411 TableColumns: []string{"v1"}, 412 ReferencedTableColumns: []string{"v1"}, 413 }, 414 }, 415 { 416 Name: "fk2", 417 TableName: "sibling", 418 TableIndex: "v2", 419 TableColumns: []uint64{7576}, 420 ReferencedTableName: "parent", 421 ReferencedTableIndex: "v2_idx", 422 ReferencedTableColumns: []uint64{7947}, 423 OnUpdate: doltdb.ForeignKeyReferentialAction_Restrict, 424 OnDelete: doltdb.ForeignKeyReferentialAction_Restrict, 425 UnresolvedFKDetails: doltdb.UnresolvedFKDetails{ 426 TableColumns: []string{"v2"}, 427 ReferencedTableColumns: []string{"v2"}, 428 }, 429 }, 430 { 431 Name: "fk3", 432 TableName: "sibling", 433 TableIndex: "v3", 434 TableColumns: []uint64{16245}, 435 ReferencedTableName: "parent", 436 ReferencedTableIndex: "v3_idx", 437 ReferencedTableColumns: []uint64{5237}, 438 OnUpdate: doltdb.ForeignKeyReferentialAction_Cascade, 439 OnDelete: doltdb.ForeignKeyReferentialAction_Cascade, 440 UnresolvedFKDetails: doltdb.UnresolvedFKDetails{ 441 TableColumns: []string{"v3"}, 442 ReferencedTableColumns: []string{"v3"}, 443 }, 444 }, 445 { 446 Name: "fk4", 447 TableName: "sibling", 448 TableIndex: "v4", 449 TableColumns: []uint64{9036}, 450 ReferencedTableName: "parent", 451 ReferencedTableIndex: "v4_idx", 452 ReferencedTableColumns: []uint64{14774}, 453 OnUpdate: doltdb.ForeignKeyReferentialAction_SetNull, 454 OnDelete: doltdb.ForeignKeyReferentialAction_SetNull, 455 UnresolvedFKDetails: doltdb.UnresolvedFKDetails{ 456 TableColumns: []string{"v4"}, 457 ReferencedTableColumns: []string{"v4"}, 458 }, 459 }, 460 { 461 Name: "fk5", 462 TableName: "sibling", 463 TableIndex: "v5", 464 TableColumns: []uint64{11586}, 465 ReferencedTableName: "parent", 466 ReferencedTableIndex: "v5_idx", 467 ReferencedTableColumns: []uint64{8125}, 468 OnUpdate: doltdb.ForeignKeyReferentialAction_NoAction, 469 OnDelete: doltdb.ForeignKeyReferentialAction_NoAction, 470 UnresolvedFKDetails: doltdb.UnresolvedFKDetails{ 471 TableColumns: []string{"v5"}, 472 ReferencedTableColumns: []string{"v5"}, 473 }, 474 }, 475 }, 476 }, 477 { 478 name: "create foreign key without preexisting child index", 479 setup: []testCommand{ 480 {commands.SqlCmd{}, []string{"-q", `alter table child add constraint child_fk foreign key (v1) references parent(v1)`}}, 481 }, 482 fks: []doltdb.ForeignKey{ 483 { 484 Name: "child_fk", 485 TableName: "child", 486 // unnamed indexes take the column name 487 TableIndex: "v1", 488 TableColumns: []uint64{1215}, 489 ReferencedTableName: "parent", 490 ReferencedTableIndex: "v1_idx", 491 ReferencedTableColumns: []uint64{6269}, 492 UnresolvedFKDetails: doltdb.UnresolvedFKDetails{ 493 TableColumns: []string{"v1"}, 494 ReferencedTableColumns: []string{"v1"}, 495 }, 496 }, 497 }, 498 }, 499 { 500 name: "create unnamed foreign key", 501 setup: []testCommand{ 502 {commands.SqlCmd{}, []string{"-q", `alter table child add index v1_idx (v1)`}}, 503 {commands.SqlCmd{}, []string{"-q", `alter table child add foreign key (v1) references parent(v1)`}}, 504 }, 505 fks: []doltdb.ForeignKey{ 506 { 507 Name: "child_ibfk_1", 508 TableName: "child", 509 TableIndex: "v1_idx", 510 TableColumns: []uint64{1215}, 511 ReferencedTableName: "parent", 512 ReferencedTableIndex: "v1_idx", 513 ReferencedTableColumns: []uint64{6269}, 514 UnresolvedFKDetails: doltdb.UnresolvedFKDetails{ 515 TableColumns: []string{"v1"}, 516 ReferencedTableColumns: []string{"v1"}, 517 }, 518 }, 519 }, 520 }, 521 { 522 name: "create table with unnamed foreign key", 523 setup: []testCommand{ 524 {commands.SqlCmd{}, []string{"-q", `create table new_table ( 525 id int, 526 v1 int, 527 foreign key (v1) references parent(v1), 528 primary key(id));`}}, 529 }, 530 fks: []doltdb.ForeignKey{ 531 { 532 Name: "new_table_ibfk_1", 533 TableName: "new_table", 534 // unnamed indexes take the column name 535 TableIndex: "v1", 536 TableColumns: []uint64{7597}, 537 ReferencedTableName: "parent", 538 ReferencedTableIndex: "v1_idx", 539 ReferencedTableColumns: []uint64{6269}, 540 UnresolvedFKDetails: doltdb.UnresolvedFKDetails{ 541 TableColumns: []string{"v1"}, 542 ReferencedTableColumns: []string{"v1"}, 543 }, 544 }, 545 }, 546 }, 547 { 548 name: "create unnamed multi-column foreign key", 549 setup: []testCommand{ 550 {commands.SqlCmd{}, []string{"-q", `alter table parent add index v1v2_idx (v1, v2)`}}, 551 {commands.SqlCmd{}, []string{"-q", `alter table child 552 add index v1v2_idx (v1, v2)`}}, 553 {commands.SqlCmd{}, []string{"-q", `alter table child 554 add foreign key (v1, v2) references parent(v1, v2)`}}, 555 }, 556 fks: []doltdb.ForeignKey{ 557 { 558 Name: "child_ibfk_1", 559 TableName: "child", 560 TableIndex: "v1v2_idx", 561 TableColumns: []uint64{1215, 8734}, 562 ReferencedTableName: "parent", 563 ReferencedTableIndex: "v1v2_idx", 564 ReferencedTableColumns: []uint64{6269, 7947}, 565 UnresolvedFKDetails: doltdb.UnresolvedFKDetails{ 566 TableColumns: []string{"v1", "v2"}, 567 ReferencedTableColumns: []string{"v1", "v2"}, 568 }, 569 }, 570 }, 571 }, 572 { 573 name: "create multiple unnamed foreign keys", 574 setup: []testCommand{ 575 {commands.SqlCmd{}, []string{"-q", `alter table child add index v1_idx (v1)`}}, 576 {commands.SqlCmd{}, []string{"-q", `alter table child add index v2_idx (v2)`}}, 577 {commands.SqlCmd{}, []string{"-q", `alter table child 578 add foreign key (v1) references parent(v1)`}}, 579 {commands.SqlCmd{}, []string{"-q", `alter table child 580 add foreign key (v2) references parent(v2)`}}, 581 }, 582 fks: []doltdb.ForeignKey{ 583 { 584 Name: "child_ibfk_1", 585 TableName: "child", 586 TableIndex: "v1_idx", 587 TableColumns: []uint64{1215}, 588 ReferencedTableName: "parent", 589 ReferencedTableIndex: "v1_idx", 590 ReferencedTableColumns: []uint64{6269}, 591 UnresolvedFKDetails: doltdb.UnresolvedFKDetails{ 592 TableColumns: []string{"v1"}, 593 ReferencedTableColumns: []string{"v1"}, 594 }, 595 }, 596 { 597 Name: "child_ibfk_2", 598 TableName: "child", 599 TableIndex: "v2_idx", 600 TableColumns: []uint64{8734}, 601 ReferencedTableName: "parent", 602 ReferencedTableIndex: "v2_idx", 603 ReferencedTableColumns: []uint64{7947}, 604 UnresolvedFKDetails: doltdb.UnresolvedFKDetails{ 605 TableColumns: []string{"v2"}, 606 ReferencedTableColumns: []string{"v2"}, 607 }, 608 }, 609 }, 610 }, 611 { 612 name: "create foreign key with pre-existing data", 613 setup: []testCommand{ 614 {commands.SqlCmd{}, []string{"-q", `insert into parent (id,v1,v2) values 615 (1,1,1), 616 (2,2,2);`}}, 617 {commands.SqlCmd{}, []string{"-q", `insert into child (id,v1,v2) values 618 (1,1,1), 619 (2,2,2), 620 (3,NULL,3);`}}, 621 {commands.SqlCmd{}, []string{"-q", `alter table child add index v1_idx (v1)`}}, 622 {commands.SqlCmd{}, []string{"-q", `alter table child 623 add constraint fk1 foreign key (v1) references parent(v1)`}}, 624 }, 625 fks: []doltdb.ForeignKey{ 626 { 627 Name: "fk1", 628 TableName: "child", 629 TableIndex: "v1_idx", 630 TableColumns: []uint64{1215}, 631 ReferencedTableName: "parent", 632 ReferencedTableIndex: "v1_idx", 633 ReferencedTableColumns: []uint64{6269}, 634 UnresolvedFKDetails: doltdb.UnresolvedFKDetails{ 635 TableColumns: []string{"v1"}, 636 ReferencedTableColumns: []string{"v1"}, 637 }, 638 }, 639 }, 640 }, 641 { 642 name: "create multi-col foreign key with pre-existing data", 643 setup: []testCommand{ 644 {commands.SqlCmd{}, []string{"-q", `insert into parent (id,v1,v2) values 645 (1,1,1), 646 (2,2,NULL), 647 (3,NULL,3), 648 (4,NULL,NULL);`}}, 649 {commands.SqlCmd{}, []string{"-q", `insert into child (id,v1,v2) values 650 (1,1,1), 651 (2,2,NULL), 652 (3,NULL,3);`}}, 653 {commands.SqlCmd{}, []string{"-q", `alter table parent add index v1v2 (v1,v2)`}}, 654 {commands.SqlCmd{}, []string{"-q", `alter table child add index v1v2 (v1,v2)`}}, 655 {commands.SqlCmd{}, []string{"-q", `alter table child 656 add constraint fk1 foreign key (v1,v2) references parent(v1,v2)`}}, 657 }, 658 fks: []doltdb.ForeignKey{ 659 { 660 Name: "fk1", 661 TableName: "child", 662 TableIndex: "v1v2", 663 TableColumns: []uint64{1215, 8734}, 664 ReferencedTableName: "parent", 665 ReferencedTableIndex: "v1v2", 666 ReferencedTableColumns: []uint64{6269, 7947}, 667 UnresolvedFKDetails: doltdb.UnresolvedFKDetails{ 668 TableColumns: []string{"v1", "v2"}, 669 ReferencedTableColumns: []string{"v1", "v2"}, 670 }, 671 }, 672 }, 673 }, 674 }