First Steps in SQLite

Foreign key example:

In this tutorial I will show you how to crate a small SQLite DB with 3 tables and assign foreign keys to some parameters in the 3rd table. I’ve used “SQLite Database Browser” tool to crate database and tables inside. You can find this tool @ here. As all you know Android support small SQL database, this SQLite is stripped down version of standard SQL’s db. This fact can crate some problems to users so let’s take a look at the SQLite limitations:

Maximum length of a string or BLOB
Maximum Number Of Columns
Maximum Length Of An SQL Statement
Maximum Number Of Tables In A Join
Maximum Depth Of An Expression Tree
Maximum Number Of Arguments On A Function
Maximum Number Of Terms In A Compound SELECT Statement
Maximum Length Of A LIKE Or GLOB Pattern
Maximum Number Of Host Parameters In A Single SQL Statement
Maximum Depth Of Trigger Recursion
Maximum Number Of Attached Databases
Maximum Number Of Pages In A Database File

You can find more details here and SQLite FAQ.

Let me start creating two tables, but you may use more tables if you want to. In both tables I have defined one parameter as PRIMARY KEY. Pay attention to the SQL parameter NOT NULL, in this case I didn’t add this to the parameter in the table. You will find out later why.

// Users table

CREATE TABLE userTB (
		UserID INTEGER PRIMARY KEY,
		FirstName TEXT NOT NULL,
		LastName TEXT NOT NULL,
		Age NUMERIC NOT NULL,
		Address TEXT NOT NULL
		);

// Event table

CREATE TABLE eventTB (
		EventID INTEGER PRIMARY KEY,
		EventName NOT NULL,
		EventType NOT NULL,
		EventDescription TEXT NOT NULL
		);

// Users table

CREATE TABLE userTB (

UserID INTEGER PRIMARY KEY,

FirstName TEXT NOT NULL,

LastName TEXT NOT NULL,

Age NUMERIC NOT NULL,

Address TEXT NOT NULL

);

// Event table

CREATE TABLE eventTB (

EventID INTEGER PRIMARY KEY,

EventName NOT NULL,

EventType NOT NULL,

EventDescription TEXT NOT NULL

);

Next, we need to crate a table which has two foreign keys, one that relates reportTB table with eventTB table, and the same for the second foreign key which relates the reportTB table to userTB table:

-- Report table

CREATE TABLE  reportTB(
  id INTEGER PRIMARY KEY,
  event_ID INTEGER,
  user_ID INTEGER,
  <strong>FOREIGN KEY</strong>(event_ID) REFERENCES eventTB(eventID),
  <strong>FOREIGN KEY</strong>(user_ID) REFERENCES userTB(userID)
);

-- Report table

CREATE TABLE reportTB(

id INTEGER PRIMARY KEY,

event_ID INTEGER,

user_ID INTEGER,

<strong>FOREIGN KEY</strong>(event_ID) REFERENCES eventTB(eventID),

<strong>FOREIGN KEY</strong>(user_ID) REFERENCES userTB(userID)

);

Add some data

// event data
INSERT INTO eventTB VALUES (null, 'Eating', 'Leisure', 'Spending Free Time');
INSERT INTO eventTB VALUES (null, 'cycling', 'Sports', 'Spending Free Time');

// user data
INSERT INTO userTB VALUES (null, 'Sasa', 'Bremec', '33', 'Nova Gorica');
INSERT INTO userTB VALUES (null, 'Pinko', 'Palinko', '11', 'Gorizia');

// orders sample data
INSERT INTO reportTB VALUES (null, 1, 2);
INSERT INTO reportTB VALUES (null, 2, 1);

// event data

INSERT INTO eventTB VALUES (null, 'Eating', 'Leisure', 'Spending Free Time');

INSERT INTO eventTB VALUES (null, 'cycling', 'Sports', 'Spending Free Time');

// user data

INSERT INTO userTB VALUES (null, 'Sasa', 'Bremec', '33', 'Nova Gorica');

INSERT INTO userTB VALUES (null, 'Pinko', 'Palinko', '11', 'Gorizia');

// orders sample data

INSERT INTO reportTB VALUES (null, 1, 2);

INSERT INTO reportTB VALUES (null, 2, 1);

How to test… easy

SELECT * FROM reportTB;

1	SELECT * FROM reportTB;