/ Check-in [353b1b18]
Login
Overview
Comment:Prevent ALTER TABLE from being used to add a column with a REFERENCES clause and a non-NULL default value while foreign key support is enabled.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1:353b1b18253ab71ba38a887e555994f5469b87bd
User & Date: dan 2009-09-25 11:26:55
Context
2009-09-25
12:00
Fix comments in fkey2.c to reflect the immediate-constraint-counter approach. check-in: 9fd54b0a user: dan tags: trunk
11:26
Prevent ALTER TABLE from being used to add a column with a REFERENCES clause and a non-NULL default value while foreign key support is enabled. check-in: 353b1b18 user: dan tags: trunk
2009-09-24
18:19
Fixes and test cases to make sure the affinity and collation sequence associated with the parent key is used when comparing it with a child key value. check-in: 76e8e74b user: dan tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to src/alter.c.

510
511
512
513
514
515
516





517
518
519
520
521
522
523
  if( pCol->isPrimKey ){
    sqlite3ErrorMsg(pParse, "Cannot add a PRIMARY KEY column");
    return;
  }
  if( pNew->pIndex ){
    sqlite3ErrorMsg(pParse, "Cannot add a UNIQUE column");
    return;





  }
  if( pCol->notNull && !pDflt ){
    sqlite3ErrorMsg(pParse, 
        "Cannot add a NOT NULL column with default value NULL");
    return;
  }








>
>
>
>
>







510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
  if( pCol->isPrimKey ){
    sqlite3ErrorMsg(pParse, "Cannot add a PRIMARY KEY column");
    return;
  }
  if( pNew->pIndex ){
    sqlite3ErrorMsg(pParse, "Cannot add a UNIQUE column");
    return;
  }
  if( (db->flags&SQLITE_ForeignKeys) && pNew->pFKey && pDflt ){
    sqlite3ErrorMsg(pParse, 
        "Cannot add a REFERENCES column with non-NULL default value");
    return;
  }
  if( pCol->notNull && !pDflt ){
    sqlite3ErrorMsg(pParse, 
        "Cannot add a NOT NULL column with default value NULL");
    return;
  }

Changes to test/fkey2.test.

53
54
55
56
57
58
59


60
61
62
63
64
65
66
...
733
734
735
736
737
738
739






































740
741
742
743
744
745
746
#
# fkey2-11.*: Test CASCADE actions.
#
# fkey2-12.*: Test RESTRICT actions.
#
# fkey2-13.*: Test that FK processing is performed when a row is REPLACED by
#             an UPDATE or INSERT statement.


#
# fkey2-genfkey.*: Tests that were used with the shell tool .genfkey
#            command. Recycled to test the built-in implementation.
#


proc drop_all_tables {{db db}} {
................................................................................
  execsql { 
    REPLACE INTO pp(rowid, a, b, c) VALUES(2, 2, 2, 3);
    SELECT rowid, * FROM pp;
    SELECT * FROM cc;
  }
} {2 2 2 3 2 3 1}







































#-------------------------------------------------------------------------
# The following block of tests, those prefixed with "fkey2-genfkey.", are 
# the same tests that were used to test the ".genfkey" command provided 
# by the shell tool. So these tests show that the built-in foreign key 
# implementation is more or less compatible with the triggers generated 
# by genfkey.
#







>
>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
...
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
#
# fkey2-11.*: Test CASCADE actions.
#
# fkey2-12.*: Test RESTRICT actions.
#
# fkey2-13.*: Test that FK processing is performed when a row is REPLACED by
#             an UPDATE or INSERT statement.
#
# fkey2-14.*: Test the ALTER TABLE and DROP TABLE commands.
#
# fkey2-genfkey.*: Tests that were used with the shell tool .genfkey
#            command. Recycled to test the built-in implementation.
#


proc drop_all_tables {{db db}} {
................................................................................
  execsql { 
    REPLACE INTO pp(rowid, a, b, c) VALUES(2, 2, 2, 3);
    SELECT rowid, * FROM pp;
    SELECT * FROM cc;
  }
} {2 2 2 3 2 3 1}

#-------------------------------------------------------------------------
# The following tests, fkey2-14.*, test that the "DROP TABLE" and "ALTER
# TABLE" commands work as expected wrt foreign key constraints.
#
# fkey2-14.1*: ALTER TABLE ADD COLUMN
# fkey2-14.2*: ALTER TABLE RENAME TABLE
# fkey2-14.3*: DROP TABLE
#
drop_all_tables
do_test fkey2-14.1 {
  # Adding a column with a REFERENCES clause is not supported.
  execsql { 
    CREATE TABLE t1(a PRIMARY KEY);
    CREATE TABLE t2(a, b);
  }
  catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 }
} {0 {}}
do_test fkey2-14.2 {
  catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 }
} {0 {}}
do_test fkey2-14.3 {
  catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL}
} {0 {}}
do_test fkey2-14.4 {
  catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'}
} {1 {Cannot add a REFERENCES column with non-NULL default value}}
do_test fkey2-14.5 {
  catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 }
} {1 {Cannot add a REFERENCES column with non-NULL default value}}
do_test fkey2-14.5 {
  execsql { 
    PRAGMA foreign_keys = off;
    ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1;
    PRAGMA foreign_keys = on;
    SELECT sql FROM sqlite_master WHERE name='t2';
  }
} {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}}

#-------------------------------------------------------------------------
# The following block of tests, those prefixed with "fkey2-genfkey.", are 
# the same tests that were used to test the ".genfkey" command provided 
# by the shell tool. So these tests show that the built-in foreign key 
# implementation is more or less compatible with the triggers generated 
# by genfkey.
#