"delete from table" does not delete the table?

Question

I create "database.db" and everything goes fine, but why doesn't it delete the table at the end? Each time I run it, I get the message "table already exist" when creating the table.

int main() { sqlite3 *db; //Database Handle char *zErr; int rc; char *sql; rc = sqlite3_open("database.db", &db); if(rc) { cout << "Can't open database: " << sqlite3_errmsg(db) << endl;; sqlite3_close(db); exit(1); } sql = "create table test(PID int primary key, Name text)"; //sql query rc = sqlite3_exec(db, sql, NULL, NULL, &zErr); //execute sql statement if(rc != SQLITE_OK) { if (zErr != NULL) { cout << "SQL error: " << zErr << endl; sqlite3_free(zErr); } } else { sql = "insert into test values (1,'John')"; rc = sqlite3_exec(db, sql, NULL, NULL, &zErr); sql = "insert into test values (2,'Smith')"; rc = sqlite3_exec(db, sql, NULL, NULL, &zErr); } //delete the table on exit. rc = sqlite3_exec(db, "delete from test", NULL, NULL, &zErr); sqlite3_close(db); return 0; } 

Furthermore, is it possible to automatically generate primary keys after the last larger key existing in the database?

Answer 1

You need to use drop table . delete deletes rows in the table.

 rc = sqlite3_exec(db, "drop table test", NULL, NULL, &zErr); 

SQLite will automatically increment the integer field declared as the primary key. See here .

Answer 2

You use the DELETE command to delete table rows.

You use the DROP command to delete an entire table or other database item.

To create a table, you can also use CREATE TABLE IF NOT EXISTS if you are not sure if it exists.

To get your automatically generated row identifiers, use: id INTEGER PRIMARY KEY in your CREATE TABLE command.

Answer 3

• Use DROP TABLE .
• In SQLite docs:

The usual algorithm is to give the newly created row a ROWID that is larger than the largest ROWID in the table before insertion.

Answer 4

DML " delete from test " deletes all rows from the table test. If you want to reset the table, try " delete table test " or " drop table " instead.