Dear all,
I really followed the 12-step ALTER TABLE schema and stumbled upon the
following problem:
PRAGMA foreign_keys=1;
CREATE TABLE A (id INTEGER PRIMARY KEY, v1 TEXT, v2 INTEGER);
CREATE TABLE B (id INTEGER PRIMARY KEY, ref REFERENCES A(id));
INSERT INTO A (v1, v2) VALUES ('test7', 7);
INSERT INTO A (v1, v2) VALUES ('test123', 123);
INSERT INTO B (ref) VALUES (2);
CREATE VIEW v AS SELECT B.id, A.v1, A.v2 FROM B LEFT JOIN A ON B.ref=A.id;
-- modify table A now
PRAGMA foreign_keys=0; -- step 1
BEGIN TRANSACTION; -- step 2
-- skip step 3, no indexes and triggers
CREATE TABLE new_a (id INTEGER PRIMARY KEY, v1 TEXT, v2 INTEGER, v3 TEXT); --
step 4
INSERT INTO new_a SELECT id, v1, v2, 'new' FROM A; -- step 5
DROP TABLE A; -- step 6
ALTER TABLE new_a RENAME TO A; -- step 7
-- skip step 8, no indexes and triggers
-- skip step 9, the view should not be affected (referenced columns stay the
same)
PRAGMA foreign_key_check; -- step 10
COMMIT; -- step 11
PRAGMA foreign_keys=1; -- step 12
Result with version 3.28.0:
Error: near line 15: error in view v: no such table: main.A
(i.e. step 7 fails)
(Note that this is only an example, I know that I could use ADD COLUMN if I
wanted to add a column. Indeed, I want to change a column constraint.)
However, this works (but does not correspond to the schema from
https://www.sqlite.org/lang_altertable.html):
BEGIN TRANSACTION;
CREATE TABLE tmp AS SELECT * FROM A;
DROP TABLE A;
CREATE TABLE A (id INTEGER PRIMARY KEY, v1 TEXT, v2 INTEGER, v3 TEXT);
INSERT INTO A SELECT id, v1, v2, 'new' FROM tmp;
DROP TABLE tmp;
PRAGMA foreign_key_check;
COMMIT;
PRAGMA foreign_keys=1;
I consider this a bug and would like to renew my request for full ALTER TABLE
support. Imho it is unacceptable to have to go thru 12 steps only for adding a
CHECK constraint to a column.
Kind regards,
Thomas
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
