Small note on this: Don't forget any indexes, triggers, etc. They will carry over when the rename table is done, but they'll have their original names. So if you have a table foo with index foo_idx, and do "alter table foo rename to bar", then you'll have index foo_idx on table bar. Which means some droping and renaming of indexes, triggers, foreign keys etc as well.
sqlite> create table foo (id integer primary key, foo text); sqlite> create index foo_idx on foo (foo); sqlite> create trigger foo_trg before delete on foo begin select raise(abort, 'Not allowing deletes from foo'); end; sqlite> create table bar(id integer primary key, foo_id int references foo); sqlite> select * from sqlite_master; type name tbl_name rootpage sql -------- -------- ---------- ---------- ---------------------------------------------------------------------------- ---------------------------------------- table foo foo 2 CREATE TABLE foo (id integer primary key, foo text) index foo_idx foo 3 CREATE INDEX foo_idx on foo (foo) trigger foo_trg foo 0 CREATE TRIGGER foo_trg before delete on foo begin select raise(abort, 'Not a llowing deletes from foo'); end table bar bar 4 CREATE TABLE bar(id integer primary key, foo_id int references foo) sqlite> alter table foo rename to foobar; sqlite> select * from sqlite_master; type name tbl_name rootpage sql -------- -------- ---------- ---------- ---------------------------------------------------------------------------- ---------------------------------------- table foobar foobar 2 CREATE TABLE "foobar" (id integer primary key, foo text) index foo_idx foobar 3 CREATE INDEX foo_idx on "foobar" (foo) trigger foo_trg foobar 0 CREATE TRIGGER foo_trg before delete on "foobar" begin select raise(abort, ' Not allowing deletes from foo'); end table bar bar 4 CREATE TABLE bar(id integer primary key, foo_id int references "foobar") sqlite> create table foo (id integer primary key, something_new text); sqlite> create index foo_idx on foo (something_new); Error: index foo_idx already exists sqlite> -----Original Message----- From: sqlite-users [mailto:[email protected]] On Behalf Of Charles Leifer Sent: Tuesday, May 22, 2018 5:53 PM To: SQLite mailing list Subject: Re: [sqlite] ALTER TABLE SQLite supports renaming tables, so in my experience you move the old table out of the way, and create the new table with the desired schema and the original name. On Tue, May 22, 2018 at 2:34 PM, Igor Korot <[email protected]> wrote: > Hi, Charles, > > On Tue, May 22, 2018 at 2:30 PM, Charles Leifer <[email protected]> > wrote: > > As a workaround, you can always rename the existing table, create the new > > table with desired attributes, and do a INSERT INTO ... SELECT FROM > > old_table. Then you can safely drop the old table. > > But the table_name will be different. > Also the data in the old table might be referencing some other table. > So this process is not really very > straightforward... > > Thank you. > > > > > On Tue, May 22, 2018 at 1:14 PM, Thomas Kurz <[email protected]> > wrote: > > > >> > ALTER TABLE ADD COLUMN has existed for a long time. > >> > >> Yes, sorry, I mixed things up. > >> > >> The order of importance is imho: > >> 1. RENAME COLUMN (shouldn't be too hard) > >> 2. DROP COLUMN (should be a bit more comlicated but feasible) > >> 3. MODIFY COLUMN > >> > >> > What kind of MODIFY COLUMN changes do you have in mind? > >> > >> I understand this can be difficult as there are many possible operations > >> that might be incompatible with the data already stored in that column. > >> Last time I needed MODIFY, I wanted to change the ON UPDATE/DELETE > CASCADE > >> action of a foreign key column. Also adding/removing a foreign key > would be > >> useful. > >> > >> Kind regards, > >> Thomas > >> > >> _______________________________________________ > >> sqlite-users mailing list > >> [email protected] > >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > >> > > _______________________________________________ > > sqlite-users mailing list > > [email protected] > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > [email protected] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
