Creating and Dropping Tables

Creating and dropping database tables in SQLite is performed with the CREATE TABLE and DROP TABLE commands respectively. The basic syntax for CREATE TABLE is as follows:

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

Simply put, a table may be declared as temporary, if desired, and the structure of each table has to have one or more column definitions followed by zero or more constraints.

Table Column Definitions

A column definition is defined as follows:

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

As you saw in Chapter 2, SQLite is typeless and therefore the type attribute is actually optional. Except for an INTEGER PRIMARY KEY column, the data type is only used to determine whether values stored in that column are to be treated as strings or numbers when compared to other values.

You can use the optional CONSTRAINT clause to specify one or more of the following column constraints that should be enforced when data is inserted:

• NOT NULL

• DEFAULT

• PRIMARY KEY

• UNIQUE

A column declared as NOT NULL must contain a value; otherwise, an INSERT attempt will fail, as demonstrated in the following example:

 sqlite> CREATE TABLE vegetables (    ...>   name CHAR NOT NULL,    ...>   color CHAR NOT NULL    ...> ); sqlite> INSERT INTO vegetables (name) VALUES ('potato'); SQL error: vegetables.color may not be NULL 

Often, a column declared NOT NULL is also given a DEFAULT value, which will be used automatically if that column is not specified in an INSERT. The following example shows this in action.

 sqlite> CREATE TABLE vegetables (    ...>   name CHAR NOT NULL,    ...>   color CHAR NOT NULL DEFAULT 'green'    ...> ); sqlite> INSERT INTO vegetables (name, color) VALUES ('carrot', 'orange'); sqlite> INSERT INTO vegetables (name) VALUES ('bean'); sqlite> SELECT * FROM vegetables; name        color ----------  ---------- carrot      orange bean        green 

However, if you attempt to insert NULL explicitly into a NOT NULL column, SQLite will still give an error:

 sqlite> INSERT INTO vegetables (name, color) VALUES ('cabbage', NULL); SQL error: vegetables.color may not be NULL 

Functionally, a PRIMARY KEY column behaves just the same as one with a UNIQUE constraint. Both types of constraint enforce that the same value may only be stored in that column once, but other than the special case of an INTEGER PRIMARY KEY, the only point to note is that a table can have only one PRIMARY KEY column.

SQLite will raise an error whenever an attempt is made to insert a duplicate value into a UNIQUE or PRIMARY KEY column, as shown in the following example. This example also shows that a column can be declared as both NOT NULL and UNIQUE.

 sqlite> CREATE TABLE vegetables (    ...>   name CHAR NOT NULL UNIQUE,    ...>   color CHAR NOT NULL    ...> ); sqlite> INSERT INTO vegetables (name, color) VALUES ('pepper', 'red'); sqlite> INSERT INTO vegetables (name, color) VALUES ('pepper', 'green'); SQL error: column name is not unique 

Resolving Conflicts

NOT NULL, PRIMARY KEY, and UNIQUE constraints may all be used in conjunction with an ON CONFLICT clause to specify the way a conflict should be resolved if an attempt to insert or modify data violates a column constraint.

The conflict resolution algorithms supported are

• ROLLBACK

• ABORT

• FAIL

• IGNORE

• REPLACE

You could apply a constraint to the vegetables table from the preceding example as follows:

 sqlite> CREATE TABLE vegetables (    ...>   name CHAR NOT NULL UNIQUE ON CONFLICT REPLACE,    ...>   color CHAR NOT NULL    ...> ); 

This time, because REPLACE was specified as the conflict resolution algorithm, inserting the same vegetable name twice does not cause an error. Instead the new record replaces the conflicting record.

 sqlite> INSERT INTO vegetables (name, color) VALUES ('pepper', 'red'); sqlite> INSERT INTO vegetables (name, color) VALUES ('pepper', 'green'); sqlite> SELECT * FROM vegetables; name        color ----------  ---------- pepper      green 

The REPLACE algorithm ensures that an SQL statement is always executed, even if a UNIQUE constraint would otherwise be violated. Before the UPDATE or INSERT takes place, any pre-existing rows that would cause the violation are removed. If a NOT NULL constraint is violated and there is no DEFAULT value, the ABORT algorithm is used instead.

The ROLLBACK algorithm causes an immediate ROLLBACK TRANSACTION to be issued as soon as the conflict occurs and the command will exit with an error.

When you use the ABORT algorithm, no ROLLBACK TRANSACTION is issued, so if the violation occurs within a transaction consisting of more than one INSERT or UPDATE, the database changes from the previous statements will remain. Any changes attempted by the statement causing the violation, however, will not take place. For a single command using only an implicit transaction, the behavior is identical to ROLLBACK.

The FAIL algorithm causes SQLite to stop with an error when a constraint is violated; however, any changes made as part of that command up to the point of failure will be preserved. For instance, when an UPDATE statement performs a change sequentially on many rows of the database, any rows affected before the constraint was violated will remain updated.

SQLite will never stop with an error when the IGNORE algorithm is specified and the constraint violation is simply passed by. In the case of an UPDATE affecting multiple rows, the modification will take place for every row other than the one that causes the conflict, both before and after.

The ON CONFLICT clause in a CREATE TABLE statement has the lowest precedence of all the places in which it can be specified. An overriding conflict resolution algorithm can be specified in the ON CONFLICT clause of a BEGIN TRANSACTION command, which can in turn be overridden by the OR clause of a COPY, INSERT, or UPDATE statement. We will see the respective syntaxes for these clauses later in this chapter.

The CHECK Clause

The CREATE TABLE syntax also allows for a CHECK clause to be defined, with an expression in parentheses. This is a feature included for SQL compatibility and is reserved for future use, but at the time of this writing is not implemented.

Using Temporary Tables

Using CREATE TEMPORARY TABLE creates a table object in SQLite that can be queried and manipulated exactly the same as a nontemporary table. However, the table will only be visible to the process in which it was created and will be destroyed as soon as the database is closed.

 $ sqlite tempdb SQLite version 2.8.12 Enter ".help" for instructions sqlite> CREATE TEMPORARY TABLE temptable (    ...>   myfield char    ...> ); sqlite> INSERT INTO temptable (myfield) VALUES ('abc'); sqlite> .quit $ sqlite tempdb SQLite version 2.8.12 Enter ".help" for instructions sqlite> INSERT INTO temptable (myfield) VALUES ('xyz'); SQL error: no such table: temptable 

The data inserted into a temporary table and its schema are not written to the connected database file, nor is there a record created in sqlite_master. Instead a separate table, sqlite_temp_master, is used to reference temporary tables.

 sqlite> CREATE TEMPORARY TABLE temptable (    ...>   myfield char    ...> ); sqlite> SELECT * FROM sqlite_temp_master;     type = table     name = temptable tbl_name = temptable rootpage = 3      sql = CREATE TEMPORARY TABLE temptable (   myfield char ) 

Temporary tables are specific to the sqlite handle, not the process. Surprisingly, people often become confused about this, particularly in Windows, where a common design pattern is to open a separate sqlite handle to the same database from each thread.