Using SQLite

Enabling CASCADE deletion#

In order to enable CASCADE deletion in SQLite, an ON DELETE CASCADE clause should be added to the foreign key declaration.

In an example below, we have a one-to-many relationship between A and B.

DROP TABLE IF EXISTS A;

CREATE TABLE A (
    id TEXT PRIMARY KEY, 
    name TEXT NOT NULL UNIQUE
);

DROP TABLE IF EXISTS B;

CREATE TABLE B (
    id TEXT PRIMARY KEY,
    aref TEXT NOT NULL, 
    name TEXT NOT NULL UNIQUE,
    FOREIGN KEY(aref) REFERENCES A(id) ON DELETE CASCADE
);

Insert data into A and into B.

INSERT INTO A VALUES ('a1', 'a1Item');
INSERT INTO B VALUES ('b1', 'a1', 'b1Item');

With the ON DELETE CASCADE clause in the foreign key declaration, deletion of the ‘a1’ record also will delete the ‘b1’ one.

DELETE FROM A WHERE id = 'a1';

Check whether a table exists#

In SQLite, a table existence can be checked by querying the sqlite_master table:

SELECT COUNT(*) as table_exists FROM sqlite_master WHERE type='table' AND name = '<table name>';

If the given table exists, the table_exists value will be 1, and 0 otherwise.