SQLiteDB – Alter Table:

In SQLite, altering table can be done in 2 ways.

Rename the table

Add a new column

It is not possible to rename or delete the existing column.

Rename the table

Syntax:

ALTER TABLE current_table_name RENAME TO new_table_name;

Example:

ALTER TABLE STUDENTS RENAME TO USERS;

Check the current tables in the database.

sqlite> .tables

CUSTOMERS  STUDENTS

Issue the command and change the table name STUDENTS to USERS.

sqlite> ALTER TABLE STUDENTS RENAME TO USERS;

sqlite> .tables

CUSTOMERS  USERS

Add a new column

Syntax:

ALTER TABLE table_name ADD COLUMN column_definition;

Example:

ALTER TABLE USERS ADD COLUMN age integer;

NOTE: You cannot add or remove the constraints too. So be careful when you adding a new column make sure that you are not mentioning the constraints.

 sqlite> .tables

CUSTOMERS  USERS



sqlite> .schema users

CREATE TABLE 'USERS' (

cust_id integer PRIMARY KEY,

first_name text not null,

last_name  text not null,

Gender  text not null,

Email text not null UNIQUE,

City   text not null

);

Let’s add the new column with constraint and it will throw an error.

sqlite> ALTER TABLE USERS ADD COLUMN age integer not null;

SQL error: Cannot add a NOT NULL column with default value NULL



Let's add the new column without constraint



sqlite> ALTER TABLE USERS ADD COLUMN age integer ;



sqlite>.schema users

CREATE TABLE 'USERS' (

cust_id integer PRIMARY KEY,

first_name text not null,

last_name text not null,

Gender text not null,

Email text not null UNIQUE,

City   text not null

, age integer);

sqlite>