3SQLite Syntax and UseINTHISCHAPTERWELOOKINDETAIL
at the SQL syntax understood by SQLite.We will
discuss the full capabilities of the language and you will learn to write effective,accurate
SQL.You have already come across most of the supported SQL commands in Chapter 2,
ÒWorking with Data,Óin the context of the demo database.This chapter builds on that
knowledge by exploring the syntax and usage of each command in more detail to give a
very broad overview of what you can do using SQLite.
Each database,table,column,index,trigger,or view has a name by which it is identified
and almost always the name is supplied by the developer.The rules governing how a
valid identifier is formed in SQLite are set out in the next few sections.
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 be present.These identifier names are valid:
nmytablenmy_fieldnxyz123naHowever,the following are not valid identifiers:
nmy tablenmy-fieldn123xyz05 067232685x CH03 10/14/04 11:47 AM Page 55
56Chapter 3SQLite Syntax and Use
You can use other characters in identifiers if they are enclosed in double quotes (or
square brackets),for example:
sqlite> CREATE TABLE "123 456"("hello-world", " ");Name LengthSQLitedoes not have a fixed upper limit on the length of an identifier name,so any
name that you find manageable to work with is suitable.
Caremust be taken when using SQLite keywords as identifier names.As a general rule
of thumb you should try to avoid using any keywords from the SQL language as identi-
fiers,although if you really want to do so,they can be used providing they are enclosed
in square brackets.
For instance the following statement will work just fine,but this should not be mim-
icked on a real database for the sake of your own sanity.
sqlite> CREATE TABLE [TABLE] (...> [SELECT],...> [INTEGER] INTEGER,...> [FROM],...> [TABLE]...> );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.
SQL commands are always shown in this book with the keywords in uppercase for
clarity;however,this is not a requirement.
NoteThe CREATE TABLE, CREATE VIEW, CREATE INDEX, and CREATE TRIGGERstatements all storethe exact way in which they were typed to the database so that the command used to create a database
object can be retrieved by querying the sqlite_mastertable. Therefore it is always a good idea to for-mat your CREATEstatements clearly, so they can be referred to easily in the future.
Creating and Dropping Tables
Creating and dropping database tables in SQLite is performed with the
CREATE TABLEand DROP TABLEcommands respectively.The basic syntax for
CREATE TABLEis asfollows:
05 067232685x CH03 10/14/04 11:47 AM Page 56
57Creating and Dropping Tables
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 columndefinition is defined as follows:
name [type] [[CONSTRAINT name] column-constraint]*As you saw in Chapter 2,SQLite is typeless and therefore the
typeattribute is actual-
ly optional.Except for an
INTEGER PRIMARY KEYcolumn,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
CONSTRAINTclause to specify one or more of the following
column constraints that should be enforced when data is inserted:
nNOT NULLnDEFAULTnPRIMARY KEYnUNIQUEA column declared as
NOT NULLmust contain a value;otherwise,an
INSERTattempt willfail,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 NULLOften,a column declared
NOT NULLis also given a
DEFAULTvalue,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Õ);05 067232685x CH03 10/14/04 11:47 AM Page 57
58Chapter 3SQLite Syntax and Use
sqlite> SELECT * FROM vegetables;name color
bean greenHowever,if you attempt to insert
NULLexplicitly into a NOT NULLcolumn,SQLite
will still give an error:
sqlite> INSERT INTO vegetables (name, color) VALUES ('cabbageÕ, NULL);SQL error: vegetables.color may not be NULLFunctionally,a
PRIMARY KEYcolumn behaves just the same as one with a
UNIQUEconstraint.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 KEYcolumn.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 exam-
ple also shows that a column can be declared as both
NOT NULLand 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 uniqueResolving ConflictsNOT NULL,PRIMARY KEY,and
UNIQUEconstraints may all be used in conjunction with an
ON CONFLICTclause 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
nROLLBACKnABORTnFAILnIGNOREnREPLACEYou could apply a constraint to the
vegetablestable from the preceding example as
sqlite> CREATE TABLE vegetables (...> name CHAR NOT NULL UNIQUE ON CONFLICT REPLACE,...> color CHAR NOT NULL...> );05 067232685x CH03 10/14/04 11:47 AM Page 58
59Creating and Dropping Tables
REPLACEwas specified as the conflict resolution algorithm,insert-
ing 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 greenThe REPLACEalgorithm ensures that an SQL statement is always executed,even if a
UNIQUEconstraint would otherwise be violated.Before the
place,any pre-existing rows that would cause the violation are removed.If a
NOT NULLconstraint is violated and there is no
ABORTalgorithm is used instead.
The ROLLBACKalgorithm causes an immediate
ROLLBACK TRANSACTIONto be issued assoon as the conflict occurs and the command will exit with an error.
When you use the
is issued,so if the
violation occurs within a transaction consisting of more than one
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 FAILalgorithm causes SQLite to stop with an error when a constraint is violat-
ed;however,any changes made as part of that command up to the point of failure will
be preserved.For instance,when an
UPDATEstatement performs a change sequentially on
many rows of the database,any rows affected before the constraint was violated will
SQLite will never stop with an error when the
IGNOREalgorithm is specified and the
constraint violation is simply passed by.In the case of an
UPDATEaffecting multiple rows,
the modification will take place for every row other than the one that causes the con-
flict,both before and after.
The ON CONFLICTclause in a CREATE TABLEstatement 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 CONFLICTclause of a BEGIN TRANSACTIONcommand,which
can in turn be overridden by the
ORclause of a COPY,INSERT,or
will see the respective syntaxes for these clauses later in this chapter.
The CHECK ClauseTheCREATE TABLEsyntax also allows for a
CHECKclause to be defined,with an expres-
sion 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.
05 067232685x CH03 10/14/04 11:47 AM Page 59
60Chapter 3SQLite Syntax and Use
Using Temporary Tables
UsingCREATE TEMPORARY TABLEcreates 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 tempdbSQLite 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 tempdbSQLite version 2.8.12
Enter ".help" for instructions
sqlite> INSERT INTO temptable (myfield) VALUES ('xyzÕ);SQL error: no such table: temptableThe data inserted into a temporary table and its schema are not written to the con-
nected database file,nor is there a record created in
sqlite_master.Instead a separate
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 = temptabletbl_name = temptable
rootpage = 3sql = CREATE TEMPORARY TABLE temptable (myfield char)Temporary tables are specific to the
sqlitehandle,not the process.Surprisingly,peo-
ple often become confused about this,particularly in Windows,where a common design
pattern is to open a separate
sqlitehandle to the same database from each thread.
Anatomy of a SELECT Statement
Thesyntax definition for an SQL statement is
SELECT [ALL | DISTINCT] result[FROM table-list][WHERE expr][GROUP BY expr-list]05 067232685x CH03 10/14/04 11:47 AM Page 60
61Anatomy of a SELECT Statement[HAVING expr][compound-op select]*[ORDER BY sort-expr-list][LIMIT integer[(OFFSET|,) integer]]The only required item in a
SELECTstatement is the result,which can be one of the
nThe * characternA comma-separated list of one or more column names
The latter two bullet points should be combined into:ÒA comma-separated list of one or
more expressions.ÓThe original two points make it seem as if the following would be an
SELECT a+1, b+1 FROM ab;but this would be okay:
SELECT a, b FROM ab;In fact,both are valid.
Using the * character or a list of columns makes no sense without a
in fact an expression whose arguments are constants rather than database items can be
used alone in a SELECTstatement,as in the following examples.
sqlite> SELECT (60 * 60 * 24);86400sqlite> SELECT max(5, 20, -4, 8.7);20sqlite> SELECT random();220860261If the FROMlist is omitted,SQLite effectively evaluates the expression against a table
that always contains a single row.
The FROMlist includes one or more table names in a comma-separated list,each with
an optional alias name that can be used to qualify individual column names in the
result.Where aliases are not used,the table name in full can be used to qualify
columns.For instance,the two following
SELECTstatements are identicalÑthe latter uses a
mfor mytable.SELECT mytable.myfieldFROM myfield;SELECT m.myfieldFROM mytable m;05 067232685x CH03 10/14/04 11:47 AM Page 61
62Chapter 3SQLite Syntax and Use
The WHERE Clause
TheWHEREclause specifies one or more conditions used to impose restrictions on the
dataset returned by a
SELECT.It is used both to limit the number of rows returned and
to indicate a relationship used to join two tables together.
The general usage to impose a condition on the rows in a table is as follows:
SELECT resultFROM table-listWHERE expr;Expression
exprgenerally involves a comparison of some kind on a table column,as
shown in the following example:
SELECT *FROM mytable
WHERE myfield = 'somevalue';Table 3.1 shows the relational operators that can be used in a
a = ba is equal to ba != ba is not equal to ba < ba is less than ba > ba is greater than b
a <= ba is less than or equal to ba >= ba is greater than or equal to b
a IN (b, c)a is equal to either b or ca NOT IN (b, c)a is equal to neither b nor cWhen you perform a comparison using a relational operatorÑand particularly the
greater-than and less-than operators,
<and >Ñthe data type of the column comes intoplay.
The following example shows how comparisons between the numbers 8,11,and 101
differ greatly when performed as string operations.As integers,the order is as you would
expect,however as strings,Ô
101'is less than '11',which is in turn less than
individual character values in the string are compared from left to right in turn to deter-
mine which is the greatest value.
sqlite> CREATE TABLE compare (string TEXT, number INTEGER);sqlite> INSERT INTO compare (string, number) values ('101Õ, 101);sqlite> SELECT number FROM compare WHERE number > 11;101
sqlite> SELECT string FROM compare WHERE string > '11Õ;805 067232685x CH03 10/14/04 11:47 AM Page 62
63Anatomy of a SELECT StatementHowever,note that if you use a relational operator with a number argument that is
not contained in quotes,an integer comparison is performed regardless of the column
sqlite> SELECT string FROM compare WHERE string > 11;101Selecting from multiple tables without a
WHEREclause produces a Cartesian product of
the datasets and is usually not a desirable result.With two tables,each record in
table1ispaired with each record in
table2.The total number of rows returned is the product of
the number of rows in each table in the
FROMlist.The following example shows the result of a Cartesian product of three tables,each
containing just two rows.In total,eight rows are returned (2
×2×2).sqlite> SELECT table1.myfield, table2.myfield, table3.myfield...> FROM table1, table2, table3;table1.myfield table2.myfield table3.myfield
-------------- -------------- --------------
Table 1 row 1 Table 2 row 1 Table 3 row 1
Table 1 row 1 Table 2 row 1 Table 3 row 2
Table 1 row 1 Table 2 row 2 Table 3 row 1
Table 1 row 1 Table 2 row 2 Table 3 row 2
Table 1 row 2 Table 2 row 1 Table 3 row 1
Table 1 row 2 Table 2 row 1 Table 3 row 2
Table 1 row 2 Table 2 row 2 Table 3 row 1
Table 1 row 2 Table 2 row 2 Table 3 row 2In this example each table has a field called
myfield,so each column in the
resulthas to be qualified with the appropriate table name.This is not necessary where a col-
umn name is unique across all tables in the
FROMlist;however,it is good practice to
always qualify column names to avoid ambiguity.If a column name could refer to more
than one table,SQLite will not make the decision.Instead an error is raised as shown in
the following example:
sqlite> SELECT myfield...> FROM table1, table2, table3;SQL error: ambiguous column name: myfieldTo join two tables on a common fieldÑknown as an equi-join because the relation-
ship is an equalityÑthe general syntax isSELECT resultFROM table1, table2
WHERE table1.keyfield1 = table2.keyfield2SQLitesupports outer joins via the
LEFT JOINkeyword,whereby each row in the
left tableÑthe one specified first in the
SELECTstatementÑis combined with a row from
05 067232685x CH03 10/14/04 11:47 AM Page 63
64Chapter 3SQLite Syntax and Use
the right table.Where the join condition does not produce a match between the two
tables,rows from the left table are still returned but with
NULLvalues for each column
that should be in the right table.
The general syntax for aLEFT JOINis as follows:
SELECT resultFROM table1
LEFT [OUTER] JOIN table2
ON table1.keyfield1 = table2.keyfield2
[WHERE expr]The LEFT JOINoperator can be written as
LEFT OUTER JOINas a matter of prefer-
OUTERkeyword is optional.
GROUP BY and Aggregate Functions
TheGROUP BYclause is used to aggregate data into a single row where the value of one
or more specified columns is repeated.This feature can be used to reduce the number of
records to only find unique values of a column,but is particularly useful when used in
conjunction with the SQLiteÕs aggregate functions.
The GROUP BYclause takes a list of expressionsÑusually column names from the
resultÑand aggregates data for each expression.In the
vegetablestable we created pre-
viously we had more than one green vegetable,so grouping on the
colorcolumn willreturn each value only once.
sqlite> SELECT color...> FROM vegetables...> GROUP BY color;color
orangeMore interesting is to use the aggregate function
count()to show how many records
there are for each value of
color:sqlite> SELECT color, count(color)...> FROM vegetables...> GROUP BY color;color count(color)
orange 1Using count(fieldname)will return the number of rows containing a non-
NULLvalue in that field.If you want to return a count of the total number of rows,regardless
count(*)will do this,as the following example shows:
05 067232685x CH03 10/14/04 11:47 AM Page 64
65Anatomy of a SELECT Statementsqlite> CREATE TABLE mytable (...> field1 CHAR,...> field2 INTEGER...> );sqlite> INSERT INTO mytable VALUES ('fooÕ, 5);sqlite> INSERT INTO mytable VALUES ('fooÕ, 14);sqlite> INSERT INTO mytable VALUES ('barÕ, 25);sqlite> INSERT INTO mytable VALUES ('barÕ, 8);sqlite> INSERT INTO mytable VALUES ('barÕ, NULL);sqlite> SELECT field1, count(field2), count(*)...> FROM mytable...> GROUP BY field1;field1 count(field2) count(*)
---------- ------------- ----------
bar 2 3
foo 2 2There are also a number of aggregate functions for performing summary calculations
on grouped data,as shown in the following example:
sqlite> SELECT field1, sum(field2), min(field2), max(field2), avg(field2)...> FROM mytable...> GROUP BY field1;field1 sum(field2) min(field2) max(field2) avg(field2)
---------- ----------- ----------- ----------- -----------
bar 33 8 25 16.5
foo 19 5 14 9.5Table 3.2 lists all the aggregate functions available in SQLite.
avg(column)Returns the mean average of all values in
columncount(column)Returns the number of times that a non-
NULLvalue appears in
columncount(*)Returns the total number of rows in a query,regardless of
max(column)Returns the highest of all values in
column,using the usual sort order
min(column)Returns the lowest of all values in
column,using the usual sort order
sum(column)Returns the numeric sum of all values in
The HAVINGclause is a further condition applied after aggregation takes place.In contrast
to a WHEREclause,which applies a condition to individual elements in a table,
HAVINGisused to restrict records based on the summary value of a grouping.
05 067232685x CH03 10/14/04 11:47 AM Page 65
66Chapter 3SQLite Syntax and Use
To return only rows from the
vegetablestable where there is more than one of the
same color,we can do this:
sqlite> SELECT color, count(*)...> FROM vegetables GROUP BY color...> HAVING count(*) > 1;color count(*)
green 2It is actually not necessary for
count(*)to appear in the result,as shown in the fol-
sqlite> SELECT color...> FROM vegetables GROUP BY color...> HAVING count(*) > 1;color
greenColumn AliasesThe column headings displayed in the output of a
SELECTstatement are usually the same
as the items specified in the resultsection.For a straight column,the name of the col-
umn is displayed.For an expression,however,the expression text is used.
Although the column headings are only displayed in
sqlitewhen .headersis set toon,it is important to know what each columnÕs name is so that all the columns can be
referenced correctly from within a programming API.A column alias is specified with
the ASkeyword to explicitly give a new name to a selected column.
In the following example,we give a column alias to the result of the
gate function.In addition to renaming the column header,we can use the alias name in
the HAVINGclause,which can sometimes aid readability of code.
sqlite> SELECT color, count(*) AS num_colors...> FROM vegetables GROUP BY color...> HAVING num_colors > 1;color num_colors
green 2Attaching to Another Database
.databasescommand lists all the databases that are open for the cur-
rent session.There will always be two databases open after you invoke
database specified on the command line,and
temp,the database used for temporary
05 067232685x CH03 10/14/04 11:47 AM Page 66
67Attaching to Another Database
sqlite> .databases0 main /home/chris/sqlite/demodb
1 temp /var/tmp/sqlite VGazbfyWvuUr29PIt is possible to attach more databases to your current session with the
ATTACH DATA-BASEstatement.This adds a connection to another database without replacing your cur-
rently selected database.
The syntax isATTACH [DATABASE] database-filename AS database-nameThe keyword
DATABASEis optional and is used only for readability,but you must pro-
vide a unique database-nameparameter that will be used to qualify table references,
essential in case more than one database could have the same table name.
Suppose you are working on a new database called
newdband want to access some of
the databases from our demo database from Chapter 2.The following example shows
demodbbeing attached to the current
sqlitesession:$ sqlite newdbSQLite version 2.8.12
Enter ".help" for instructions
sqlite> ATTACH DATABASE demodb AS demodb;sqlite> .databases0 main /home/chris/sqlite/newdb
1 temp /var/tmp/sqlite_VGazbfyWvuUr29P
2 demodb /home/chris/sqlite/demodbAccessing tables from an attached database is straightforwardÑjust prefix any table
name with the database name (the name given after the keyword
AS,not the filename,if
they are different) and a period.
We can perform a query on the
sqlite> SELECT company_name FROM demodb.clients;company_name
Premier Things LtdTables in the
maindatabase can be accessed using their table name alone,or qualified
as main.tablename.If a table name is unique across all databases attached in a particular
session,it does not need to be prefixed with its database name even if it is not in the
maindatabase.However,it is still good practice to qualify all tables when you are work-
ing with multiple databases to avoid confusion.
05 067232685x CH03 10/14/04 11:47 AM Page 67
68Chapter 3SQLite Syntax and Use
NoteThe SQL commands INSERT, UPDATE, SELECT, and DELETEcan all be performed on an attached data-base by using the database name prefix. However,
CREATE TABLEand DROP TABLEcan only takeplace on the maindatabaseÑyou must exit sqliteand begin a new session if you want to manipulatetables from a different database.Note the situation with multi-database transactions here. If a machine or software failure occurs, a transac-tion is only atomic within one database. If more than one database were written to within a single transac-
tion, one database might be committed and the other rolled back in the event of a failure.There is a compile-time limit of 10 attached database files by default.This can be
increased to up to 255 concurrent databases by modifying the following line in
src/sqliteInt.h:#define MAX_ATTACHED 10To detach an attached database,the syntax is simply
DETACH [DATABASE] database-nameManipulating DataNext weÕll look at how records can be added to a database and demonstrate different
ways of using the
INSERTcommand,and examine the syntax of the SQL
Anychange to a SQLite database must take place within a
Ña block of one or
more statements that alter the database in some way.Transactions are the way in which a
robust database system ensures that either all or none of the requests to alter the database
is carried out;it can never be just partially completed.This property of a database is
called atomicity.Whenever an
DELETEcommand is issued,SQLite will begin a
new transaction unless one has already been started.An implicit transaction lasts only for
the duration of the one statement but ensures that,for instance,an
UPDATEaffectingmany rows of a large table will always carry out the action on every row orÑin the
unlikely event of a system failure while processing this commandÑnone of them.The
database will not reflect a change to any row until every row has been updated and the
transaction closed.A transaction can be started from SQL if you want to make a series of changes to the
database as one atomic unit.This is the syntax of the
BEGIN TRANSACTIONstatement:BEGIN [TRANSACTION [name]] [ON CONFLICT conflict-algorithm]The transaction name is optional and,currently,is ignored by SQLite.The facility to
provide a transaction name is included for future use if the ability to nest transactions is
05 067232685x CH03 10/14/04 11:47 AM Page 68
69Manipulating Dataadded.Currently only one transaction can be open at a time.In fact the keyword
TRANSACTIONis also optional,but is included for readability.
An ON CONFLICTclause can be specified to override the default conflict resolution
algorithm specified at the table level,but can be superseded itself by the
ORclause of anINSERT,UPDATE,or
DELETEstatement.To end a transaction and save changes to the database,use
optional transaction name may be specified.To abort a transaction without any of the
changes being stored,use
ROLLBACK TRANSACTION.Inserting Data
Thereare two versions of the syntax for the
INSERTstatement,depending on where the
data to be inserted is coming from.
The first syntax is the one we have already used in Chapter 2,to insert a single row
from values provided in the statement itself.The second version is used to insert a dataset
returned as the result of a
SELECTstatement.INSERT Using VALUES
Thesyntax for a single-row insert using the
VALUESkeyword and a list of values provid-
ed as part of the statement is as follows:
INSERT [OR conflict-algorithm]INTO [database-name .] table-name [(column-list)]
VALUES (value-list)Although all our examples so far have included a
column-list,it is actually optional.
value-listis assumed to contain one value for
each column in the table,in the order they appear in the schema.
This can be a useful shortcut when you are adding data;for instance because we
column-listis a name and then a color,a record can be inserted into the
vegetablestable simply using this format:
sqlite> INSERT INTO vegetables VALUES ('mushroomÕ, 'whiteÕ);However if the schema of the table is not what you are expecting,the
INSERTwillfail with an error.SQLite would not make any assumption as to which columns you are
Because SQLite does not have an
ALTER TABLEcommand,it is much harder to
change a schema after a table has been created than it is with other database engines that
include this command.WeÕll see a workaround for
ALTER TABLEin the following exam-
ple,and if for any reason the
vegetablestable had been expanded to include three
INSERTstatement would produce this error:
sqlite> INSERT INTO vegetables VALUES ('mushroomÕ, 'whiteÕ);SQL error: table vegetables has 3 columns but 2 values were supplied05 067232685x CH03 10/14/04 11:47 AM Page 69
70Chapter 3SQLite Syntax and Use
Therefore it is good practice to always include the
column-listin an INSERTstate-mentÑalso known as performing a
.The ORkeyword is used to specify a conflict resolution algorithm in the same way we
saw for the
CREATE TABLEstatement.The list of algorithms and their behavior is identi-
cal,but the keyword
ORis used instead of ON CONFLICTto give a more natural-sounding
syntax.The conflict algorithm in the
ORclause of an INSERTstatement has the highest prece-
dence possible,and will override any other setting present at the table or transaction
INSERT Using SELECT
Thesyntax to insert the result of a
SELECTquery into another table is as follows:
INSERT [OR conflict-algorithm]INTO [database-name .] table-name [(column-list)] select-statementThe select-statementshould return a dataset with the same number and order as
the columns specified in the column-list(or every column in the destination table if
no column-list is supplied).The full syntax of the
SELECTstatement is available,and any
number of rows can be returned.
As with INSERT ... VALUES,the
column-listis optional but including it is highly
Updating DataThesyntax of the
UPDATEstatement in SQLite is as follows:
UPDATE [OR conflict-algorithms] [database-name .] table-nameSET assignment [, assignment]*
[WHERE expr]One or more
assignmentscan be performed within the same statement upon the
same subset of data,defined by the optional
WHEREclause.An assignment is defined as
column-name = exprAlthough the WHEREclause is not required,it is usually desirable.The following exam-
ple would assign the value of
colorto greenfor every row in the table,when in fact we
probably only meant to update one or a few records.
sqlite> UPDATE vegetables...> SET color = 'greenÕ;The WHEREclause can be as simple or complex as necessary,and all the conditional
elements that can be used in the WHEREclause of a SELECTstatement can be used here.
It is not logical to join two or more tables when performing an
subselects can be used in the WHEREclause,as in the following example:
05 067232685x CH03 10/14/04 11:47 AM Page 70
71Manipulating Datasqlite> UPDATE mytable...> SET myfield = 'somevalueÕ...> WHERE mykey IN (...> SELECT keyfield...> FROM anothertable...> );The ORkeyword is used in an
UPDATEstatement to specify a conflict resolution algo-
rithm with the highest precedence possible,in the same way as with an
INSERT.Deleting DataTheDELETEstatement is used to remove rows from a database.Its syntax is
DELETE FROM [database-name .] table-name [WHERE expr]As with the UPDATEstatement,the
WHEREclause is optional but is usually desiredÑ
DELETEon a table with no
WHEREclause will empty the table.No
column-listis required for a
DELETEbecause the operation affects the entire row.
The WHEREclause can use the ANDand ORoperators to combine conditions and can
use subselects to perform a
DELETEoperation conditional on the results of another query.
The following example modifies a query from Chapter 2 to remove records from the
timesheetstable where the
project_codefield does not correspond to a key in the
sqlite> DELETE FROM timesheets...> WHERE project_code NOT IN (...> SELECT code...> FROM projects...> );Altering a Table Schema
ALTER TABLEstatement in SQLite;instead a table must be dropped and re-
created with a new field added,with any data that you want to preserve extracted before
the table is dropped and reloaded into the new structure.
A temporary table is the ideal place to hold such data,and the
CREATE TABLE ... ASsyntax gives us a very easy way to create a copy of an existing table.The syntax is simply
CREATE [TEMP | TEMPORARY TABLE] table-name AS select-statementLetÕs suppose we want to add a new descriptive column to our
without losing the data we have already created.The first step is to take a copy of the
existing vegetablestable to a new temporary table.
sqlite> CREATE TEMPORARY TABLE veg_temp...> AS SELECT * FROM vegetables;05 067232685x CH03 10/14/04 11:47 AM Page 71
72Chapter 3SQLite Syntax and Use
However,only the field specification has been copied when a table is created this way.
The schema of the new table does not include any data type names or column con-
straints.It is not possible to give a set of column definitions when using
CREATE TABLE... ASin SQLite.
sqlite> .schema veg_tempCREATE TEMP TABLE veg_temp(name,color);Compare this to the schema of the original
vegetablestable,which weÕll need for
re-creating the table with the new field:
sqlite> .schema vegetablesCREATE TABLE vegetables (
name CHAR NOT NULL,
color CHAR NOT NULL DEFAULT 'green'
);So now we can safely drop the old
vegetablestable and re-create it with our new
field:sqlite> DROP TABLE vegetables;sqlite> CREATE TABLE vegetables (...> name CHAR NOT NULL,...> color CHAR NOT NULL DEFAULT 'greenÕ,...> description CHAR...> );Finally,reinstate the copied data from the temporary table using the
INSERT ...SELECTsyntax:sqlite> INSERT INTO vegetables (name, color)...> SELECT name, color FROM veg_temp;Loading Data from a File
TheCOPYcommand in SQLite was based on a similar command found in PostgreSQL
and as a result is designed to read the output of the
pg_dumpcommand to facilitate data
transfer between the two systems.
COPYcan also be used to load data from most delimited text file formats
into SQLite.It has the following syntax:
COPY [OR conflict-algorithm] [database-name .] table-name FROM filename[USING DELIMITERS delim]The destination table
table-namemust existÑthough it need not be emptyÑbefore
the COPYoperation is attempted,and either
filenamemust be in the current directory
or a full path given.
Each line in the input file will become a record in the table,with each column sepa-
rated by a tab character,unless a different delimiter character is specified in the
USINGDELIMITERSclause.05 067232685x CH03 10/14/04 11:47 AM Page 72
73IndexesIf a tabÑor the specified delimiterÑappears within a data column,it must be escaped
with a backslash character.Thebackslash itself can appear in the data if it is escaped itself;
in other words it will appear as two consecutive backslash characters.
The special character sequence \Nin the data file can be used to represent a
Tab is used to separate columns in the output of
pg_dump,so it is the default delim-
iter for the COPYcommand.Another popular format is comma-separated values (CSV).
Listing 3.1 shows a comma-separated data file that can be loaded into the three-column
Listing 3.1vegetables.csvcucumber,green,Long green salad vegetablepumpkin,orange,Great for Halloweenavocado,green,Can't make guacamole without itThe COPYcommand to load this data file into SQLite issqlite> COPY vegetables FROM 'vegetables.csvÕ...> USING DELIMITERS ',Õ;You can instruct
COPYto read data from the standard input stream instead of a file by
using the keyword
STDINinstead of a filename.A blank line,or a backslash followed by a
period,is used to indicate the end of the input.
COPYpermits an overriding conflict resolution algorithm to be specified after the
The subject of keys and indexes and how they can affect the performance of your data-
base will be addressed in Chapter 4,ÒQuery Optimization,Óbut first we will examine the
syntax for creating and finding information on table indexes.
Creating and Dropping Indexes
TheCREATE INDEXcommand is used to add a new index to a database table,using this
syntax:CREATE [UNIQUE] INDEX index-nameON [database-name .] table-name (column-name [, column-name]*)
[ON CONFLICT conflict-algorithm]The index-nameis a user-provided identifier for the new index and must be unique
across all database objects.It cannot take the same name as a table,view,or trigger.A
popular naming convention is to use the table name and the column name(s) used for
the index key separated by an underscore character.
05 067232685x CH03 10/14/04 11:47 AM Page 73
74Chapter 3SQLite Syntax and Use
To add an index to the color column of the
vegetablestable,we would use the fol-
sqlite> CREATE INDEX vegetables_color...> ON vegetables(color);The syntax of column-nameallows for a sort order to be given after each column
ASCor DESC;however,currently in SQLite this is ignored.At the present
time,all indexes are created in ascending order.
Removing an index is done with reference to the identifier given when it was creat-
ed,which you can always find by querying the
sqlite_mastertable if you cannot
sqlite> SELECT * FROM sqlite_master...> WHERE type = 'indexÕ;type = index
name = vegetables_colortbl_name = vegetables
rootpage = 10sql = CREATE INDEX vegetables_colorON vegetables(color)TheDROP INDEXcommand works as you might expect:
sqlite> DROP INDEX vegetables_color;DonÕt worry if you misread the
sqlite_masteroutput and use the table name
instead of the index name.SQLite only allows you to drop indexes with the
DROP INDEXcommand and tables with the
DROP TABLEcommand.sqlite> DROP INDEX vegetables;SQL error: no such index: vegetablesUNIQUE Indexes
TheUNIQUEkeyword is used to specify that every value in an indexed column is unique.
Where an index is created on more than one column,every permutation of the column
values has to be unique,even though the same value may appear more than once in its
Since we have already inserted several vegetables of the same color into the table,
SQLite will give an error if we attempt to create a unique index on the
colorfield.sqlite> CREATE UNIQUE INDEX vegetables_color...> ON vegetables(color);SQL error: indexed columns are not uniqueThe ON CONFLICTclause at the index level is only relevant for a
wise,there will never be a conflict on the data it applies to.The conflict resolution algo-
rithm is used when an
COPYstatement would cause the unique
05 067232685x CH03 10/14/04 11:47 AM Page 74
75Viewsconstraint of the index to be violated.It cannot be used in the preceding
CREATEUNIQUE INDEXstatement to force a unique index onto a column containing multiple
The default conflict resolution algorithm is
ABORT,and the same list of algorithms is
permitted for indexes as in the
A view is a convenient way of packaging a query into an object that can itself be used in
the FROMclause of a SELECTstatement.Creating and Dropping Views
The syntax for CREATE VIEWis shown next.
CREATE [TEMP | TEMPORARY] VIEW view-name AS select-statementThe select-statementcan be as simple or as complex as necessary;it could return
the subset of a single table based on a conditional
WHEREclause,or join many tables
together to form a single object that can be more easily referenced in SQL.
To drop a view,simply use the
DROP VIEWstatement with the view-namegiven when
it was created.
A view is not a table.You cannot perform an
DELETEon aview,but if the data in one of the source tables changes,those changes are reflected
instantly in the view.
The following example shows a view based on the demo database tables
employeesandemployee_ratesusing a query that returns the current rate of pay for each employee.
sqlite> CREATE VIEW current_pay AS...> SELECT e.*, er.rate...> FROM employees e, employee_rates er...> WHERE e.id = er.employee_id...> AND er.end_date IS NULL;We can then query the new view directly,even adding a new condition in the
sqlite> SELECT * FROM current_pay...> WHERE sex = 'MÕ;id first_name last_name sex email rate
---- ---------- ---------- --- ------------------------ ------
101 Alex Gladstone M email@example.com 30.00
103 Colin Aynsley M firstname.lastname@example.org 25.0005 067232685x CH03 10/14/04 11:47 AM Page 75
76Chapter 3SQLite Syntax and Use
The column names in a view are the column names from the table.Where an expres-
sion is used,SQLite will faithfully reproduce the expression as the column heading.
sqlite> CREATE VIEW veg_upper AS...> SELECT upper(name), upper(color)...> FROM vegetables;sqlite> SELECT * FROM veg_upper LIMIT 1;upper(name)|upper(color)
CARROT|GREENHowever,the column in the view cannot actually be called
in the following example,SQLite will attempt to evaluate the
upper()function on thenonexistent namecolumn.sqlite> SELECT upper(name) from veg_upper;SQL error: no such column: nameColumn aliases can be used to give an explicit name to a column so that they can be
referenced within a subsequent query.
sqlite> CREATE VIEW veg_upper AS...> SELECT upper(name) AS uppername, upper(color) AS uppercolor...> FROM vegetables;sqlite> SELECT * FROM veg_upper...> WHERE uppercolor = 'ORANGEÕ;uppername|uppercolor
PUMPKIN|ORANGENoteWhen a view includes two columns with the same nameÑwhether it is the same column selected twicefrom one table, or once each from two tables that happen to share a column nameÑSQLite will modify the
column names in the view unless aliases are used. A duplicate column will be suffixed with _1the firsttime it appears, _2the second time, and so on.SQLite does not validate the
select-statementSQL in CREATE VIEW.You will only
know if there is an error in the
SELECTwhen you come to query the new view.The
SELECTstatement is effectively substituted into the query at the point where
view-nameappears,so the errors displayed may not appear to reflect the query you
typed.The following example creates a view with a deliberate errorÑthere is no column
entitled shapein vegetablesÑand shows that the error is not detected until you query
sqlite> CREATE VIEW veg_error AS...> SELECT shape FROM vegetables;sqlite> SELECT * from veg_error;SQL error: no such column: shape05 067232685x CH03 10/14/04 11:47 AM Page 76
is an event-driven rule on a database,where an operation is initiated when some
other transaction (event) takes place.Triggers may be set to fire on any
DELETE,INSERT,or UPDATEon a particular table,or on an
UPDATE OFparticular columns within a table.
Creating and Dropping Triggers
The syntax to create a trigger on a table is as follows:
CREATE [TEMP | TEMPORARY] TRIGGER trigger-name[BEFORE | AFTER] database-event ON [database-name .]table-name
trigger-actionThe trigger-nameis user-specified and must be unique across all objects in the data-
baseÑit cannot share the same name as a table,view,or index.
The trigger can be set to fire either
to pre-empt the transaction and perform its action just before the
DELETEtakes place,or to wait until the operation has completed and then immediately
carry out the required action.
If the database-eventis specified as UPDATE OF column-list
,it will create a trigger
that will fire only when particular columns are affected.The trigger will ignore changes
that do not affect one of the listed columns.The trigger-actionis further defined as
[FOR EACH ROW | FOR EACH STATEMENT] [WHEN expression]BEGINtrigger-step; [trigger-step;] *ENDAt present only
FOR EACH ROWtriggers are supported,so each trigger stepÑwhich
may be an
DELETEstatement or SELECTwith a function expressionÑ
is performed once for every affected row in the transaction that causes the trigger to fire.
The WHENclause can be used to cause a trigger to fire only for rows for which the
WHENclause is true.The
WHENclause is formed in the same way as the
WHEREclause in aSELECTstatement.The WHENclause and any
trigger-stepsmay reference elements of the affected row,
both before and after the trigger action is carried out,as
UPDATEaction both OLDand NEWare valid.An
INSERTevent can only provide a reference to the
OLDis valid for
An ON CONFLICTclause can be specified in a trigger-step;however,any conflict
resolution algorithm specified in the statement that causes the trigger to fire will over-
As you might expect,the syntax to drop a trigger is simply
DROP TRIGGER [database-name .] table-name05 067232685x CH03 10/14/04 11:47 AM Page 77
78Chapter 3SQLite Syntax and Use
If you forget the name of a trigger,you can query
sqlite_masterusing type ='trigger'to find all the triggers on the current database.
In the last chapter we mentioned that triggers could be used to implement a cascading
delete,so that rows from a table that referenced a foreign key would also be deleted if
the foreign key were deleted from its own table.The trigger in the following example
shows how this could be implemented on the demo database to delete entries from the
timesheetstable if the foreign key
project_codeis deleted from the
sqlite> CREATE TRIGGER projcode_cascade...> AFTER DELETE ON projects...> BEGIN...> DELETE FROM timesheets WHERE project_code = OLD.code;...> END;Similarly,we could create a trigger that maintains data integrityÑif the project code
changes in the projectstable,the child records in
timesheetswill be updated toreflect the new foreign key value.
sqlite> CREATE TRIGGER projcode_update...> AFTER UPDATE OF code ON projects...> BEGIN...> UPDATE timesheets...> SET project_code = NEW.code...> WHERE project_code = OLD.code;...> END;A quick test verifies that this trigger is working as we want it to:
sqlite> UPDATE projects...> SET code = 'NEWCODEÕ...> WHERE code = 'ABCCONSÕ;sqlite> SELECT count(*)...> FROM timesheets...> WHERE project_code = 'NEWCODEÕ;count(*)
3Interrupting a Trigger
trigger-stepsit is possible to interrupt the command that caused the trig-
ger to fire and execute one of the conflict resolution algorithms available in an
ONCONFLICTclause.This is done using the
RAISE()function,which can be invoked using a
SELECTstatement as one of the following:
05 067232685x CH03 10/14/04 11:47 AM Page 78
RAISE (ABORT, error-message) |RAISE (FAIL, error-message) |
RAISE (ROLLBACK, error-message) |
RAISE (IGNORE)Issuing an ABORT,FAIL,or
ROLLBACKwithin a trigger will cause the transaction to exit
and take the relevant action,and the
error-messageparameter is returned to the user.
We could use this behavior to prevent a project code from being deleted from the
projectstable while rows exist in
timesheetsthat use it as a foreign key,rather than
the rather destructive cascading delete.
sqlite> CREATE TRIGGER projcode_rollback...> BEFORE DELETE ON projects...> WHEN OLD.code IN (...> SELECT project_code FROM timesheets...> )...> BEGIN...> SELECT RAISE(ROLLBACK, 'Timesheets exist for that project codeÕ);...> END;An attempted DELETEwill produce an error:
sqlite> DELETE FROM projects WHERE code = 'NEWCODEÕ;SQL error: Timesheets exist for that project codeWe can also verify that the
DELETEtransaction was rolled back.
sqlite> SELECT * FROM projects...> WHERE code = 'NEWCODEÕ;code client_id title start_date due_date
---------- ---------- ------------------- ---------- ----------
NEWCODE 502 Ongoing consultancy 20030601Using RAISE(IGNORE)causes the current trigger to be abandoned;however,any
changes made up to that point will be saved and if the trigger was fired as the result of
another trigger,that outer triggerÕs execution will continue.
Creating a Trigger on a View
Thesyntax for using triggers on views is slightly different than with tables.This func-
tionality is provided as a way of intercepting
DELETEoperations ona view,usually to simulate that action by executing the actual steps necessary to make the
requested data change appear in the view.Because a view may join two or more tables,a
number of steps may be required.
The syntax for creating a trigger on a view is
CREATE [TEMP | TEMPORARY] TRIGGER trigger-nameINSTEAD OF database-event ON [database-name .] view-name
trigger-action05 067232685x CH03 10/14/04 11:47 AM Page 79
80Chapter 3SQLite Syntax and Use
UPDATE OF column-list,and the
trigger-actionis one or more SQL operations contained between the
BEGINand END.Working with Dates and Times
In our sample database we have chosen to use integers for columns that store a date
value,represented by the format
YYYYMMDD.This format is fairly readable and,because the
most significant part (the year) comes first,allows arithmetic comparisons to be per-
formed.For instance just as February 29th 2004 is earlier than March 1st,
20040229is asmaller number than
20040301.This technique is not without its limitations.First,there is no validation on the values
stored.Although February 29th is a valid date in the leap year 2004,it does not exist
three years out of four and the value
20050229is not a real date,yet could still be stored
in the integer column or compared to a real date.
In fact even if you used a trigger to make the number eight digits long and also fall
within a sensible year range,there are many values that could still be stored that do not
represent dates on the calendar.Very strict checking would be required in your applica-
tion program to ensure such date information was valid.
Similarly,you cannot perform date arithmetic using integer dates.Although
20040101+ 7gives a date seven days later,
20040330 + 7would give a number that looks like
We have not even looked at a data type to store a time value yet,but the same limita-
tions apply if a numeric field is used.SQLite contains a number of functions that allow
you to work with both dates and times stored as character strings,allowing you to
manipulate the values in useful ways.
Valid Timestring Formats
SQLiteis fairly flexible about the format in which you can specify a date and/or time.
The valid time string formats are shown in the following list:
nYYYY-MM-DDnYYYY-MM-DD HH:MMnYYYY-MM-DD HH:MM:SSnYYYY-MM-DD HH:MM:SS.SSSnHH:MMnHH:MM:SSnHH:MM:SS.SSSnnownDDDD.DDDD05 067232685x CH03 10/14/04 11:47 AM Page 80
81Working with Dates and Times
For the format strings that only specify a time,the date is assumed to be
2000-01-01.Where no time is specified,midday is used.Simply using the string
nowtells SQLite touse the current date and time.
The format string
DDDD.DDDDrepresents a Julian day numberÑthe number of days
since noon on November 24,4714 BC,Greenwich Mean Time.SQLite uses Julian date
format internally to manipulate date and time values.
Displaying a Formatted Date and Time
Thecore date and time function in SQLite is
strftime(),which has the following pro-
totype:strftime(format, timestring, modifier, modifier, ...)This function is based upon the C function strftime()and the format parameter
will accept most,although not all,of the same conversion specifiers.The following
example shows how a date can be reformatted to
strftime().sqlite> SELECT strftime('%m/%d/%YÕ, '2004-10-31Õ);10/31/2004Table 3.3 lists the conversions that can be performed by SQLite on a timestring.
Date and Time Conversion Specifiers
%dDay of month,01-31
%jDay of year,001-366
%JJulian day number,
%sSeconds since 1970-01-01 (unix epoch)%SSeconds,00-59
%wDay of week,0-6 (0 is Sunday)
%WWeek of year,01-53
symbolDate and Time Modifiers
Given one or more optional
modifierarguments,strftime()can perform a calculation
on the date given in
timestring.05 067232685x CH03 10/14/04 11:47 AM Page 81
82Chapter 3SQLite Syntax and Use
To add or subtract a period of time,the
days,hours,minutes,seconds,monthsandyearsmodifiers can be used,as shown in these examples:
sqlite> SELECT strftime('%Y-%m-%dÕ, '2004-10-31Õ, '+7 daysÕ);2004-11-07sqlite> SELECT strftime('%H:%MÕ, '22:00Õ, '+12 hoursÕ);10:00sqlite> SELECT strftime('%Y-%m-%d %H:%M:%SÕ,'2004-01-01 00:00:00Õ, '-1 secondÕ, '+1 yearÕ);2004-12-31 23:59:59NoteThe modifier keywords can be written as either singular or plural. In the last of the preceding examples, weused 1 secondand 1 yearrather than 1 secondsand 1 yearsfor readability. SQLite does not
understand English grammar, so either is always acceptable.
In these examples we have used the same output format as the original
timestringto return the date information in a format that can be recognized by SQLite.You should
only format the date differently when you want to display it in your application in a par-
To save having to enter the same
formatstrings repeatedly when working with dates,
SQLite provides four convenience functions that call
strftime()with predefined for-
mats.Use date()to return a date with the format string
%Y-%m-%dandtime()to return a
time as %H:%S.The function
datetime()returns the date and time using these two for-
julianday()uses the %Jformat specifier to return the Julian
The arguments to all four functions are the same as
strftime()except that theformatargument is omitted.The following example uses
datetime()to produce a
more concise SQL statement:
sqlite> SELECT datetime('2004-01-01 00:00:00Õ, '-1 secondÕ, '+1 yearÕ);2004-12-31 23:59:59Other modifiers allow you to adjust a date or time to the nearest significant value.
Specifying start of month,start of year,or start of day will decrease the value given in
timestring to midnight on the first of the month or year,or on that day respectively.
When executed on any day during 2004,the
start of yearmodifier returns
2004-01-01,as shown in the following example:
sqlite> SELECT datetime('nowÕ, 'start of yearÕ);2004-01-01 00:00:0005 067232685x CH03 10/14/04 11:47 AM Page 82
83SQL92 Features Not SupportedModifiers are applied to
timestringin the order they appear in the statement,as
shown in the following example.Note that had the second statement been executed on
the last day of the month,the result would have been differentÑthe start of the follow-
ing month would have been returned.
sqlite> SELECT datetime('nowÕ, 'start of monthÕ, '+1 dayÕ);2004-07-02 00:00:00sqlite> SELECT datetime('nowÕ, '+1 dayÕ, 'start of monthÕ);2004-07-01 00:00:00Any number of modifiers can be combined,giving you considerable power when
working with dates and times.For instance,the last day of the current month can be
found using three modifiers in succession.
sqlite> SELECT date('nowÕ, '+1 monthÕ, 'start of monthÕ, '-1 dayÕ);2004-07-31Handling Different Time Zones
The locale settings of your system will determine which time zone is used when display-
ing dates and times;however,the underlying system clock will use Coordinated
Universal Time (UTC),also known as Greenwich Mean Time (GMT)Greenwich Mean
Time (GMT).Your time zone setting will specify a number of hours to be added to or
subtracted from the UTC value to arrive at the correct local time.
For instance,to find the local time in New York you have to subtract five hours from
UTC,or four hours during daylight savings time.Even in Greenwich,the local time is
UTC + 1 hour during the summer months.
To convert between UTC and local time values when formatting a date,use the
utcor localtimemodifiers.The following examples were run on a system with the time-
zone set to Eastern Standard Time (UTC Ð 5 hours).
sqlite> SELECT time('12:00Õ, 'localtimeÕ);2000-01-01 07:00:00sqlite> SELECT time('12:00Õ, 'utcÕ);2000-01-01 17:00:00SQL92 Features Not Supported
We finish this chapter on SQLiteÕs implementation of the SQL language by looking at
features of the ANSI SQL92 standard that are not currently supported by SQLite.
nAlthough the CREATE TABLEsyntax permits an optional
CHECKclause to be pres-
CHECKconstraint is not enforced.
FOREIGN KEYare allowable in a
this currently has no effect.
05 067232685x CH03 10/14/04 11:47 AM Page 83
84Chapter 3SQLite Syntax and Use
nSubqueries must return a static data set,and they may not refer to variables in the
outer queryÑalso known as correlated subqueries.
nAll triggers are currently
FOR EACH ROW,even if
FOR EACH STATEMENTis specified.
nViews are read-only,even when they select only from one table.However,an
INSTEAD OFtrigger can fire on an attempted
DELETEto a view
and deal with the transaction in the desired manner.
nINSTEAD OFtriggers are allowed only on views,not on tables.
nRecursive triggersÑtriggers that trigger themselvesÑare not supported.
nThe ALTER TABLEstatement is not present;instead a table must be dropped and
re-created with the new schema.
nTransactions cannot be nested.
cannot be used.However,this can be achieved by
selecting a count()from a subselect of the desired table that uses the
nAll outer joins must be written as
LEFT OUTER JOIN.RIGHT OUTER JOINandFULL OUTER JOINare not recognized.
nThe GRANTand REVOKEcommands are meaningless in SQLiteÑthe only permis-
sions applicable are those on the database file itself.
05 067232685x CH03 10/14/04 11:47 AM Page 84