SQLite Forum

Renaming TABLE *into* existing VIEW definition?
Login

Renaming TABLE *into* existing VIEW definition?

(1) By Mark Lawrence (mark) on 2020-04-30 18:21:27 [link] [source]

Don't know how to describe my issue better than this:

CREATE VIEW v1 AS
    SELECT * FROM x;

CREATE TABLE y(id INTEGER);

ALTER TABLE y RENAME TO x;
-- Error: near line 6: error in view v1: no such table: main.x

I don't know why the rename concerns itself with table 'x'. It feels to me like that error is raised a little too early.

https://sqlite.org/lang_altertable.html says:

If the table being renamed has triggers or indices,
then these remain attached to the table after it has been renamed. 

That doesn't seem to cover this situation. If this is not a bug per se then perhaps the documentation could at explain the background actions (and constraints) in more detail.

(2) By Dan Kennedy (dan) on 2020-05-04 07:35:05 in reply to 1 [source]

Use:

      PRAGMA legacy_alter_table=ON 

before the ALTER TABLE command.