SQLite Create, Alter and Drop table

Last update on May 30 2022 13:11:42 (UTC/GMT +8 hours)

Introduction

The SQLite CREATE TABLE command is used to create a new table in an SQLite database. It is also used to create indexes, views and triggers. A CREATE TABLE command specifies the following attributes of the new table:.

The name of the new table.
The database in which the new table is created. Tables may be created in the main database, the temp database, or in any attached database.
The name of each column in the table.
The declared type of each column in the table and each column must have one datatype, that may be NULL - the value is a NULL value, INTEGER - a signed integer, REAL - a floating point value, TEXT - a text string or BLOB - a blob of data.
A default value or expression for each column in the table.
A default collation sequence to use with each column.
Optionally, a PRIMARY KEY for the table. Both single column and composite (multiple column) primary keys are supported.
A set of SQL constraints for each table. SQLite supports UNIQUE, NOT NULL, CHECK and FOREIGN KEY constraints.
A unique indexes will automatically create to enforce a UNIQUE or PRIMARY KEY constraint. and they cannot be manually dropped with the DROP INDEX command.

Table of contents

Naming Conventions

Each database, table, column, index, trigger, or view should have a name by which it can be identified. An identifier name must begin with a letter or the underscore character, which may be followed by a number of alphanumeric characters or underscores. No other characters may allowed with this name.

The valid identifier names are :

tsttable
tst_table
table456
z

However the following are invalid identifier:

tst table
tst-table
456table

Other characters can be used in identifiers if they are enclosed in double quotes (or square brackets), for example:

"test table"
"tst table"
"123 456"

Reserved Keywords

Each CREATE TABLE statement must specify a name for the new table. Table names that begin with "sqlite_" are reserved for internal use. It is an error to attempt to create a table with a name that starts with "sqlite_".

Name Length

In SQLite there is not restricted upper limit on the length of an identifier name, so you can use a manageable length of identifier name to be use.

Case Sensitivity

For the most part, case sensitivity in SQLite is off. Table names and column names can be typed in uppercase, lowercase, or mixed case, and different capitalizations of the same database object name can be used interchangeably.

Notes:

When a new table is created, it must be in a database and the database name may be either "main", "temp", or the name of an attached database. If the keyword "TEMP" or "TEMPORARY" used between the "CREATE" and "TABLE" then the new table is created in the temp database. Both database name and TEMP or TEMPORARY keyword can not be used. If no database name is specified and the TEMP keyword is not present then the table is created in the main database.

Attempt to create a table into a database which contains a table or index or view of the same name, an error will occur, but if "IF NOT EXISTS" clause is specified as a part of the CREATE TABLE statement will create no effect.

Syntax:

CREATE [TEMP | TEMPORARY] TABLE table-name (
column-def[, column-def]
[,constraint]
);

TEMPORARY - a table may be declared as temporary, if desired.

Column-def. - A column definition consists of the name of the column, and followed by the declared type of the column which is optional, then one or more optional column constraints.

A column definition can be defined as follows:

name [type] [[CONSTRAINT name] column-constraint]

Example

Here is a simple Create Table Statement:

CREATE TABLE company(
com_id text(4),
com_name text(15));

To see the structure of the table, use the following commands:

Sample Output:

sqlite> .mode column
sqlite> .header on
sqlite> PRAGMA table_info(company);
cid         name        type        notnull     dflt_value  pk
----------  ----------  ----------  ----------  ----------  ----------
0           com_id      text(4)     0                       0
1           com_name    text(15)    0                       0

Here the .mode command shows the columns and .header on command shows the header on.

Create a table to use TEMPORARY clause

CREATE TABLE TEMPORARY (
com_id text(4),
com_name text(15));

This time table is created in temp databases. If you want to see the created table execute the following command-

sqlite> .tables
TEMPORARY

If you use the table name with TEMPORARY keyword an error message will appear. Here see below-

sqlite> create table TEMPORARY company(
   ...> com_id text(4),
   ...> com_name text(15));
Error: near "company": syntax error

Making a copy of an existing table within the main database

It is very much necessery to a developer to take a backup of the original table which is on working, and as no harmful measure happened unmindfully or accediently at the time of developing. Additionally, you may need to change column names or remove one or more columns from a sqlite table, so it is good practice to take a backup of the working table time to time.

If you want to take a backup of company table including all records, the following statement can be used

CREATE TABLE backup_company AS SELECT * FROM company ;

If you want to take a backup of only structure of company table, the following statement can be used

CREATE TABLE backup_company AS SELECT * FROM company LIMIT 0;

ALTER TABLE

The ALTER TABLE command can only be used in SQLite to allow the user only to rename a table or to add a new column to an existing table. It is not possible to rename a column or remove a column, or add or remove constraints from a table.

The RENAME TO command is used to rename the table identified by [database-name.]table-name to new-table-name. This command cannot be used to move a table within two databases only be renamed within same database.

If the table being renamed containing triggers or indices, no effect will made and these remain intact as it was before renamed. However, any view definitions, or statements executed by triggers that refer to the table being renamed, will not be automatically modified.

Here we create a new table company.

sqlite> create table company(
   ...> com_id text(4),
   ...> com_name text(15));

To add a new column:

ALTER TABLE company ADD com_add VARCHAR(50);

After adding a column see the structure of the table company.

sqlite> .schema
CREATE TABLE company(
com_id text(4),
com_name text(15), 
com_add VARCHAR(50));

To rename a table:

sqlite> .tables
company           hrdb.departments  hrdb.jobs
hrdb.countries    hrdb.employees    hrdb.locations
hrdb.department   hrdb.job_history  hrdb.regions

Now rename the table company to new name company_new. Here is the statement below.

ALTER TABLE company RENAME TO company_new;

After rename, see the list of the tables below.

sqlite> .tables
company_new hrdb.departments  hrdb.jobs
hrdb.countries    hrdb.employees    hrdb.locations
hrdb.department   hrdb.job_history  hrdb.regions

Rrenaming or Dropping column from a table:

Note : Sqlite has only limited ALTER TABLE support. Dropping columns and renaming does not supported by SQLite. So it is better way to create a new table with the changes according to your requirement, then drop the original table and again rename the just created new table to the original table name. One thing to remember that, once a table is dropped, any associated triggers will be lost, so be aware about this. It is a good idea that, before rename or drop table you can take a copy of the statement of creating the table. Here is the statement from which you will get statement.

SELECT sql FROM sqlite_master WHERE tbl_name = 'table_name';

DROP TABLE

The DROP TABLE command removes a table from a database.

Syntax:

DROP TABLE table_name [, ...]
or
DROP TABLE [database_name.]table_name [, ...]

Paremeters:

table_name - The name of an existing table you want to drop. You may drop more than one tables by specifying their names seperated by a comma.

DROP TABLE company_new;

Previous: Data Types
Next: Constraint