How SQLite modifies table structure field types

SQLite only supports part of the ALTER TABLE statement, we can change the name of a table with the ALTER TABLE statement, or we can add a field (column) to the table, but we cannot delete an existing field, or change the name of an existing field, the data type , qualifiers, and so on.

Change table name-ALTER TABLE old table name RENAME to new table name

Add a column-ALTER table name add column name data type

Modifying a column can not be done directly with the "ALTER table table name ADD column name data type" in the same way as other databases, so take a different approach, see below

Tools/Materials

• Sqlite

Method/Step

1. 1

   --1. Change table name to temp table

   ALTER table Student "RENAME to" _student_old_20140409 ";

2. 2

   --2. Create a new table

   CREATE TABLE "Student" (

   "Id"   INTEGER PRIMARY KEY AutoIncrement,

   "Name"   Text);

3. 3

   --3. Import data

   INSERT into "Student" ("id", "Name") Select "id", "Title" From "_student_old_20140409";

4. 4

   --4. Update sqlite_sequence

   Update "sqlite_sequence" SET seq = 3 WHERE name = ' Student ';

   Because the self-growing field is used in SQLite, the engine automatically generates a Sqlite_sequence table that records the maximum value used for the self-growing field of each table, so it is updated together. If there is no self-growth set, skip this step.

5. 5

   --5. Delete temporary table (optional)

   DROP table _student_old_20140409;

   end