sql – Kev's Development Toolbox

November 15, 2012

SQLite essentials

SQLite docs: http://www.sqlite.org/docs.html

Date Types: http://www.sqlite.org/datatype3.html

null, inteteger, real, text, blob

SQLite does not have a date/time time – dates stored in either text, real or integer columns are converted appropriately to that type.

Table relationships

create table person (id integer primary key asc, name text);

create table address( id integer primary key asc,
  person_id references person( id ),
  add_line1 text );

March 27, 2011

Enabling FK support in SQLite with Firefox SQLite Manager

SQLite Manager is an awesome plugin for Firefox that lets you create & manage a SQLite db.

To enable FK support using SQLite Manager:

Go to DB Settings, change Foreign Keys to ‘On’, Press ‘Change’
Go to Tools, then ‘Use table for extension data’
Go to Tools, then ‘Open on-connect SQL’
Click on the ‘On connect SQL’ tab – the ‘On-connect SQL statements for THIS database’ textarea is now enabled.
In the field enter: PRAGMA foreign_keys=ON;
Press Save.

Syntax to create an FK ref:

<tt>CREATE TABLE child_table_name (field_1 INTEGER PRIMARY KEY,
field_2  TEXT,
foreign_key_field INTEGER ,
FOREIGN KEY(foreign_key_field)  REFERENCES parent_table_name(parent_key_field));</tt>