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 |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA1: |
353b1b18253ab71ba38a887e555994f5 |
| User & Date: | dan 2009-09-25 11:26:55.000 |
Context
|
2009-09-25
| ||
| 12:00 | Fix comments in fkey2.c to reflect the immediate-constraint-counter approach. (check-in: 9fd54b0aa7 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: 353b1b1825 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: 76e8e74b49 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 |
#
# 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}} {
| > > | 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
#
# 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}} {
|
| ︙ | ︙ | |||
733 734 735 736 737 738 739 740 741 742 743 744 745 746 |
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.
#
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 |
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.
#
|
| ︙ | ︙ |