modify table (again)

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

modify table (again)

Thomas Kurz
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: modify table (again)

Richard Hipp-3
On 7/1/19, Thomas Kurz <[hidden email]> wrote:
>
> I really followed the 12-step ALTER TABLE schema and stumbled upon the
> following problem:

Step 3 and Step 8 need to be modified to also record the content of
VIEWs in addition to INDEXes and TRIGGERs.  I am working on the
revised text now.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users