Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
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: |
353b1b18253ab71ba38a887e555994f5 |
| 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
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. # |