SQLite

by Scott, Revised on February 18, 2013 (Databases)

SQLite is not a database server, but is a self-contained database storage engine that uses SQL for access and is fast and easy to use. For web development, it will be fine for most projects.

Create a SQLite Database From a SQL File:

Dump a SQL file into a sqlite database:

sqlite3 mydb.sqlite < mydata.sql

Dump data from a sqlite database into a file:

sqlite3 mydb.sqlite .dump > mydata.sql

SQLite foreign key support

Using the DB for this site, the "uid" foreign key is found in the blog table referencing the user table "id" column:

FOREIGN KEY(uid) REFERENCES users(id) ON DELETE RESTRICT ON UPDATE CASCADE

Also, the comment table has a "blog_id" foreign key refrencing the blog table "id" column:

FOREIGN KEY(blog_id) REFERENCES blogs(id) ON DELETE CASCADE ON UPDATE CASCADE

The "ON DELETE" and "ON UPDATE" clauses are used to configure actions that take place when deleting or updating rows from the parent table. The clauses are NO ACTION, RESTRICT, SET NULL, SET DEFAULT, and CASCADE. The first example above prevents deletion of a user record (ON DELETE RESTRICT) if a blog for that user exists. The second example allows for deletion (ON DELETE CASCADE) of the comment record if the blog record that the foreign key points to is also deleted.

Tags: Databases