SQLite Create, Alter and Drop table
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
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 :
However the following are invalid identifier:
- tst table
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"
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_".
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.
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.
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.
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]
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:
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;
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';
The DROP TABLE command removes a table from a database.
DROP TABLE table_name [, ...] or DROP TABLE [database_name.]table_name [, ...]
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;