Does SQLite support foreign keys?

Short Answer:
FOREIGN KEY constraints are parsed but are not enforced. However, the equivalent constraint enforcement can be achieved using triggers.

A. Foreign Key (FK)
FK is a referential constraint between two tables. A FK is a field(s) that reference to the primary key of another table. The purpose of the FK is to ensure referential integrity of the data. The values in one row of the referencing columns must occur in a single row in the referenced table. Thus, a row in the referencing table cannot contain values that don't exist in the referenced table (except potentially NULL).

This way references can be made to link information together and it is an essential part of database normalization. Multiple rows in the referencing table may refer to the same row in the referenced table. Most of the time, it reflects the one (master table, or referenced table) to many (child table, or referencing table) relationship.

FK is defined in DDL (Data Definition Language), so we do not code in our application. Once we define FK constraints, they must remain true while inputting/modifying/deleting data in the database. Constraints are used to enforce table rules and prevent data dependent deletion (enforce database integrity).


B. Enforce Foreign Key Constraint using PRAGMA
The PRAGMA statement is a SQL extension specific to SQLite and used to modify the operation of the SQLite library or to query the SQLite library for internal (non-table) data. The foreign_keys pragma controls the enforcement of foreign key constraints in all of the attached databases. If set to off, foreign key constraints are ignored. The default value is off.

For many years SQLite would parse foreign key constraints, but was unable to enforce them. When native support for foreign key constraints was finally added to the SQLite core, enforcement was left off by default to avoid any backwards compatibility issues.

This default value may change in future releases. To avoid problems, it is recommended that an application explicitly set the pragma one way or the other. This pragma cannot be set when there is an active transaction in progress.

1. Parent table: We define Primary Key
CREATE TABLE parentorder (
jmid VARCHAR(16) NOT NULL PRIMARY KEY,
jmdate DATE NOT NULL,
jmsupp VARCHAR(6) NOT NULL);

2. Child table: We define a reference to primary key above
CREATE TABLE childorder (
jm2id VARCHAR(16) NOT NULL CONSTRAINT fk_childorder_id REFERENCES parentorder(jmid),
jm2no VARCHAR(3) NOT NULL,
jm2itemid VARCHAR(6) NOT NULL,
jm2qty INTEGER NOT NULL,
PRIMARY KEY (jm2id,jm2no));

Test them in SQLite
sqlite>select sqlite_version();
3.6.23.1
sqlite> insert into parentorder values ('A1','2010-05-08','SUP1');
sqlite> insert into childorder values ('A1','P','CODE1', 10.5);
sqlite> insert into childorder values ('A1','V','CODE2', 11);

here we try with unexisting key in the primary key of parentorder table.
sqlite> insert into childorder values ('A2','X','CODE3', 12);

We see, FK constraint does not work, because the data A2 successfully inserted! It should raise an error for A2 is not exist in parent table.

Let's continue...with the PRAGMA statement
sqlite> PRAGMA foreign_keys;
0

Here we enforce the foreign key constraint to ON by using PRAGMA statement
sqlite> PRAGMA foreign_keys=1;
sqlite> update childorder set jm2id='A3' WHERE jm2id='A2';
Error: foreign key constraint failed

Look! We could not change A2 to A3, it means the FK constraint works!

sqlite> update childorder set jm2id='A1' WHERE jm2id='A2';
sqlite> select * from childorder;
A1 P CODE1 10.5
A1 V CODE2 11
A1 X CODE3 12

sqlite> select * from parentorder;
A1 2010-05-08 SUP1

sqlite> update childorder set jm2id='A3' WHERE jm2id='A1';
Error: foreign key constraint failed

C. Using Pragma in Program
using C#:
conn = new SQLiteConnection(connString);
conn.Open();
const string sqlString = "PRAGMA foreign_keys = ON;";
SQLiteCommand command = new SQLiteCommand(sqlString, conn); command.ExecuteNonQuery();

D. Trigger
Maintain FK constraints in above scenario might be not simple as it should be, such as enforce FK constraint each time we use database or when using application through connection string, or set the pragma in SQLite command line. For these reason, below we implemet triggers for FK constraints. See more.

-- Foreign Key Preventing insert
CREATE TRIGGER fki_childorder_jm2id_parentorder_jmid
BEFORE INSERT ON [childorder]
FOR EACH ROW
BEGIN
SELECT RAISE(ROLLBACK, 'insert on table "childorder" violates foreign key constraint "fki_childorder_jm2id_parentorder_jmid"')
WHERE (SELECT jmid FROM parentorder WHERE jmid = NEW.jm2id) IS NULL;
END;

-- Foreign key preventing update
CREATE TRIGGER fku_childorder_jm2id_parentorder_jmid
BEFORE UPDATE ON [childorder] FOR EACH ROW
BEGIN
SELECT RAISE(ROLLBACK, 'update on table "childorder" violates foreign key constraint "fku_childorder_jm2id_parentorder_jmid"')
WHERE
(SELECT jmid FROM parentorder WHERE jmid = NEW.jm2id) IS NULL;
END;

-- Foreign key preventing delete
CREATE TRIGGER fkd_childorder_jm2id_parentorder_jmid
BEFORE DELETE ON parentorder
FOR EACH ROW
BEGIN
SELECT RAISE(ROLLBACK, 'delete on table "parentorder" violates foreign key constraint "fkd_childorder_jm2id_parentorder_jmid"')
WHERE
(SELECT jm2id FROM childorder WHERE jm2id = OLD.jmid) IS NOT NULL;
END;

E. Testing Trigger Code
sqllite>.exit
C:\sqllite3>sqlite tesdb
SQLite version 3.6.23.1

sqlite> copy-paste fki_childorder_jm2id_parentorder_jmid trigger above!
sqlite> insert into childorder values ('A1ERR','P','CODE1', 10.5);
Error: insert on table "childorder" violates foreign key constraint "fki_childorder_jm2id_parentorder_jmid"

sqlite> do the same for fku_childorder_jm2id_parentorder_jmid!
sqlite> update childorder set jm2id='TESTING';
Error: update on table "childorder" violates foreign key constraint "fku_childorder_jm2id_parentorder_jmid"

sqlite> do the same for fkd_childorder_jm2id_parentorder_jmid!
sqlite> delete from parentorder;Error: delete on table "parentorder" violates foreign key constraint "fkd_childorder_jm2id_parentorder_jmid"

--This pragma returns one row for each foreign key that references a column in the argument table.
sqlite> pragma foreign_key_list('childorder');
0 0 parentorder jm2id jmid NO ACTION NO ACTION NONE


F. Utility
1. Use this utility to generate triggers to enforce foreign key constraints with sqlite, operates on database schema and understands most CREATE TABLE syntax. Win32 and linux x86 binaries included, public domain source code.

2. The triggers code above is generated using this web tool. You could try by copy-paste the two Create Table statement above (B.1 and B.2) then click Submit.


G. Trigger vs Constraint
Constraint is a rule define in DDL statement (Foreign Key, Primary Key, etc).
Trigger is a rule define using DML statement.
Both are useful in special situation.

Using triggers we could define complex processing logic, and also support all of the functionality of constraints; however, DML triggers are not always the best method for a given feature.

Entity integrity should always be enforced at the lowest level by indexes that are part of PRIMARY KEY and UNIQUE constraints or are created independently of constraints. Domain integrity should be enforced through CHECK constraints, and referential integrity (RI) should be enforced through FOREIGN KEY constraints, assuming their features meet the functional needs of the application. DML triggers are most useful when the features supported by constraints cannot meet the functional needs of the application.

Each RDBMS has their way for implementation of constraints and triggers, and we also know that there is SQL standards to meet. For constraints and triggers,  I keep these guidance:
-FK constraints can validate a column value only with an exact match to a value in another column, unless the REFERENCES clause defines a cascading referential action.
-Constraints can communicate about errors only through standardized system error messages.
-DML triggers can cascade changes through related tables in the database; however, these changes can be executed more efficiently through cascading referential integrity constraints.
-DML triggers can disallow or roll back changes that violate referential integrity, thereby canceling the attempted data modification. Such a trigger might go into effect when you change a foreign key and the new value does not match its primary key. However, FOREIGN KEY constraints are usually used for this purpose.
-If constraints exist on the trigger table, they are checked after the INSTEAD OF trigger execution but prior to the AFTER trigger execution. If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger is not executed.

Wish that in the next officials release of SQLite, the foreign keys constraints come with default on.

SQLite: ROWID and VACUUM

A. ROWID
Every row of every SQLite table has a 64-bit signed integer key that is unique within the same table. This integer is called "rowid". The rowid is the actual key used in the B-Tree that implements an SQLite table. Because rows are stored in rowid order, a rowid is a candidate for a primary key, as it can uniquely identify a record within a table...

Below we see how it works:

1. WITHOUT Primary Key Keyword
sqlite>CREATE TABLE t1 (name TEXT);
sqlite>insert into t1 values ('a');
sqlite>insert into t1 values ('b');
sqlite>insert into t1 values ('c');
sqlite>insert into t1 values ('d');
sqlite>select rowid, name from t1;
rowid name
1 a
2 b
3 c
4 d

sqlite>delete from t1 where name='a';
sqlite>delete from t1 where rowid=3;
sqlite>select rowid, name from t1;
rowid name
2 b
4 d
sqlite>vacuum;
sqlte>select _rowid_, name from t1;
rowid name
1 b
2 d

2. WITH Primary Key Keyword
CREATE TABLE t2 (pk INTEGER PRIMARY KEY, name TEXT);

insert into t2(name) values ('a');
insert into t2(name) values ('b');
insert into t2(name) values ('c');
insert into t2(name) values ('d');

select rowid, pk, name from t2;
pk pk_1 name
1 1 a
2 2 b
3 3 c
4 4 d

delete from t2 where name='a';
delete from t2 where rowid=3;
select oid, pk, name from t2;
pk pk_1 name
2 2 b
4 4 d

sqllite>vacuum;
select oid, pk, name from t2;
pk pk_1 name
2 2 b
4 4 d

B. OBSERVED
- As we saw, the ROWID in the second sample will be retain static!
- So, don't forget to include INTEGER PRIMARY KEY in your Create Table statement.
- Rowid is integer key with amount of 9223372036854775807. Is it enough for you?.
- The rowid value can be accessed using one of the special names "ROWID", "OID", or "_ROWID_".
- If a column is declared to be an INTEGER PRIMARY KEY, then that column is not a "real" database column but instead becomes an alias for the rowid. Unlike normal SQLite columns, the rowid must be a non-NULL integer value.
- The VACUUM command may change the ROWIDs of entries in tables that do not have an explicit INTEGER PRIMARY KEY.

C. VACUUM
When an object (table, record, index, trigger, or view) is dropped from the database, it leaves behind empty space. This empty space will be reused the next time new information is added to the database. But in the meantime, the database file might be larger than strictly necessary. Also, frequent inserts, updates, and deletes can cause the information in the database to become fragmented - scrattered out all across the database file rather than clustered together in one place.

The VACUUM command cleans the main database by copying its contents to a temporary database file and reloading the original database file from the copy. This eliminates free pages, aligns table data to be contiguous, and otherwise cleans up the database file structure.

SQLite is so simple

SQLite is a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. It means a simplicity. Simplicity in a database engine can be either a strength or a weakness, depending on what you are trying to do.

A. Appropriate Uses
The primary design goal of SQLite is to be simple:
- Simple to administer
- Simple to operate
- Simple to embed in a larger program
- Simple to maintain and customize

So use SQLite in situations where simplicity of administration, implementation, and maintenance are more important than the countless complex features that enterprise database engines provide. SQLite is small, fast and reliable. Reliability is a consequence of simplicity. With less complication, there is less to go wrong.

B. Situations Where SQLite Works Well
1. Application File Format
SQLite has been used with great success as the on-disk file format for desktop applications such as financial analysis tools, CAD packages, record keeping programs, and so forth. The traditional File/Open operation does an sqlite3_open() and executes a BEGIN TRANSACTION to get exclusive access to the content. The use of transactions guarantees that updates to the application file are atomic, consistent, isolated, and durable (ACID).

2. Embedded devices and applications
SQLite is a good choice for devices or services that must work unattended and without human support. SQLite is a good fit for use in cellphones, PDAs, set-top boxes, and/or appliances. It also works well as an embedded database in downloadable consumer applications.

3. Websites
SQLite usually will work great as the database engine for low to medium traffic websites (which is to say, 99.9% of all websites). The amount of web traffic that SQLite can handle depends, of course, on how heavily the website uses its database. Generally speaking, any site that gets fewer than 100K hits/day should work fine with SQLite.

4. Replacement for ad hoc disk files
Many programs use fopen(), fread(), and fwrite() to create and manage files of data in home-grown formats. SQLite works particularly well as a replacement for these ad hoc data files.

5. Internal or temporary databases
For programs that have a lot of data that must be sifted and sorted in diverse ways, it is often easier and quicker to load the data into an in-memory SQLite database and use queries with joins and ORDER BY clauses to extract the data in the form and order needed rather than to try to code the same operations manually. Using an SQL database internally in this way also gives the program greater flexibility since new columns and indices can be added without having to recode every query.

6. Command-line dataset analysis tool
Experienced SQL users can employ the command-line sqlite program to analyze miscellaneous datasets. Raw data can be imported from CSV files, then that data can be sliced and diced to generate a myriad of summary reports. Possible uses include website log analysis, sports statistics analysis, compilation of programming metrics, and analysis of experimental results.

You can also do the same thing with an enterprise client/server database, of course. The advantages to using SQLite in this situation are that SQLite is much easier to set up and the resulting database is a single file that you can store on a floppy disk or flash-memory stick or email to a colleague.

7. Stand-in for an enterprise database during demos or testing
If you are writing a client application for an enterprise database engine, it makes sense to use a generic database backend that allows you to connect to many different kinds of SQL database engines. It makes even better sense to go ahead and include SQLite in the mix of supported databases and to statically link the SQLite engine in with the client. That way the client program can be used standalone with an SQLite data file for testing or for demonstrations.

8. Database Pedagogy
Because it is simple to setup and use (installation is trivial: just copy the sqlite or sqlite.exe executable to the target machine and run it) SQLite makes a good database engine for use in teaching SQL. Students can easily create as many databases as they like and can email databases to the instructor for comments or grading.

11. Experimental SQL language extensions
The simple, modular design of SQLite makes it a good platform for prototyping new, experimental database language features or ideas.

C. Situations Where Another RDBMS May Work Better
1. Client/Server Applications
If you have many client programs accessing a common database over a network, you should consider using a client/server database engine instead of SQLite. A good rule of thumb is that you should avoid using SQLite in situations where the same database will be accessed simultaneously from many computers over a network filesystem.

2. High-volume Websites
SQLite will normally work fine as the database backend to a website. But if you website is so busy that you are thinking of splitting the database component off onto a separate machine, then you should definitely consider using an enterprise-class client/server database engine instead of SQLite.

3. Very large datasets
With the default page size of 1024 bytes, an SQLite database is limited in size to 2 tebibytes (2⁴¹ bytes). And even if it could handle larger databases, SQLite stores the entire database in a single disk file and many filesystems limit the maximum size of files to something less than this. So if you are contemplating databases of this magnitude, you would do well to consider using a client/server database engine that spreads its content across multiple disk files, and perhaps across multiple volumes.

4. High Concurrency
SQLite uses reader/writer locks on the entire database file. That means if any process is reading from any part of the database, all other processes are prevented from writing any other part of the database. Similarly, if any one process is writing to the database, all other processes are prevented from reading any other part of the database. For many situations, this is not a problem. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution.

D. Want to explore?
1. Installation
Download Precompiled Windows Binaries:
1. Download http://www.sqlite.org/sqlite-3_6_23_1.zip (contains: sqllite3.exe)
2. and this http://www.sqlite.org/sqlitedll-3_6_23_1.zip (This is a DLL of the SQLite library)
3. Extract all the file and put in one directory (ex: C:/sqlite3)

C:\sqlite3>dir
Volume in drive C has no label.
Volume Serial Number is 9863-4221
Directory of C:\sqlite3
2010-04-27 00:13 .
2010-04-27 00:13 ..
2010-03-29 16:02 3.809 sqlite3.def
2010-03-29 16:02 520.234 sqlite3.dll
2010-03-29 16:02 528.522 sqlite3.exe
3 File(s) 1.052.565 bytes
2 Dir(s) 84.336.992.256 bytes free

The only external dependency is MSVCRT.DLL.

2. Create Database
A standalone program called sqlite3 is provided which can be used to create a database, define tables within it, insert and change rows, run queries and manage an SQLite database file. This program is a single executable file on the host machine. It also serves as an example for writing applications that use the SQLite library.

C:\sqlite3>sqlite3 pontianak.db3
SQLite version 3.6.23.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .exit

SQLite will create a new one, if the database does not exist.

3. Create Table
If you are not exit in the previous step, just do

sqlite> create table create table mytab (pk integer primary key, nametext not null);
sqlite> .table (this a sqllite command line to display tables, for other commands just type .help
mytab
sqlite>

or if you have exitted, type this:

C:\sqlite3>sqlite3 pontianak.db3 "create table mytab (pk integer primary key, nametext not null);

4. Insert Data

C:\sqlite3>sqlite3 pontianak.db3 "insert into mytab values (100, 'My Name');"
C:\sqlite3>sqlite3 pontianak.db3 "insert into mytab values (200, 'Your Name');"

or

sqlite>insert into mytab values (300, 'Tova Naruto');

5. Display Data

C:\sqlite3>sqlite3 pontianak.db3 "select * from mytab ;"

or

sqlite>select * from mytab;
100My Name
200Your Name
300Tova Naruto

E. GUI for Data Administration
Yes, you are absolutely right...for productivity reason we use GUI for data administration. You can find some in Management Tools , and I use SQLite Administration.

As stated above, if we want to move our database into another folder, computer, other device or mail it, just copy and paste pontianak.db3 database into destination.

At all...it's so simple and now it's time for me to do my job.
Happy coding and for detail see this.