Add a column to an Existing Table

Sqlite will allow you to add a column or columns to an existing table using the "ALTER TABLE" statement, however the removal of a column from a table requires that the table be recreated and the data from the old table to be loaded into the revised table using a select query. See the section on making a copy of an existing table.

ALTER TABLE table_name ADD new_column_name data_type ;

sqlite> .schema inventorystat
CREATE TABLE inventorystat (naicscode INTEGER,year INTEGER,month INTEGER,inventoryval DOUBLE);
sqlite>ALTER TABLE inventorystat ADD descrip VARCHAR(50);
sqlite> .schema inventorystat
CREATE TABLE inventorystat(naicscode INTEGER,year INTEGER,month INTEGER,inventoryval DOUBLE,descrip VARCHAR(50));
sqlite>

In the example above we have entered ".schema inventorystat" to show the create statement for the table. The ALTER TABLE statement then adds the column "descrip" to the table specification which can be shown by entering ".schema inventorystat" once again.

Rename a Table

ALTER TABLE old_table_name RENAME TO new_table_name ;

sqlite> .tables
ReqDetail     ReqTotal    inventory     test1
ReqEquip     RequisitionDetail     reqdescrip    test2
sqlite> ALTER TABLE test2 RENAME TO backuplist;
sqlite> .tables
ReqDetail     ReqTotal     backuplist    reqdescrip
ReqEquip     RequisitionDetail     inventory    test1
sqlite>

In this example the table names "test2" is being renamed to "backuplist". The ".tables" statements show the list of the tables in the database before and after the change.

Deleting a table

Be very careful about using the "DROP TABLE" because once a table is gone, it is gone forever along with whatever data that it may have contained.

DROP TABLE database_name.table_name ;

sqlite> .tables
CustomerBackup    Customers    asforum    catalogsales
sqlite> DROP TABLE CustomerBackup;
sqlite> .tables
Customers    asforum    catalogsales
sqlite>

Renaming or Dropping Columns in Table

Sqlite has only limited ALTER TABLE support. Operations involving dropping columns, renaming columns or a combination of the two require that a new table be created with the changes incorporated in it. The original table is then dropped or renamed and the new table is renamed to the original table name. One thing to be aware of is that once a table is dropped, any associated triggers will be lost. It is a good idea to run a query against the sqlite_master table for the table that you are working on and copy them into a text editor so that they can be easily be reentered by copying and pasting the CREATE TRIGGER statement(s) into the command line. Triggers may have to be edited if any column names referenced in the trigger(s) have been changed

SELECT sql FROM sqlite_master WHERE tbl_name = 'table_name';

sqlite> select sql from sqlite_master where tbl_name = 'ReqEquip';
sql
CREATE TABLE 'ReqEquip'(ReqNumber INTEGER PRIMARY KEY,Requestor VARCHAR(30) NOT NULL,Auth VARCHAR(30) NOT NULL,ReqDate CHAR(10) NOT NULL)
CREATE TRIGGER ReqNumDel
BEFORE DELETE ON 'ReqEquip'
FOR EACH ROW BEGIN
DELETE from ReqDetail WHERE ReqDetail.ReqNumber = OLD.ReqNumber;
END sqlite>

Rename Columns in a Table

  1. Compose a CREATE TABLE statement with a new table name that uses an "AS SELECT" clause followed by a comma separated list of the columns names in the table.
  2. For those column names that you wish to change, follow the name with single space and then the new name enclosed in single quotes.
  3. Run the sql statement and confirm that the new table is structured as you want it and that the data from the old table has be loaded into the new table correctly.
  4. Rename or drop the original table and rename the new table to the name of the original table.

CREATE TABLE temp_table_name AS SELECT old_field_name ' new_field_name', list_other_fields FROM table_name;
ALTER TABLE table_name RENAME TO archive_old_table;
ALTER TABLE temp_table_name RENAME TO table_name;

sqlite> CREATE TABLE test1bk AS SELECT Requisition 'Req_num', Requestor,ReqDate 'Req_Date', Req_Total FROM test1; sqlite> ALTER TABLE test1 RENAME TO test1_old;
sqlite> ALTER TABLE test1bk RENAME TO test1;
sqlite> SELECT * FROM test1 limit 2;
Req_num|Requestor|Req_Date|Req_Total
1000|Carl Jones|2007/10/30|$ 24.12
1001|Peter Smith|2007/11/05|$ 13.51
sqlite>

Drop columns from a Table

  1. Compose a CREATE TABLE statement with a new table name that uses a SELECT AS clause followed by a list of the columns or field names that you wish to retain.
  2. Run the sql statement and confirm that the new table is structured as you want it.
  3. Drop the original table or rename it and rename the new table to the name of the original table.

Making a copy of an existing table within the main database

From time to time you may want to duplicate an existing table in a database to test table changes without risking the original data or to make a snapshot of the data for backup purposes. Additionally, if you wish to change column names or remove one or more columns from a sqlite table then it will be necessary to create a table with the desired changes and to copy the data using a sql query before renaming the original and the new tables.

CREATE TABLE new_table_name AS SELECT * FROM original_table_name;

sqlite> CREATE TABLE CustomersCopy AS SELECT * FROM Customers;
sqlite> SELECT * FROM CustomersCopy;
130169|Acme Widgets|1744 Alder Road|Apt 31C|Springfield|VA|20171|Alan Allen|57551267
130208|Nike Missiles Inc|5946 Oak Drive||Springfield|VA|20171|Lucy Baker|57155762
130247|Charlies Bakery|7116 Ginko St|suite 100|Springfield|VA|20171|Susan Nordrom|5715552363
130286|Unisales Inc.|8438 Maple Ave||Springfield|VA|20171-3521|Roger Norton|57551418
130325|M.I. Sinform & Sons|1785 Elm Avenue|P.O. Box 31|Springfield|VA|20171|Mi I. Sinform|5715558760
130364|Big Dents Towing Inc.|7578 Spruce St.|Building 31 A|Springfield|VA|20175231|George Spencer|5715557855
130365|Weneverpay Inc|428 Holly Ct||Springfield|VA|20171|Peter Norton|57155543
sqlite>

If you wish only to copy the table structure without copying the records then add a limit of 0 to the end of the statement.

CREATE TABLE new_table_name AS SELECT * FROM original_table_name LIMIT 0;

sqlite> CREATE TABLE CustomersEmpty AS SELECT * FROM Customers LIMIT 0;
sqlite> Select * from CustomersEmpty;
sqlite>
sqlite> .schema CustomersEmpty
CREATE TABLE CustomersEmpty(
AcctNumber INTEGER,
Custname VARCHAR(50),
Addr1 VARCHAR(50),
Addr2 VARCHAR(50),
City VARCHAR(30),
State CHAR(2),
Zipcode VARCHAR(10),
Contact VARCHAR(30),
Phone VARCHAR(10)
);

Colin Riley -- December 2007 -- Alphabetical Subject Index -- Contact form

Hosted by www.Geocities.ws

1