github.com/hasnat/dolt/go@v0.0.0-20210628190320-9eb5d843fbb7/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 TestForeignKeys(t *testing.T) { 31 for _, test := range foreignKeyTests { 32 t.Run(test.name, func(t *testing.T) { 33 testForeignKeys(t, test) 34 }) 35 } 36 } 37 38 func TestForeignKeyErrors(t *testing.T) { 39 cmds := []testCommand{ 40 {commands.SqlCmd{}, []string{"-q", `CREATE TABLE test(pk BIGINT PRIMARY KEY, v1 BIGINT, INDEX (v1));`}}, 41 {commands.SqlCmd{}, []string{"-q", `CREATE TABLE test2(pk BIGINT PRIMARY KEY, v1 BIGINT, INDEX (v1),` + 42 `CONSTRAINT child_fk FOREIGN KEY (v1) REFERENCES test(v1));`}}, 43 } 44 45 ctx := context.Background() 46 dEnv := dtestutils.CreateTestEnv() 47 48 for _, c := range cmds { 49 exitCode := c.cmd.Exec(ctx, c.cmd.Name(), c.args, dEnv) 50 require.Equal(t, 0, exitCode) 51 } 52 53 exitCode := commands.SqlCmd{}.Exec(ctx, commands.SqlCmd{}.Name(), []string{"-q", `ALTER TABLE test MODIFY v1 INT;`}, dEnv) 54 require.Equal(t, 1, exitCode) 55 exitCode = commands.SqlCmd{}.Exec(ctx, commands.SqlCmd{}.Name(), []string{"-q", `ALTER TABLE test2 MODIFY v1 INT;`}, dEnv) 56 require.Equal(t, 1, exitCode) 57 } 58 59 type foreignKeyTest struct { 60 name string 61 setup []testCommand 62 fks []doltdb.ForeignKey 63 } 64 65 type testCommand struct { 66 cmd cli.Command 67 args []string 68 } 69 70 var fkSetupCommon = []testCommand{ 71 {commands.SqlCmd{}, []string{"-q", "create table parent (" + 72 "id int," + 73 "v1 int," + 74 "v2 int," + 75 "index v1_idx (v1)," + 76 "index v2_idx (v2)," + 77 "primary key(id));"}}, 78 {commands.SqlCmd{}, []string{"-q", "create table child (" + 79 "id int, " + 80 "v1 int," + 81 "v2 int," + 82 "primary key(id));"}}, 83 } 84 85 func testForeignKeys(t *testing.T, test foreignKeyTest) { 86 ctx := context.Background() 87 dEnv := dtestutils.CreateTestEnv() 88 89 for _, c := range fkSetupCommon { 90 exitCode := c.cmd.Exec(ctx, c.cmd.Name(), c.args, dEnv) 91 require.Equal(t, 0, exitCode) 92 } 93 for _, c := range test.setup { 94 exitCode := c.cmd.Exec(ctx, c.cmd.Name(), c.args, dEnv) 95 require.Equal(t, 0, exitCode) 96 } 97 98 root, err := dEnv.WorkingRoot(ctx) 99 require.NoError(t, err) 100 fkc, err := root.GetForeignKeyCollection(ctx) 101 require.NoError(t, err) 102 103 assert.Equal(t, test.fks, fkc.AllKeys()) 104 105 for _, fk := range test.fks { 106 // verify parent index 107 pt, _, ok, err := root.GetTableInsensitive(ctx, fk.ReferencedTableName) 108 require.NoError(t, err) 109 require.True(t, ok) 110 ps, err := pt.GetSchema(ctx) 111 require.NoError(t, err) 112 pi, ok := ps.Indexes().GetByNameCaseInsensitive(fk.ReferencedTableIndex) 113 require.True(t, ok) 114 require.Equal(t, fk.ReferencedTableColumns, pi.IndexedColumnTags()) 115 116 // verify child index 117 ct, _, ok, err := root.GetTableInsensitive(ctx, fk.TableName) 118 require.NoError(t, err) 119 require.True(t, ok) 120 cs, err := ct.GetSchema(ctx) 121 require.NoError(t, err) 122 ci, ok := cs.Indexes().GetByNameCaseInsensitive(fk.TableIndex) 123 require.True(t, ok) 124 require.Equal(t, fk.TableColumns, ci.IndexedColumnTags()) 125 } 126 } 127 128 var foreignKeyTests = []foreignKeyTest{ 129 { 130 name: "create foreign key", 131 setup: []testCommand{ 132 {commands.SqlCmd{}, []string{"-q", `alter table child add index v1_idx (v1)`}}, 133 {commands.SqlCmd{}, []string{"-q", `alter table child add 134 constraint child_fk foreign key (v1) references parent(v1)`}}, 135 }, 136 fks: []doltdb.ForeignKey{ 137 { 138 Name: "child_fk", 139 TableName: "child", 140 TableIndex: "v1_idx", 141 TableColumns: []uint64{1215}, 142 ReferencedTableName: "parent", 143 ReferencedTableIndex: "v1_idx", 144 ReferencedTableColumns: []uint64{6269}, 145 }, 146 }, 147 }, 148 { 149 name: "create multi-column foreign key", 150 setup: []testCommand{ 151 {commands.SqlCmd{}, []string{"-q", `alter table parent add index v1v2_idx (v1, v2)`}}, 152 {commands.SqlCmd{}, []string{"-q", `alter table child add index v1v2_idx (v1, v2)`}}, 153 {commands.SqlCmd{}, []string{"-q", `alter table child add 154 constraint multi_col foreign key (v1, v2) references parent(v1, v2)`}}, 155 }, 156 fks: []doltdb.ForeignKey{ 157 { 158 Name: "multi_col", 159 TableName: "child", 160 TableIndex: "v1v2_idx", 161 TableColumns: []uint64{1215, 8734}, 162 ReferencedTableName: "parent", 163 ReferencedTableIndex: "v1v2_idx", 164 ReferencedTableColumns: []uint64{6269, 7947}, 165 }, 166 }, 167 }, 168 { 169 name: "create multiple foreign keys", 170 setup: []testCommand{ 171 {commands.SqlCmd{}, []string{"-q", `alter table child add index v1_idx (v1)`}}, 172 {commands.SqlCmd{}, []string{"-q", `alter table child add index v2_idx (v2)`}}, 173 {commands.SqlCmd{}, []string{"-q", `alter table child 174 add constraint fk1 foreign key (v1) references parent(v1)`}}, 175 {commands.SqlCmd{}, []string{"-q", `alter table child 176 add constraint fk2 foreign key (v2) references parent(v2)`}}, 177 }, 178 fks: []doltdb.ForeignKey{ 179 { 180 Name: "fk1", 181 TableName: "child", 182 TableIndex: "v1_idx", 183 TableColumns: []uint64{1215}, 184 ReferencedTableName: "parent", 185 ReferencedTableIndex: "v1_idx", 186 ReferencedTableColumns: []uint64{6269}, 187 }, 188 { 189 Name: "fk2", 190 TableName: "child", 191 TableIndex: "v2_idx", 192 TableColumns: []uint64{8734}, 193 ReferencedTableName: "parent", 194 ReferencedTableIndex: "v2_idx", 195 ReferencedTableColumns: []uint64{7947}, 196 }, 197 }, 198 }, 199 { 200 name: "create table with foreign key", 201 setup: []testCommand{ 202 {commands.SqlCmd{}, []string{"-q", `create table new_table ( 203 id int, 204 v1 int, 205 constraint new_fk foreign key (v1) references parent(v1), 206 primary key(id));`}}, 207 }, 208 fks: []doltdb.ForeignKey{ 209 { 210 Name: "new_fk", 211 TableName: "new_table", 212 // unnamed indexes take the column name 213 TableIndex: "v1", 214 TableColumns: []uint64{7597}, 215 ReferencedTableName: "parent", 216 ReferencedTableIndex: "v1_idx", 217 ReferencedTableColumns: []uint64{6269}, 218 }, 219 }, 220 }, 221 { 222 name: "create foreign keys with update or delete rules", 223 setup: []testCommand{ 224 {commands.SqlCmd{}, []string{"-q", `alter table child add index v1_idx (v1)`}}, 225 {commands.SqlCmd{}, []string{"-q", `alter table child add index v2_idx (v2)`}}, 226 {commands.SqlCmd{}, []string{"-q", `alter table child 227 add constraint fk1 foreign key (v1) references parent(v1) on update cascade`}}, 228 {commands.SqlCmd{}, []string{"-q", `alter table child 229 add constraint fk2 foreign key (v2) references parent(v2) on delete set null`}}, 230 }, 231 fks: []doltdb.ForeignKey{ 232 { 233 Name: "fk1", 234 TableName: "child", 235 TableIndex: "v1_idx", 236 TableColumns: []uint64{1215}, 237 ReferencedTableName: "parent", 238 ReferencedTableIndex: "v1_idx", 239 ReferencedTableColumns: []uint64{6269}, 240 OnUpdate: doltdb.ForeignKeyReferenceOption_Cascade, 241 }, 242 { 243 Name: "fk2", 244 TableName: "child", 245 TableIndex: "v2_idx", 246 TableColumns: []uint64{8734}, 247 ReferencedTableName: "parent", 248 ReferencedTableIndex: "v2_idx", 249 ReferencedTableColumns: []uint64{7947}, 250 OnDelete: doltdb.ForeignKeyReferenceOption_SetNull, 251 }, 252 }, 253 }, 254 { 255 name: "create single foreign key with update and delete rules", 256 setup: []testCommand{ 257 {commands.SqlCmd{}, []string{"-q", `alter table child add index v1_idx (v1)`}}, 258 {commands.SqlCmd{}, []string{"-q", `alter table child 259 add constraint child_fk foreign key (v1) references parent(v1) on update cascade on delete cascade`}}, 260 }, 261 fks: []doltdb.ForeignKey{ 262 { 263 Name: "child_fk", 264 TableName: "child", 265 TableIndex: "v1_idx", 266 TableColumns: []uint64{1215}, 267 ReferencedTableName: "parent", 268 ReferencedTableIndex: "v1_idx", 269 ReferencedTableColumns: []uint64{6269}, 270 OnUpdate: doltdb.ForeignKeyReferenceOption_Cascade, 271 OnDelete: doltdb.ForeignKeyReferenceOption_Cascade, 272 }, 273 }, 274 }, 275 { 276 name: "create foreign keys with all update and delete rules", 277 setup: []testCommand{ 278 {commands.SqlCmd{}, []string{"-q", "alter table parent add column v3 int;"}}, 279 {commands.SqlCmd{}, []string{"-q", "alter table parent add column v4 int;"}}, 280 {commands.SqlCmd{}, []string{"-q", "alter table parent add column v5 int;"}}, 281 {commands.SqlCmd{}, []string{"-q", "alter table parent add index v3_idx (v3);"}}, 282 {commands.SqlCmd{}, []string{"-q", "alter table parent add index v4_idx (v4);"}}, 283 {commands.SqlCmd{}, []string{"-q", "alter table parent add index v5_idx (v5);"}}, 284 {commands.SqlCmd{}, []string{"-q", `create table sibling ( 285 id int, 286 v1 int, 287 v2 int, 288 v3 int, 289 v4 int, 290 v5 int, 291 constraint fk1 foreign key (v1) references parent(v1), 292 constraint fk2 foreign key (v2) references parent(v2) on delete restrict on update restrict, 293 constraint fk3 foreign key (v3) references parent(v3) on delete cascade on update cascade, 294 constraint fk4 foreign key (v4) references parent(v4) on delete set null on update set null, 295 constraint fk5 foreign key (v5) references parent(v5) on delete no action on update no action, 296 primary key (id));`}}, 297 }, 298 fks: []doltdb.ForeignKey{ 299 { 300 Name: "fk1", 301 TableName: "sibling", 302 TableIndex: "v1", 303 TableColumns: []uint64{16080}, 304 ReferencedTableName: "parent", 305 ReferencedTableIndex: "v1_idx", 306 ReferencedTableColumns: []uint64{6269}, 307 }, 308 { 309 Name: "fk2", 310 TableName: "sibling", 311 TableIndex: "v2", 312 TableColumns: []uint64{7576}, 313 ReferencedTableName: "parent", 314 ReferencedTableIndex: "v2_idx", 315 ReferencedTableColumns: []uint64{7947}, 316 OnUpdate: doltdb.ForeignKeyReferenceOption_Restrict, 317 OnDelete: doltdb.ForeignKeyReferenceOption_Restrict, 318 }, 319 { 320 Name: "fk3", 321 TableName: "sibling", 322 TableIndex: "v3", 323 TableColumns: []uint64{16245}, 324 ReferencedTableName: "parent", 325 ReferencedTableIndex: "v3_idx", 326 ReferencedTableColumns: []uint64{5237}, 327 OnUpdate: doltdb.ForeignKeyReferenceOption_Cascade, 328 OnDelete: doltdb.ForeignKeyReferenceOption_Cascade, 329 }, 330 { 331 Name: "fk4", 332 TableName: "sibling", 333 TableIndex: "v4", 334 TableColumns: []uint64{9036}, 335 ReferencedTableName: "parent", 336 ReferencedTableIndex: "v4_idx", 337 ReferencedTableColumns: []uint64{14774}, 338 OnUpdate: doltdb.ForeignKeyReferenceOption_SetNull, 339 OnDelete: doltdb.ForeignKeyReferenceOption_SetNull, 340 }, 341 { 342 Name: "fk5", 343 TableName: "sibling", 344 TableIndex: "v5", 345 TableColumns: []uint64{11586}, 346 ReferencedTableName: "parent", 347 ReferencedTableIndex: "v5_idx", 348 ReferencedTableColumns: []uint64{8125}, 349 OnUpdate: doltdb.ForeignKeyReferenceOption_NoAction, 350 OnDelete: doltdb.ForeignKeyReferenceOption_NoAction, 351 }, 352 }, 353 }, 354 { 355 name: "create foreign key without preexisting child index", 356 setup: []testCommand{ 357 {commands.SqlCmd{}, []string{"-q", `alter table child add constraint child_fk foreign key (v1) references parent(v1)`}}, 358 }, 359 fks: []doltdb.ForeignKey{ 360 { 361 Name: "child_fk", 362 TableName: "child", 363 // unnamed indexes take the column name 364 TableIndex: "v1", 365 TableColumns: []uint64{1215}, 366 ReferencedTableName: "parent", 367 ReferencedTableIndex: "v1_idx", 368 ReferencedTableColumns: []uint64{6269}, 369 }, 370 }, 371 }, 372 { 373 name: "create unnamed foreign key", 374 setup: []testCommand{ 375 {commands.SqlCmd{}, []string{"-q", `alter table child add index v1_idx (v1)`}}, 376 {commands.SqlCmd{}, []string{"-q", `alter table child add foreign key (v1) references parent(v1)`}}, 377 }, 378 fks: []doltdb.ForeignKey{ 379 { 380 Name: "19eof0mu", 381 TableName: "child", 382 TableIndex: "v1_idx", 383 TableColumns: []uint64{1215}, 384 ReferencedTableName: "parent", 385 ReferencedTableIndex: "v1_idx", 386 ReferencedTableColumns: []uint64{6269}, 387 }, 388 }, 389 }, 390 { 391 name: "create table with unnamed foreign key", 392 setup: []testCommand{ 393 {commands.SqlCmd{}, []string{"-q", `create table new_table ( 394 id int, 395 v1 int, 396 foreign key (v1) references parent(v1), 397 primary key(id));`}}, 398 }, 399 fks: []doltdb.ForeignKey{ 400 { 401 Name: "mv9a59oo", 402 TableName: "new_table", 403 // unnamed indexes take the column name 404 TableIndex: "v1", 405 TableColumns: []uint64{7597}, 406 ReferencedTableName: "parent", 407 ReferencedTableIndex: "v1_idx", 408 ReferencedTableColumns: []uint64{6269}, 409 }, 410 }, 411 }, 412 { 413 name: "create unnamed multi-column foreign key", 414 setup: []testCommand{ 415 {commands.SqlCmd{}, []string{"-q", `alter table parent add index v1v2_idx (v1, v2)`}}, 416 {commands.SqlCmd{}, []string{"-q", `alter table child 417 add index v1v2_idx (v1, v2)`}}, 418 {commands.SqlCmd{}, []string{"-q", `alter table child 419 add foreign key (v1, v2) references parent(v1, v2)`}}, 420 }, 421 fks: []doltdb.ForeignKey{ 422 { 423 Name: "n4qun7ju", 424 TableName: "child", 425 TableIndex: "v1v2_idx", 426 TableColumns: []uint64{1215, 8734}, 427 ReferencedTableName: "parent", 428 ReferencedTableIndex: "v1v2_idx", 429 ReferencedTableColumns: []uint64{6269, 7947}, 430 }, 431 }, 432 }, 433 { 434 name: "create multiple unnamed foreign keys", 435 setup: []testCommand{ 436 {commands.SqlCmd{}, []string{"-q", `alter table child add index v1_idx (v1)`}}, 437 {commands.SqlCmd{}, []string{"-q", `alter table child add index v2_idx (v2)`}}, 438 {commands.SqlCmd{}, []string{"-q", `alter table child 439 add foreign key (v1) references parent(v1)`}}, 440 {commands.SqlCmd{}, []string{"-q", `alter table child 441 add foreign key (v2) references parent(v2)`}}, 442 }, 443 fks: []doltdb.ForeignKey{ 444 { 445 Name: "19eof0mu", 446 TableName: "child", 447 TableIndex: "v1_idx", 448 TableColumns: []uint64{1215}, 449 ReferencedTableName: "parent", 450 ReferencedTableIndex: "v1_idx", 451 ReferencedTableColumns: []uint64{6269}, 452 }, 453 { 454 Name: "p79c8qtq", 455 TableName: "child", 456 TableIndex: "v2_idx", 457 TableColumns: []uint64{8734}, 458 ReferencedTableName: "parent", 459 ReferencedTableIndex: "v2_idx", 460 ReferencedTableColumns: []uint64{7947}, 461 }, 462 }, 463 }, 464 { 465 name: "create foreign key with pre-existing data", 466 setup: []testCommand{ 467 {commands.SqlCmd{}, []string{"-q", `insert into parent (id,v1,v2) values 468 (1,1,1), 469 (2,2,2);`}}, 470 {commands.SqlCmd{}, []string{"-q", `insert into child (id,v1,v2) values 471 (1,1,1), 472 (2,2,2), 473 (3,NULL,3);`}}, 474 {commands.SqlCmd{}, []string{"-q", `alter table child add index v1_idx (v1)`}}, 475 {commands.SqlCmd{}, []string{"-q", `alter table child 476 add constraint fk1 foreign key (v1) references parent(v1)`}}, 477 }, 478 fks: []doltdb.ForeignKey{ 479 { 480 Name: "fk1", 481 TableName: "child", 482 TableIndex: "v1_idx", 483 TableColumns: []uint64{1215}, 484 ReferencedTableName: "parent", 485 ReferencedTableIndex: "v1_idx", 486 ReferencedTableColumns: []uint64{6269}, 487 }, 488 }, 489 }, 490 { 491 name: "create multi-col foreign key with pre-existing data", 492 setup: []testCommand{ 493 {commands.SqlCmd{}, []string{"-q", `insert into parent (id,v1,v2) values 494 (1,1,1), 495 (2,2,NULL), 496 (3,NULL,3), 497 (4,NULL,NULL);`}}, 498 {commands.SqlCmd{}, []string{"-q", `insert into child (id,v1,v2) values 499 (1,1,1), 500 (2,2,NULL), 501 (3,NULL,3);`}}, 502 {commands.SqlCmd{}, []string{"-q", `alter table parent add index v1v2 (v1,v2)`}}, 503 {commands.SqlCmd{}, []string{"-q", `alter table child add index v1v2 (v1,v2)`}}, 504 {commands.SqlCmd{}, []string{"-q", `alter table child 505 add constraint fk1 foreign key (v1,v2) references parent(v1,v2)`}}, 506 }, 507 fks: []doltdb.ForeignKey{ 508 { 509 Name: "fk1", 510 TableName: "child", 511 TableIndex: "v1v2", 512 TableColumns: []uint64{1215, 8734}, 513 ReferencedTableName: "parent", 514 ReferencedTableIndex: "v1v2", 515 ReferencedTableColumns: []uint64{6269, 7947}, 516 }, 517 }, 518 }, 519 }