SQLIte DROP table ROLL back

Question

In Android SQLite I had one table MyTable. By mistake I dropped it after upgrade the DB.

How can I ROLL BACK that dropped table if it's possible.

Any good answer will be accepted.

Thanks.

Answer 1

Dropping tables is not a recoverable action, unless performed as part of a transaction that is rolled back (which appears to be not the scenario for your particular case).

From the SQLite documentation:

The DROP TABLE statement removes a table added with the CREATE TABLE statement. The name specified is the table name.

The dropped table is completely removed from the database schema and the disk file. The table can not be recovered. All indices and triggers associated with the table are also deleted.

That's not quite the complete picture, as the behaviour under rolled-back transaction can be seen with (tested on https://sqliteonline.com/):

drop table if exists paxtable;
create table paxtable (paxcolumn integer);
insert into paxtable values (42);
begin transaction;
drop table paxtable;
rollback;
select paxcolumn from paxtable;

That shows that the table still exists after the rollback. If you commit rather than roll back (or if you remove the transactional control altogether), the table has died, expired, gone to meet its maker, shuffled off this mortal coil, <insert your favourite metaphor here>.

So, since you didn't do it as part of a rolled-back transaction (as evidenced by the fact the table has actually gone), you'll need to re-create it from scratch (or from backups if possible).

Answer 2

CLARIFICATION:

Although you can commit or rollback DML statements like "insert" or "delete" (provided you do it within a transaction), in general you cannot rollback a DDL statement like "alter table" or "drop table".

This is true for most databases under most circumstances: Oracle, MSSQL, mySQL, etc.

There is an exception for sqlite: if you drop table in a transaction, then a rollback will restore that table.

Otherwise (per the sqlite manual):

• http://sqlite.org/lang_droptable.html

The DROP TABLE statement removes a table added with the CREATE TABLE statement. The name specified is the table name. The dropped table is completely removed from the database schema and the disk file. The table can not be recovered. All indices and triggers associated with the table are also deleted.

PS:

This link discusses "DDL", "DML" and related acronyms, if you're interested:

• http://www.orafaq.com/faq/what_are_the_difference_between_ddl_dml_and_dcl_commands

Answer 3

I think there's two different interpretations of this question and I want to make sure both get answered and demonstrated conclusively since this is still the top search result for sqlite drop table rollback (and the links to the SQLite documentation seems misleading as well).

To the first question, you can rollback a drop table DDL action that occurs within a transaction scope, i.e.,

// with connection created
var transaction = connection.BeginTransaction();
try {
    // create a table
    // drop a different table
    transaction.Commit(); // go ahead and commit if everything is successfully
}
catch
{
    transaction.Rollback(); // rollback in case of error
}

And to confirm this is the same behavior in a language-agnostic fashion, here's the same behavior using the SQ Lite command-line shell:

sqlite3 demo
// outside transaction scope
create table tbl1( col varchar(10));
// list the current tables
.tables
// start a transaction that wraps both your DDL commands 
begin transaction;
enter code here
create table tbl2 (col varchar(10));
drop table tbl1;
rollback;
.tables

The expectation is that the final list tables command, should still return tbl1 since both the create table and drop table commands were both rolled back. Another way to say this is that SQLite is not bound by the same DML/DDL distinction for what operations can be rolled back that are present in Oracle.

For the second interpretation of the question, i.e., can I recover a table dropped outside of a transaction scope (which would also entail the "Oh S#%T" experience you may have had as a developer as well as disaster recovery), the references to the SQ Lite documentation are appropriate:

The dropped table is completely removed from the database schema and the disk file. The table can not be recovered. All indices and triggers associated with the table are also deleted.