github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/alter_table (about) 1 # LogicTest: !3node-tenant 2 3 statement ok 4 CREATE TABLE other (b INT PRIMARY KEY) 5 6 statement ok 7 INSERT INTO other VALUES (9) 8 9 statement ok 10 CREATE TABLE t (a INT PRIMARY KEY CHECK(a > 0), f INT REFERENCES other, INDEX (f)) 11 12 statement ok 13 INSERT INTO t VALUES (1, 9) 14 15 statement error at or near "\*": syntax error 16 ALTER TABLE t RENAME TO t.* 17 18 statement ok 19 ALTER TABLE t ADD b INT 20 21 query TTBTTTB colnames 22 SHOW COLUMNS FROM t 23 ---- 24 column_name data_type is_nullable column_default generation_expression indices is_hidden 25 a INT8 false NULL · {primary,t_f_idx} false 26 f INT8 true NULL · {t_f_idx} false 27 b INT8 true NULL · {} false 28 29 statement ok 30 ALTER TABLE t ADD CONSTRAINT foo UNIQUE (b) 31 32 query TTTTRT 33 SELECT job_type, description, user_name, status, fraction_completed, error 34 FROM crdb_internal.jobs 35 WHERE job_type = 'SCHEMA CHANGE' 36 ORDER BY created DESC 37 LIMIT 1 38 ---- 39 SCHEMA CHANGE ALTER TABLE test.public.t ADD CONSTRAINT foo UNIQUE (b) root succeeded 1 · 40 41 statement error duplicate constraint name: "foo" 42 ALTER TABLE t ADD CONSTRAINT foo UNIQUE (b) 43 44 statement error pq: multiple primary keys for table "t" are not allowed 45 ALTER TABLE t ADD CONSTRAINT bar PRIMARY KEY (b) 46 47 query TTBITTBB colnames 48 SHOW INDEXES FROM t 49 ---- 50 table_name index_name non_unique seq_in_index column_name direction storing implicit 51 t primary false 1 a ASC false false 52 t t_f_idx true 1 f ASC false false 53 t t_f_idx true 2 a ASC false true 54 t foo false 1 b ASC false false 55 t foo false 2 a ASC false true 56 57 query III 58 SELECT * FROM t 59 ---- 60 1 9 NULL 61 62 statement ok 63 ALTER TABLE t ADD c INT 64 65 statement ok 66 INSERT INTO t VALUES (2, 9, 1, 1), (3, 9, 2, 1) 67 68 statement error pgcode 23505 violates unique constraint "bar" 69 ALTER TABLE t ADD CONSTRAINT bar UNIQUE (c) 70 71 # Test that rollback was successful 72 query TTTTTR 73 SELECT job_type, regexp_replace(description, 'JOB \d+', 'JOB ...'), user_name, status, running_status, fraction_completed::decimal(10,2) 74 FROM crdb_internal.jobs 75 WHERE job_type = 'SCHEMA CHANGE' OR job_type = 'SCHEMA CHANGE GC' 76 ORDER BY created DESC 77 LIMIT 2 78 ---- 79 SCHEMA CHANGE GC GC for ROLLBACK of ALTER TABLE test.public.t ADD CONSTRAINT bar UNIQUE (c) root running NULL 0.00 80 SCHEMA CHANGE ALTER TABLE test.public.t ADD CONSTRAINT bar UNIQUE (c) root failed NULL 0.00 81 82 query IIII colnames,rowsort 83 SELECT * FROM t 84 ---- 85 a f b c 86 1 9 NULL NULL 87 2 9 1 1 88 3 9 2 1 89 90 query TTTTB colnames 91 SHOW CONSTRAINTS FROM t 92 ---- 93 table_name constraint_name constraint_type details validated 94 t check_a CHECK CHECK ((a > 0:::INT8)) true 95 t fk_f_ref_other FOREIGN KEY FOREIGN KEY (f) REFERENCES other(b) true 96 t foo UNIQUE UNIQUE (b ASC) true 97 t primary PRIMARY KEY PRIMARY KEY (a ASC) true 98 99 statement error CHECK 100 INSERT INTO t (a, f) VALUES (-2, 9) 101 102 statement ok 103 ALTER TABLE t DROP CONSTRAINT check_a 104 105 statement ok 106 INSERT INTO t (a, f) VALUES (-2, 9) 107 108 statement error validation of CHECK "a > 0:::INT8" failed on row: a=-2, f=9, b=NULL, c=NULL 109 ALTER TABLE t ADD CONSTRAINT check_a CHECK (a > 0) 110 111 statement ok 112 DELETE FROM t WHERE a = -2 113 114 statement ok 115 ALTER TABLE t ADD CONSTRAINT check_a CHECK (a > 0) 116 117 statement error CHECK 118 INSERT INTO t (a) VALUES (-3) 119 120 query TTTTB 121 SHOW CONSTRAINTS FROM t 122 ---- 123 t check_a CHECK CHECK ((a > 0:::INT8)) true 124 t fk_f_ref_other FOREIGN KEY FOREIGN KEY (f) REFERENCES other(b) true 125 t foo UNIQUE UNIQUE (b ASC) true 126 t primary PRIMARY KEY PRIMARY KEY (a ASC) true 127 128 statement error duplicate constraint name 129 ALTER TABLE t ADD CONSTRAINT check_a CHECK (a > 0) 130 131 statement error duplicate constraint name 132 ALTER TABLE t ADD CONSTRAINT fk_f_ref_other FOREIGN KEY (a) REFERENCES other (b) 133 134 # added constraints with generated names avoid name collisions. 135 statement ok 136 ALTER TABLE t ADD CHECK (a > 0) 137 138 query TTTTB 139 SHOW CONSTRAINTS FROM t 140 ---- 141 t check_a CHECK CHECK ((a > 0:::INT8)) true 142 t check_a1 CHECK CHECK ((a > 0:::INT8)) true 143 t fk_f_ref_other FOREIGN KEY FOREIGN KEY (f) REFERENCES other(b) true 144 t foo UNIQUE UNIQUE (b ASC) true 145 t primary PRIMARY KEY PRIMARY KEY (a ASC) true 146 147 statement error constraint "typo" does not exist 148 ALTER TABLE t VALIDATE CONSTRAINT typo 149 150 # TODO(erik): re-enable test when unvalidated checks can be added 151 #statement error validation of CHECK "a > 0" failed on row: a=-2, f=9, b=NULL, c=NULL 152 #ALTER TABLE t VALIDATE CONSTRAINT check_a 153 154 #statement ok 155 #DELETE FROM t WHERE a = -2 156 157 statement ok 158 ALTER TABLE t VALIDATE CONSTRAINT check_a 159 160 query TTTTB 161 SHOW CONSTRAINTS FROM t 162 ---- 163 t check_a CHECK CHECK ((a > 0:::INT8)) true 164 t check_a1 CHECK CHECK ((a > 0:::INT8)) true 165 t fk_f_ref_other FOREIGN KEY FOREIGN KEY (f) REFERENCES other(b) true 166 t foo UNIQUE UNIQUE (b ASC) true 167 t primary PRIMARY KEY PRIMARY KEY (a ASC) true 168 169 statement ok 170 ALTER TABLE t DROP CONSTRAINT check_a, DROP CONSTRAINT check_a1 171 172 statement error pgcode 42703 column "d" does not exist 173 ALTER TABLE t DROP d 174 175 statement ok 176 ALTER TABLE t DROP IF EXISTS d 177 178 statement error column "a" is referenced by the primary key 179 ALTER TABLE t DROP a 180 181 statement error constraint "bar" does not exist 182 ALTER TABLE t DROP CONSTRAINT bar 183 184 statement ok 185 ALTER TABLE t DROP CONSTRAINT IF EXISTS bar 186 187 statement error cannot drop UNIQUE constraint \"foo\" using ALTER TABLE DROP CONSTRAINT, use DROP INDEX CASCADE instead 188 ALTER TABLE t DROP CONSTRAINT foo 189 190 statement ok 191 DROP INDEX foo CASCADE 192 193 query TTTTTRT 194 SELECT job_type, description, user_name, status, running_status, fraction_completed, error 195 FROM crdb_internal.jobs 196 WHERE job_type = 'SCHEMA CHANGE' OR job_type = 'SCHEMA CHANGE GC' 197 ORDER BY created DESC 198 LIMIT 2 199 ---- 200 SCHEMA CHANGE GC GC for DROP INDEX test.public.t@foo CASCADE root running NULL 0 · 201 SCHEMA CHANGE DROP INDEX test.public.t@foo CASCADE root succeeded NULL 1 · 202 203 query TTBITTBB colnames 204 SHOW INDEXES FROM t 205 ---- 206 table_name index_name non_unique seq_in_index column_name direction storing implicit 207 t primary false 1 a ASC false false 208 t t_f_idx true 1 f ASC false false 209 t t_f_idx true 2 a ASC false true 210 211 statement ok 212 ALTER TABLE t DROP b, DROP c 213 214 query II rowsort 215 SELECT * FROM t 216 ---- 217 1 9 218 2 9 219 3 9 220 221 statement ok 222 ALTER TABLE t ADD d INT UNIQUE 223 224 statement ok 225 INSERT INTO t VALUES (4, 9, 1) 226 227 statement error duplicate key value \(d\)=\(1\) violates unique constraint \"t_d_key\" 228 INSERT INTO t VALUES (5, 9, 1) 229 230 # Add a column with no default value 231 statement ok 232 ALTER TABLE t ADD COLUMN x DECIMAL 233 234 # Add a non NULL column with a default value 235 statement ok 236 ALTER TABLE t ADD COLUMN y DECIMAL NOT NULL DEFAULT (DECIMAL '1.3') 237 238 statement error could not parse "1-3" as type decimal 239 ALTER TABLE t ADD COLUMN p DECIMAL NOT NULL DEFAULT (DECIMAL '1-3') 240 241 # Add a non NULL column with no default value 242 statement error pgcode 23502 null value in column \"q\" violates not-null constraint 243 ALTER TABLE t ADD COLUMN q DECIMAL NOT NULL 244 245 statement ok 246 ALTER TABLE t ADD COLUMN z DECIMAL DEFAULT (DECIMAL '1.4') 247 248 statement ok 249 INSERT INTO t VALUES (11, 9, 12, DECIMAL '1.0') 250 251 statement ok 252 INSERT INTO t (a, d) VALUES (13, 14) 253 254 statement ok 255 INSERT INTO t (a, d, y) VALUES (21, 22, DECIMAL '1.0') 256 257 statement ok 258 INSERT INTO t (a, d) VALUES (23, 24) 259 260 statement error foreign key 261 INSERT INTO t VALUES (31, 7, 32) 262 263 statement error in use as a foreign key constraint 264 DROP INDEX t@t_f_idx 265 266 statement ok 267 ALTER TABLE t DROP CONSTRAINT fk_f_ref_other 268 269 statement ok 270 INSERT INTO t VALUES (31, 7, 32) 271 272 statement ok 273 INSERT INTO t (a, d, x, y, z) VALUES (33, 34, DECIMAL '2.0', DECIMAL '2.1', DECIMAL '2.2') 274 275 statement ok 276 DROP INDEX t@t_f_idx 277 278 query TTTTTRT 279 SELECT job_type, description, user_name, status, running_status, fraction_completed, error 280 FROM crdb_internal.jobs 281 WHERE job_type = 'SCHEMA CHANGE' OR job_type = 'SCHEMA CHANGE GC' 282 ORDER BY created DESC 283 LIMIT 2 284 ---- 285 SCHEMA CHANGE GC GC for DROP INDEX test.public.t@t_f_idx root running NULL 0 · 286 SCHEMA CHANGE DROP INDEX test.public.t@t_f_idx root succeeded NULL 1 · 287 288 statement ok 289 ALTER TABLE t DROP COLUMN f 290 291 query IITTT colnames,rowsort 292 SELECT * FROM t 293 ---- 294 a d x y z 295 1 NULL NULL 1.3 1.4 296 2 NULL NULL 1.3 1.4 297 3 NULL NULL 1.3 1.4 298 4 1 NULL 1.3 1.4 299 11 12 1.0 1.3 1.4 300 13 14 NULL 1.3 1.4 301 21 22 NULL 1.0 1.4 302 23 24 NULL 1.3 1.4 303 31 32 NULL 1.3 1.4 304 33 34 2.0 2.1 2.2 305 306 statement ok 307 ALTER TABLE t DROP COLUMN d 308 309 statement ok 310 ALTER TABLE t ADD COLUMN e INT; ALTER TABLE t ADD COLUMN d INT 311 312 statement ok 313 CREATE VIEW v AS SELECT x, y FROM t WHERE e > 5 314 315 statement error cannot drop column "x" because view "v" depends on it 316 ALTER TABLE t DROP COLUMN x 317 318 statement error cannot drop column "y" because view "v" depends on it 319 ALTER TABLE t DROP COLUMN y 320 321 statement error cannot drop column "e" because view "v" depends on it 322 ALTER TABLE t DROP COLUMN e 323 324 # TODO(knz): this statement should succeed after #17269 is fixed. 325 statement error cannot drop column "d" because view "v" depends on it 326 ALTER TABLE t DROP COLUMN d 327 328 # TODO(knz): remove the following once the test above succeeds. 329 statement ok 330 ALTER TABLE t DROP COLUMN d CASCADE 331 332 statement ok 333 ALTER TABLE t DROP COLUMN e CASCADE 334 335 statement ok 336 ALTER TABLE t ADD COLUMN e INT 337 338 statement ok 339 CREATE VIEW v AS SELECT x, y FROM t WHERE e > 5 340 341 statement ok 342 ALTER TABLE t DROP COLUMN IF EXISTS q 343 344 statement error cannot drop column "e" because view "v" depends on it 345 ALTER TABLE t DROP COLUMN IF EXISTS e 346 347 statement ok 348 ALTER TABLE t DROP COLUMN IF EXISTS e CASCADE 349 350 statement ok 351 ALTER TABLE t ADD COLUMN g INT UNIQUE 352 353 statement ok 354 CREATE TABLE o (gf INT REFERENCES t (g), h INT, i INT, INDEX ii (i) STORING(h)) 355 356 statement error "t_g_key" is referenced by foreign key from table "o" 357 ALTER TABLE t DROP COLUMN g 358 359 statement ok 360 ALTER TABLE t DROP COLUMN g CASCADE 361 362 statement error column "h" is referenced by existing index "ii" 363 ALTER TABLE o DROP COLUMN h 364 365 statement ok 366 ALTER TABLE o DROP COLUMN h CASCADE 367 368 statement ok 369 ALTER TABLE t ADD f INT CHECK (f > 1) 370 371 statement ok 372 ALTER TABLE t ADD g INT DEFAULT 1 CHECK (g > 0) 373 374 statement ok 375 ALTER TABLE t ADD h INT CHECK (h > 0) CHECK (h < 10) UNIQUE 376 377 statement error pq: validation of CHECK "i < 0:::INT8" failed on row:.* i=1 378 ALTER TABLE t ADD i INT DEFAULT 1 CHECK (i < 0) 379 380 statement error pq: validation of CHECK "i < g" failed on row:.* g=1.* i=1 381 ALTER TABLE t ADD i INT DEFAULT 1 CHECK (i < g) 382 383 statement error pq: validation of CHECK "i > 0:::INT8" failed on row:.* g=1.* i=0 384 ALTER TABLE t ADD i INT AS (g - 1) STORED CHECK (i > 0) 385 386 query TTTTB 387 SHOW CONSTRAINTS FROM t 388 ---- 389 t check_f CHECK CHECK ((f > 1:::INT8)) true 390 t check_g CHECK CHECK ((g > 0:::INT8)) true 391 t check_h CHECK CHECK ((h > 0:::INT8)) true 392 t check_h1 CHECK CHECK ((h < 10:::INT8)) true 393 t primary PRIMARY KEY PRIMARY KEY (a ASC) true 394 t t_h_key UNIQUE UNIQUE (h ASC) true 395 396 statement ok 397 DROP TABLE t 398 399 # Test that more than one column with constraints can be added in the same 400 # statement. The constraints added here are on columns that are new and both 401 # columns and constraints run through the schema change process together. 402 403 statement ok 404 CREATE TABLE t (a INT PRIMARY KEY) 405 406 statement ok 407 INSERT INTO t VALUES (1) 408 409 # Check references column added in same statement 410 statement ok 411 ALTER TABLE t ADD b INT DEFAULT 1, ADD c INT DEFAULT 2 CHECK (c > b) 412 413 statement ok 414 ALTER TABLE t ADD d INT UNIQUE, ADD e INT UNIQUE, ADD f INT 415 416 # Check references column added in same statement 417 statement error pq: validation of CHECK "g = h" failed on row:.* g=3.* h=2 418 ALTER TABLE t ADD g INT DEFAULT 3, ADD h INT DEFAULT 2 CHECK (g = h) 419 420 # Multiple unique columns can be added, followed by other commands (#35011) 421 statement ok 422 ALTER TABLE t ADD COLUMN u INT UNIQUE, ADD COLUMN v INT UNIQUE, ADD CONSTRAINT ck CHECK (a > 0); 423 424 query TTTTB 425 SHOW CONSTRAINTS FROM t 426 ---- 427 t check_c_b CHECK CHECK ((c > b)) true 428 t ck CHECK CHECK ((a > 0:::INT8)) true 429 t primary PRIMARY KEY PRIMARY KEY (a ASC) true 430 t t_d_key UNIQUE UNIQUE (d ASC) true 431 t t_e_key UNIQUE UNIQUE (e ASC) true 432 t t_u_key UNIQUE UNIQUE (u ASC) true 433 t t_v_key UNIQUE UNIQUE (v ASC) true 434 435 statement ok 436 DROP TABLE t 437 438 # Subsequent operations succeed because the table is empty 439 statement ok 440 CREATE TABLE tt (a INT PRIMARY KEY) 441 442 statement ok 443 ALTER TABLE tt ADD COLUMN q DECIMAL NOT NULL 444 445 statement ok 446 ALTER table tt ADD COLUMN r DECIMAL 447 448 # Ensure that a UNIQUE NOT NULL COLUMN can be added when there is no data in 449 # the table. 450 statement ok 451 ALTER TABLE tt ADD COLUMN s DECIMAL UNIQUE NOT NULL 452 453 statement ok 454 ALTER TABLE tt ADD t DECIMAL UNIQUE DEFAULT 4.0 455 456 query TTBTTTB colnames 457 SHOW COLUMNS FROM tt 458 ---- 459 column_name data_type is_nullable column_default generation_expression indices is_hidden 460 a INT8 false NULL · {primary,tt_s_key,tt_t_key} false 461 q DECIMAL false NULL · {} false 462 r DECIMAL true NULL · {} false 463 s DECIMAL false NULL · {tt_s_key} false 464 t DECIMAL true 4.0:::DECIMAL · {tt_t_key} false 465 466 # Default values can be added and changed after table creation. 467 statement ok 468 CREATE TABLE add_default (a int primary key, b int not null) 469 470 statement error null value in column "b" violates not-null constraint 471 INSERT INTO add_default (a) VALUES (1) 472 473 statement ok 474 ALTER TABLE add_default ALTER COLUMN b SET DEFAULT 42 475 476 # query TTBTTTB colnames 477 # SHOW COLUMNS FROM add_default 478 # ---- 479 # column_name data_type is_nullable column_default generation_expression indices is_hidden 480 481 statement ok 482 INSERT INTO add_default (a) VALUES (2) 483 484 statement ok 485 ALTER TABLE add_default ALTER COLUMN b SET DEFAULT 10 486 487 statement ok 488 INSERT INTO add_default (a) VALUES (3) 489 490 statement error could not parse "foo" as type int 491 ALTER TABLE add_default ALTER COLUMN b SET DEFAULT 'foo' 492 493 statement error variable sub-expressions are not allowed in DEFAULT 494 ALTER TABLE add_default ALTER COLUMN b SET DEFAULT c 495 496 statement error variable sub-expressions are not allowed in DEFAULT 497 ALTER TABLE add_default ALTER COLUMN b SET DEFAULT (SELECT 1) 498 499 statement ok 500 ALTER TABLE add_default ALTER COLUMN b DROP DEFAULT 501 502 statement error null value in column "b" violates not-null constraint 503 INSERT INTO add_default (a) VALUES (4) 504 505 statement ok 506 ALTER TABLE add_default ALTER COLUMN b SET DEFAULT NULL 507 508 statement error null value in column "b" violates not-null constraint 509 INSERT INTO add_default (a) VALUES (4) 510 511 # Each row gets the default value from the time it was inserted. 512 query II rowsort 513 SELECT * FROM add_default 514 ---- 515 2 42 516 3 10 517 518 statement ok 519 ALTER TABLE add_default ALTER b DROP NOT NULL 520 521 statement ok 522 INSERT INTO add_default (a) VALUES (5) 523 524 query II 525 SELECT * from add_default WHERE a=5 526 ---- 527 5 NULL 528 529 # Add a column with a default current_timestamp() 530 statement ok 531 ALTER TABLE add_default ADD COLUMN c TIMESTAMP DEFAULT current_timestamp() 532 533 query II rowsort 534 SELECT a,b FROM add_default WHERE current_timestamp > c AND current_timestamp() - c < interval '10s' 535 ---- 536 2 42 537 3 10 538 5 NULL 539 540 # Add a column with a default transaction_timestamp() 541 statement ok 542 ALTER TABLE add_default ADD COLUMN d TIMESTAMP DEFAULT transaction_timestamp() 543 544 query II rowsort 545 SELECT a,b FROM add_default WHERE d > c AND d - c < interval '10s' 546 ---- 547 2 42 548 3 10 549 5 NULL 550 551 # Add a column with a default statement_timestamp() 552 statement ok 553 ALTER TABLE add_default ADD COLUMN e TIMESTAMP DEFAULT statement_timestamp() 554 555 query II rowsort 556 SELECT a,b FROM add_default WHERE e > d AND e - d < interval '10s' 557 ---- 558 2 42 559 3 10 560 5 NULL 561 562 # Add a column with a null-default statement_timestamp() 563 statement ok 564 ALTER TABLE add_default ADD COLUMN f TIMESTAMP DEFAULT NULL 565 566 query IIS rowsort 567 SELECT a,b,f FROM add_default 568 ---- 569 2 42 NULL 570 3 10 NULL 571 5 NULL NULL 572 573 # Adding a unique column to an existing table with data with a default value 574 # is illegal 575 statement error pgcode 23505 violates unique constraint \"add_default_g_key\" 576 ALTER TABLE add_default ADD g INT UNIQUE DEFAULT 1 577 578 # various default evaluation errors 579 580 statement ok 581 CREATE SEQUENCE initial_seq 582 583 statement error pgcode 0A000 cannot evaluate scalar expressions containing sequence operations in this context 584 ALTER TABLE add_default ADD g INT DEFAULT nextval('initial_seq') 585 586 statement error pgcode 22C01 cannot evaluate scalar expressions using table lookups in this context 587 ALTER TABLE add_default ADD g OID DEFAULT 'foo'::regclass::oid 588 589 statement error cannot access virtual schema in anonymous database 590 ALTER TABLE add_default ADD g INT DEFAULT 'foo'::regtype::INT 591 592 subtest 26422 593 594 statement ok 595 BEGIN 596 597 statement ok 598 ALTER TABLE add_default ADD fee FLOAT NOT NULL DEFAULT 2.99 599 600 statement ok 601 ALTER TABLE add_default ALTER COLUMN fee DROP DEFAULT 602 603 statement error pgcode XXA00 null value in column "fee" violates not-null constraint 604 COMMIT 605 606 query T rowsort 607 SELECT feature_name FROM crdb_internal.feature_usage WHERE feature_name = 'sql.schema.change_in_explicit_txn' 608 ---- 609 sql.schema.change_in_explicit_txn 610 611 statement error pgcode 42703 column "fee" does not exist 612 ALTER TABLE add_default DROP fee 613 614 # Multiple columns can be added at once with heterogeneous DEFAULT usage 615 statement ok 616 CREATE TABLE d (a INT PRIMARY KEY) 617 618 statement ok 619 INSERT INTO d VALUES (1), (2) 620 621 statement ok 622 ALTER TABLE d ADD COLUMN c INT, ADD COLUMN b INT DEFAULT 7 623 624 statement ok 625 INSERT INTO d (a, c) VALUES (3, 4) 626 627 query III rowsort 628 SELECT * FROM d 629 ---- 630 1 NULL 7 631 2 NULL 7 632 3 4 7 633 634 # Test privileges. 635 636 statement ok 637 CREATE TABLE privs (a INT PRIMARY KEY, b INT) 638 639 statement ok 640 INSERT INTO privs VALUES (1) 641 642 user testuser 643 644 query T 645 SHOW DATABASE 646 ---- 647 test 648 649 statement error user testuser does not have CREATE privilege on relation privs 650 ALTER TABLE privs ADD c INT 651 652 statement error user testuser does not have CREATE privilege on relation privs 653 ALTER TABLE privs ADD CONSTRAINT foo UNIQUE (b) 654 655 user root 656 657 query TTBTTTB colnames 658 SHOW COLUMNS FROM privs 659 ---- 660 column_name data_type is_nullable column_default generation_expression indices is_hidden 661 a INT8 false NULL · {primary} false 662 b INT8 true NULL · {} false 663 664 statement ok 665 GRANT CREATE ON privs TO testuser 666 667 user testuser 668 669 statement ok 670 ALTER TABLE privs ADD c INT 671 672 statement ok 673 ALTER TABLE privs ADD CONSTRAINT foo UNIQUE (b) 674 675 query TTBTTTB colnames 676 SHOW COLUMNS FROM privs 677 ---- 678 column_name data_type is_nullable column_default generation_expression indices is_hidden 679 a INT8 false NULL · {primary,foo} false 680 b INT8 true NULL · {foo} false 681 c INT8 true NULL · {} false 682 683 statement error pgcode 42P01 relation "nonexistent" does not exist 684 ALTER TABLE nonexistent SPLIT AT VALUES (42) 685 686 statement error pgcode 42P01 relation "nonexistent" does not exist 687 ALTER INDEX nonexistent@noindex SPLIT AT VALUES (42) 688 689 statement error pgcode 42P01 relation "nonexistent" does not exist 690 ALTER TABLE nonexistent UNSPLIT AT VALUES (42) 691 692 statement error pgcode 42P01 relation "nonexistent" does not exist 693 ALTER INDEX nonexistent@noindex UNSPLIT AT VALUES (42) 694 695 statement error pgcode 42P01 relation "nonexistent" does not exist 696 ALTER TABLE nonexistent UNSPLIT ALL 697 698 statement error pgcode 42P01 relation "nonexistent" does not exist 699 ALTER INDEX nonexistent@noindex UNSPLIT ALL 700 701 user root 702 703 statement ok 704 CREATE VIEW privsview AS SELECT a,b,c FROM privs 705 706 statement error pgcode 42809 "privsview" is not a table 707 ALTER TABLE privsview ADD d INT 708 709 statement error pgcode 42809 "privsview" is not a table 710 ALTER TABLE privsview SPLIT AT VALUES (42) 711 712 statement error pgcode 42809 "privsview" is not a table 713 ALTER TABLE privsview UNSPLIT AT VALUES (42) 714 715 statement error pgcode 42809 "privsview" is not a table 716 ALTER TABLE privsview UNSPLIT ALL 717 718 # Verify that impure defaults are evaluated separately on each row 719 # (#14352) 720 statement ok 721 CREATE TABLE impure (x INT); INSERT INTO impure(x) VALUES (1), (2), (3); 722 723 statement ok 724 ALTER TABLE impure ADD COLUMN a INT DEFAULT unique_rowid(); 725 726 query I 727 SELECT count(distinct a) FROM impure 728 ---- 729 3 730 731 # No orphaned schema change jobs. 732 query I 733 SELECT count(*) FROM crdb_internal.jobs 734 WHERE job_type = 'SCHEMA CHANGE' AND status = 'pending' OR status = 'started' 735 ---- 736 0 737 738 # Verify that ALTER TABLE statements are rolled back properly when a DEFAULT expression returns 739 # an error. 740 741 statement ok 742 CREATE TABLE default_err_test (foo text) 743 744 statement ok 745 INSERT INTO default_err_test VALUES ('foo'), ('bar'), ('baz') 746 747 statement error some_msg 748 ALTER TABLE default_err_test ADD COLUMN id int DEFAULT crdb_internal.force_error('foo', 'some_msg') 749 750 query T 751 SELECT * from default_err_test ORDER BY foo 752 ---- 753 bar 754 baz 755 foo 756 757 # Create a table with a computed column that we'll de-compute 758 statement ok 759 CREATE TABLE decomputed_column (a INT PRIMARY KEY, b INT AS ( a + 1 ) STORED, FAMILY "primary" (a, b)) 760 761 statement ok 762 INSERT INTO decomputed_column VALUES (1), (2) 763 764 statement error cannot write directly to computed column 765 INSERT INTO decomputed_column VALUES (3, NULL), (4, 99) 766 767 statement ok 768 ALTER TABLE decomputed_column ALTER COLUMN b DROP STORED 769 770 statement error pq: column "a" is not a computed column 771 ALTER TABLE decomputed_column ALTER COLUMN a DROP STORED 772 773 statement error pq: column "b" is not a computed column 774 ALTER TABLE decomputed_column ALTER COLUMN b DROP STORED 775 776 # Verify that the computation is dropped and that we can mutate the column 777 statement ok 778 INSERT INTO decomputed_column VALUES (3, NULL), (4, 99) 779 780 query II 781 select a, b from decomputed_column order by a 782 ---- 783 1 2 784 2 3 785 3 NULL 786 4 99 787 788 query TT 789 show create table decomputed_column 790 ---- 791 decomputed_column CREATE TABLE decomputed_column ( 792 a INT8 NOT NULL, 793 b INT8 NULL, 794 CONSTRAINT "primary" PRIMARY KEY (a ASC), 795 FAMILY "primary" (a, b) 796 ) 797 798 # Test for https://github.com/cockroachdb/cockroach/issues/26483 799 # We try to create a unique column on an un-indexable type. 800 statement ok 801 CREATE TABLE b26483() 802 803 # Verify that auditing can be enabled by root, and cannot be disabled by non-root. 804 805 statement ok 806 CREATE TABLE audit(x INT); ALTER TABLE audit EXPERIMENTAL_AUDIT SET READ WRITE; 807 808 query T 809 SELECT feature_name FROM crdb_internal.feature_usage WHERE feature_name = 'sql.schema.set_audit_mode.read_write' 810 ---- 811 sql.schema.set_audit_mode.read_write 812 813 # The user must be able to issue ALTER for this test to be meaningful. 814 statement ok 815 GRANT CREATE ON audit TO testuser 816 817 user testuser 818 819 # Check the user can indeed change the table 820 statement ok 821 ALTER TABLE audit ADD COLUMN y INT 822 823 # But not the audit settings. 824 statement error change auditing settings on a table 825 ALTER TABLE audit EXPERIMENTAL_AUDIT SET OFF 826 827 user root 828 829 statement ok 830 ALTER TABLE audit EXPERIMENTAL_AUDIT SET OFF 831 832 query T 833 SELECT feature_name FROM crdb_internal.feature_usage WHERE feature_name = 'sql.schema.set_audit_mode.off' 834 ---- 835 sql.schema.set_audit_mode.off 836 837 # Check column backfill in the presence of fks 838 subtest 27402 839 840 statement ok 841 CREATE TABLE users ( 842 id INT NOT NULL, 843 city STRING NOT NULL, 844 name STRING NULL, 845 CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC) 846 ) 847 848 statement ok 849 CREATE TABLE vehicles ( 850 id INT NOT NULL, 851 city STRING NOT NULL, 852 type STRING NULL, 853 owner_id INT NULL, 854 mycol STRING NULL, 855 CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC), 856 INDEX vehicles_auto_index_fk_city_ref_users (city ASC, owner_id ASC) 857 ) 858 859 statement ok 860 CREATE TABLE rides ( 861 id INT NOT NULL, 862 city STRING NOT NULL, 863 vehicle_city STRING NULL, 864 rider_id INT NULL, 865 vehicle_id INT NULL, 866 CONSTRAINT "primary" PRIMARY KEY (city ASC, id ASC), 867 INDEX rides_auto_index_fk_city_ref_users (city ASC, rider_id ASC), 868 INDEX rides_auto_index_fk_vehicle_city_ref_vehicles (vehicle_city ASC, vehicle_id ASC), 869 CONSTRAINT check_vehicle_city_city CHECK (vehicle_city = city) 870 ) 871 872 statement ok 873 ALTER TABLE vehicles ADD CONSTRAINT fk_city_ref_users FOREIGN KEY (city, owner_id) REFERENCES users (city, id) 874 875 statement ok 876 ALTER TABLE rides ADD CONSTRAINT fk_city_ref_users FOREIGN KEY (city, rider_id) REFERENCES users (city, id) 877 878 statement ok 879 ALTER TABLE rides ADD CONSTRAINT fk_vehicle_city_ref_vehicles FOREIGN KEY (vehicle_city, vehicle_id) REFERENCES vehicles (city, id) 880 881 882 statement ok 883 INSERT INTO users VALUES (10, 'lagos', 'chimamanda') 884 885 statement ok 886 INSERT INTO vehicles VALUES (100, 'lagos', 'toyota', 10, 'mycol') 887 888 statement ok 889 INSERT INTO rides VALUES (567, 'lagos', 'lagos', 10, 100) 890 891 statement ok 892 ALTER TABLE vehicles DROP COLUMN mycol; 893 894 # check that adding a reference on a column still being backfilled fails. 895 # fix through #32917 896 897 statement ok 898 CREATE TABLE t32917 (a INT PRIMARY KEY) 899 900 statement ok 901 INSERT INTO t32917 VALUES (1), (2), (3) 902 903 statement ok 904 CREATE TABLE t32917_2 (b INT PRIMARY KEY) 905 906 statement ok 907 INSERT INTO t32917_2 VALUES (1), (2), (3) 908 909 # Test SET NOT NULL 910 statement ok 911 CREATE TABLE t (a INT) 912 913 statement ok 914 INSERT INTO t VALUES (1), (NULL) 915 916 statement error validation of NOT NULL constraint failed: validation of CHECK "a IS NOT NULL" failed 917 ALTER TABLE t ALTER COLUMN a SET NOT NULL 918 919 statement ok 920 DELETE FROM t WHERE a IS NULL 921 922 statement ok 923 ALTER TABLE t ALTER COLUMN a SET NOT NULL 924 925 statement error null value in column "a" violates not-null constraint 926 INSERT INTO t VALUES (NULL) 927 928 query TTTTB 929 SHOW CONSTRAINTS FROM t 930 ---- 931 932 statement ok 933 ALTER TABLE t ALTER COLUMN a DROP NOT NULL 934 935 statement ok 936 INSERT INTO t VALUES (NULL) 937 938 statement ok 939 DROP TABLE t 940 941 # Test interaction of SET NOT NULL with other constraints 942 statement ok 943 CREATE TABLE t (a INT) 944 945 statement ok 946 INSERT INTO t VALUES (1) 947 948 # Check for name collisions with the auto-generated NOT NULL check constraint name 949 statement ok 950 ALTER TABLE t ADD CONSTRAINT a_auto_not_null CHECK (a IS NOT NULL) 951 952 statement ok 953 ALTER TABLE t ADD CONSTRAINT a_auto_not_null1 CHECK (a IS NOT NULL), ALTER COLUMN a SET NOT NULL 954 955 statement error null value in column "a" violates not-null constraint 956 INSERT INTO t VALUES (NULL) 957 958 query TTTTB 959 SHOW CONSTRAINTS FROM t 960 ---- 961 t a_auto_not_null CHECK CHECK ((a IS NOT NULL)) true 962 t a_auto_not_null1 CHECK CHECK ((a IS NOT NULL)) true 963 964 statement ok 965 DROP TABLE t 966 967 # Check for adding constraints NOT VALID 968 statement ok 969 CREATE TABLE t (a int); 970 971 statement ok 972 INSERT INTO t VALUES (10), (15), (17) 973 974 statement error pq: validation of CHECK "a < 16:::INT8" failed on row: a=17 975 ALTER TABLE t ADD CHECK (a < 16) 976 977 statement ok 978 ALTER TABLE t ADD CHECK (a < 100) 979 980 statement ok 981 ALTER TABLE t ADD CHECK (a < 16) NOT VALID 982 983 query TTTTB 984 SHOW CONSTRAINTS FROM t 985 ---- 986 t check_a CHECK CHECK ((a < 100:::INT8)) true 987 t check_a1 CHECK CHECK ((a < 16:::INT8)) false 988 989 query error pq: failed to satisfy CHECK constraint \(a < 16:::INT8\) 990 INSERT INTO t VALUES (20) 991 992 statement error pq: validation of CHECK "a < 16:::INT8" failed on row: a=17 993 ALTER TABLE t VALIDATE CONSTRAINT check_a1 994 995 statement ok 996 DELETE FROM t WHERE a = 17 997 998 statement ok 999 ALTER TABLE t VALIDATE CONSTRAINT check_a1 1000 1001 query TTTTB 1002 SHOW CONSTRAINTS FROM t 1003 ---- 1004 t check_a CHECK CHECK ((a < 100:::INT8)) true 1005 t check_a1 CHECK CHECK ((a < 16:::INT8)) true 1006 1007 subtest regression_42858 1008 1009 statement ok 1010 CREATE TABLE TEST2 (COL1 SERIAL PRIMARY KEY, COL2 INT8) 1011 1012 statement ok 1013 CREATE TABLE TEST1 (COL1 SERIAL PRIMARY KEY, COL2 INT8, COL3 INT8) 1014 1015 statement ok 1016 ALTER TABLE TEST1 ADD CONSTRAINT duplicate_name FOREIGN KEY (COL2) REFERENCES TEST2 (COL1) 1017 1018 statement error pq: duplicate constraint name: "duplicate_name" 1019 ALTER TABLE TEST1 ADD CONSTRAINT duplicate_name FOREIGN KEY (COL3) REFERENCES TEST2 (COL1) 1020 1021 statement ok 1022 DROP TABLE test1; DROP TABLE test2 1023 1024 subtest multiple_set_drop_not_null_inside_txn 1025 1026 statement ok 1027 CREATE TABLE t1(x INT, y INT); 1028 1029 statement ok 1030 BEGIN; 1031 ALTER TABLE t1 ALTER COLUMN x SET NOT NULL; 1032 ALTER TABLE t1 ALTER COLUMN y SET NOT NULL; 1033 COMMIT 1034 1035 statement ok 1036 BEGIN; 1037 ALTER TABLE t1 ALTER COLUMN x DROP NOT NULL; 1038 ALTER TABLE t1 ALTER COLUMN y DROP NOT NULL; 1039 COMMIT 1040 1041 statement ok 1042 DROP TABLE t1 1043 1044 subtest regression_43092 1045 1046 statement ok 1047 CREATE TABLE t43092(x INT PRIMARY KEY) 1048 1049 statement error pgcode 42P16 column "x" is in a primary index 1050 ALTER TABLE t43092 ALTER COLUMN x DROP NOT NULL 1051 1052 statement ok 1053 DROP TABLE t43092 1054 1055 subtest regression_qualification_feature_counts 1056 1057 statement ok 1058 CREATE TABLE telemetry_test (d int); 1059 ALTER TABLE telemetry_test 1060 ADD COLUMN a int DEFAULT 1, 1061 ADD COLUMN b int UNIQUE CHECK(b > 1), 1062 ADD COLUMN c int AS (a + b) STORED 1063 1064 query T rowsort 1065 SELECT feature_name FROM crdb_internal.feature_usage 1066 WHERE feature_name IN ( 1067 'sql.schema.new_column.qualification.computed', 1068 'sql.schema.new_column.qualification.default_expr', 1069 'sql.schema.new_column.qualification.unique' 1070 ) 1071 ---- 1072 sql.schema.new_column.qualification.unique 1073 sql.schema.new_column.qualification.computed 1074 sql.schema.new_column.qualification.default_expr 1075 1076 statement ok 1077 DROP TABLE telemetry_test 1078 1079 # Disable stats injection in explicit transactions. 1080 statement ok 1081 BEGIN 1082 1083 statement ok 1084 CREATE TABLE inject_stats (k CHAR PRIMARY KEY, v TIMESTAMPTZ) 1085 1086 statement error pq: cannot inject statistics in an explicit transaction 1087 ALTER TABLE inject_stats INJECT STATISTICS '[]' 1088 1089 statement ok 1090 ROLLBACK 1091 1092 subtest regression_47141 1093 1094 statement ok 1095 CREATE TABLE regression_47141(a time(3), b bytea) 1096 1097 # Regression for #26045. 1098 statement ok 1099 CREATE TABLE t25045 (x INT, y INT AS (x+1) STORED) 1100 1101 statement error pq: column \"x\" is referenced by computed column \"y\" 1102 ALTER TABLE t25045 DROP COLUMN x 1103 1104 subtest add_col_references 1105 1106 statement ok 1107 DROP TABLE IF EXISTS t1, t2; 1108 CREATE TABLE t1 (x INT PRIMARY KEY); 1109 CREATE TABLE t2 (y INT) 1110 1111 statement ok 1112 ALTER TABLE t2 ADD COLUMN x INT REFERENCES t1 (x) 1113 1114 statement ok 1115 INSERT INTO t1 VALUES (1) 1116 1117 statement error pq: insert on table "t2" violates foreign key constraint "fk_x_ref_t1" 1118 INSERT INTO t2 VALUES (2, 2) 1119 1120 statement ok 1121 INSERT INTO t2 VALUES (1, 1) 1122 1123 # Error out trying to add a column with a foreign key on a non-empty table. 1124 statement error pq: foreign key requires an existing index on columns \("z"\) 1125 ALTER TABLE t2 ADD COLUMN z INT REFERENCES t1 (x) 1126 1127 # Check that the foreign key was indeed added. 1128 query TT 1129 SHOW CREATE t2 1130 ---- 1131 t2 CREATE TABLE t2 ( 1132 y INT8 NULL, 1133 x INT8 NULL, 1134 CONSTRAINT fk_x_ref_t1 FOREIGN KEY (x) REFERENCES t1(x), 1135 INDEX t2_auto_index_fk_x_ref_t1 (x ASC), 1136 FAMILY "primary" (y, rowid, x) 1137 ) 1138 1139 # Test that only one index gets created when adding a column 1140 # with references and unique. 1141 statement ok 1142 CREATE TABLE t3 (y INT) 1143 1144 statement ok 1145 ALTER TABLE t3 ADD COLUMN x INT UNIQUE REFERENCES t1 (x) 1146 1147 query TT 1148 SHOW CREATE t3 1149 ---- 1150 t3 CREATE TABLE t3 ( 1151 y INT8 NULL, 1152 x INT8 NULL, 1153 CONSTRAINT fk_x_ref_t1 FOREIGN KEY (x) REFERENCES t1(x), 1154 UNIQUE INDEX t3_x_key (x ASC), 1155 FAMILY "primary" (y, rowid, x) 1156 ) 1157 1158 # We allowed the foreign key validation code to look into the mutations 1159 # list to validate what columns / indexes can be used for foreign keys. 1160 # Ensure that we still have the correct restrictions. 1161 statement ok 1162 DROP TABLE t1, t2 CASCADE; 1163 CREATE TABLE t1 (x INT PRIMARY KEY); 1164 CREATE TABLE t2 (x INT, y INT, INDEX i (x)) 1165 1166 statement error pq: column \"x\" does not exist 1167 BEGIN; 1168 ALTER TABLE t2 DROP COLUMN x; 1169 ALTER TABLE t2 ADD FOREIGN KEY (x) REFERENCES t1(x); 1170 1171 statement ok 1172 ROLLBACK 1173 1174 statement ok 1175 INSERT INTO t2 VALUES (1, 2) 1176 1177 statement error pq: foreign key requires an existing index on columns \("x"\) 1178 BEGIN; 1179 DROP INDEX t2@i; 1180 ALTER TABLE t2 ADD FOREIGN KEY (x) REFERENCES t1(x) 1181 1182 statement ok 1183 ROLLBACK 1184 1185 # Test using ADD COL REFERENCES in a self referencing constraint. 1186 statement ok 1187 DROP TABLE t1 CASCADE; 1188 CREATE TABLE t1 (x INT PRIMARY KEY); 1189 ALTER TABLE t1 ADD COLUMN x2 INT REFERENCES t1 (x) 1190 1191 query TT 1192 SHOW CREATE t1 1193 ---- 1194 t1 CREATE TABLE t1 ( 1195 x INT8 NOT NULL, 1196 x2 INT8 NULL, 1197 CONSTRAINT "primary" PRIMARY KEY (x ASC), 1198 CONSTRAINT fk_x2_ref_t1 FOREIGN KEY (x2) REFERENCES t1(x), 1199 INDEX t1_auto_index_fk_x2_ref_t1 (x2 ASC), 1200 FAMILY "primary" (x, x2) 1201 ) 1202 1203 statement error pq: insert on table "t1" violates foreign key constraint "fk_x2_ref_t1" 1204 INSERT INTO t1 VALUES (1, 2) 1205 1206 # Test ADD COL REFERENCES on a new table in the same txn. 1207 statement ok 1208 DROP TABLE t1, t2 CASCADE 1209 1210 statement ok 1211 BEGIN; 1212 CREATE TABLE t1 (x INT PRIMARY KEY); 1213 CREATE TABLE t2 (y INT); 1214 ALTER TABLE t2 ADD COLUMN x INT REFERENCES t1 (x); 1215 COMMIT 1216 1217 query TT 1218 SHOW CREATE t2 1219 ---- 1220 t2 CREATE TABLE t2 ( 1221 y INT8 NULL, 1222 x INT8 NULL, 1223 CONSTRAINT fk_x_ref_t1 FOREIGN KEY (x) REFERENCES t1(x), 1224 INDEX t2_auto_index_fk_x_ref_t1 (x ASC), 1225 FAMILY "primary" (y, rowid, x) 1226 ) 1227 1228 # Test that we can also add a column and then an FK in the same txn. 1229 statement ok 1230 DROP TABLE t1, t2 CASCADE 1231 1232 statement ok 1233 BEGIN; 1234 CREATE TABLE t1 (x INT PRIMARY KEY); 1235 CREATE TABLE t2 (y INT); 1236 ALTER TABLE t2 ADD COLUMN x INT; 1237 ALTER TABLE t2 ADD FOREIGN KEY (x) REFERENCES t1 (x); 1238 COMMIT 1239 1240 query TT 1241 SHOW CREATE t2 1242 ---- 1243 t2 CREATE TABLE t2 ( 1244 y INT8 NULL, 1245 x INT8 NULL, 1246 CONSTRAINT fk_x_ref_t1 FOREIGN KEY (x) REFERENCES t1(x), 1247 INDEX t2_auto_index_fk_x_ref_t1 (x ASC), 1248 FAMILY "primary" (y, rowid, x) 1249 ) 1250 1251 # Test that we can add a column and an index to an FK in the same txn. 1252 statement ok 1253 DROP TABLE t1, t2 CASCADE 1254 1255 statement ok 1256 BEGIN; 1257 CREATE TABLE t1 (x INT PRIMARY KEY); 1258 INSERT INTO t1 VALUES (1); 1259 CREATE TABLE t2 (y INT); 1260 INSERT INTO t2 VALUES (2); 1261 ALTER TABLE t2 ADD COLUMN x INT; 1262 CREATE INDEX ON t2 (x); 1263 ALTER TABLE t2 ADD FOREIGN KEY (x) REFERENCES t1 (x); 1264 COMMIT 1265 1266 query TT 1267 SHOW CREATE t2 1268 ---- 1269 t2 CREATE TABLE t2 ( 1270 y INT8 NULL, 1271 x INT8 NULL, 1272 CONSTRAINT fk_x_ref_t1 FOREIGN KEY (x) REFERENCES t1(x), 1273 INDEX t2_x_idx (x ASC), 1274 FAMILY "primary" (y, rowid, x) 1275 ) 1276 1277 # Test the above on a table not created in the same txn. 1278 statement ok 1279 DROP TABLE t1, t2 CASCADE; 1280 CREATE TABLE t1 (x INT PRIMARY KEY); 1281 CREATE TABLE t2 (y INT) 1282 1283 statement ok 1284 BEGIN; 1285 ALTER TABLE t2 ADD COLUMN x INT; 1286 CREATE INDEX ON t2 (x); 1287 ALTER TABLE t2 ADD FOREIGN KEY (x) REFERENCES t1 (x); 1288 COMMIT 1289 1290 query TT 1291 SHOW CREATE t2 1292 ---- 1293 t2 CREATE TABLE t2 ( 1294 y INT8 NULL, 1295 x INT8 NULL, 1296 CONSTRAINT fk_x_ref_t1 FOREIGN KEY (x) REFERENCES t1(x), 1297 INDEX t2_x_idx (x ASC), 1298 FAMILY "primary" (y, rowid, x) 1299 ) 1300 1301 # Test that an FK can use a newly created index. 1302 statement ok 1303 DROP TABLE t1, t2 CASCADE; 1304 CREATE TABLE t1 (x INT PRIMARY KEY); 1305 CREATE TABLE t2 (x INT) 1306 1307 statement ok 1308 BEGIN; 1309 CREATE INDEX ON t2 (x); 1310 ALTER TABLE t2 ADD FOREIGN KEY (x) REFERENCES t1 (x); 1311 COMMIT 1312 1313 query TT 1314 SHOW CREATE t2 1315 ---- 1316 t2 CREATE TABLE t2 ( 1317 x INT8 NULL, 1318 CONSTRAINT fk_x_ref_t1 FOREIGN KEY (x) REFERENCES t1(x), 1319 INDEX t2_x_idx (x ASC), 1320 FAMILY "primary" (x, rowid) 1321 ) 1322 1323 # Test when default column value leads to an FK violation. 1324 statement ok 1325 DROP TABLE t1, t2 CASCADE 1326 1327 statement ok 1328 CREATE TABLE t1 (x INT PRIMARY KEY); 1329 INSERT INTO t1 VALUES (1); 1330 CREATE TABLE t2 (y INT); 1331 INSERT INTO t2 VALUES (2) 1332 1333 statement error pq: foreign key violation 1334 ALTER TABLE t2 ADD COLUMN x INT DEFAULT 2 UNIQUE REFERENCES t1 (x) 1335 1336 # Test that it works with an appropriate default. 1337 statement ok 1338 ALTER TABLE t2 ADD COLUMN x INT DEFAULT 1 UNIQUE REFERENCES t1 (x) 1339 1340 query TT 1341 SHOW CREATE t2 1342 ---- 1343 t2 CREATE TABLE t2 ( 1344 y INT8 NULL, 1345 x INT8 NULL DEFAULT 1:::INT8, 1346 CONSTRAINT fk_x_ref_t1 FOREIGN KEY (x) REFERENCES t1(x), 1347 UNIQUE INDEX t2_x_key (x ASC), 1348 FAMILY "primary" (y, rowid, x) 1349 )