Integridade referencial no SQLite

No Timesheet, tenho 3 tabelas (jobs, tasks, tracking). Quando excluo um trabalho, devo remover todas as suas tarefas e trackings. Isso é muito chato de fazer porque preciso propagar a exclusão manualmente. Até onde eu sei, o SQLite não tem suporte às chaves estrangeiras, apesar de aceitar sua definição na cláusula SQL.

Mas graças ao excelente suporte à triggers, consigo fazer essa propagação toda através do banco de dados.

Primeiro, vamos criar 2 tabelas.

CREATE TABLE groups (
    id INTEGER PRIMARY KEY,
    name VARCHAR
);

CREATE TABLE items (
    id INTEGER PRIMARY KEY,
    name VARCHAR,
    group_id INTEGER NOT NULL
        CONSTRAINT group_id REFERENCES groups (id) ON DELETE CASCADE
);

Agora, iremos criar as nossas triggers que irão forçar a integridade referencial. Precisamos definir uma trigger para INSERT, outra para UPDATE e uma terceira para DELETE.

CREATE TRIGGER insert_groups_items
BEFORE INSERT ON items
    FOR EACH ROW BEGIN
        SELECT CASE
            WHEN ((SELECT id FROM groups WHERE id = NEW.group_id) IS NULL)
            THEN RAISE (ABORT, 'INSERT on table "items" violates foreign key')
        END;
    END;

CREATE TRIGGER update_groups_items
BEFORE UPDATE ON items
    FOR EACH ROW BEGIN
        SELECT CASE
            WHEN ((SELECT id FROM groups WHERE id = NEW.group_id) IS NULL)
            THEN RAISE(ABORT, 'UPDATE on table "items" violates foreign key')
        END;
    END;

CREATE TRIGGER delete_groups_items
BEFORE DELETE ON groups
    FOR EACH ROW BEGIN
        DELETE FROM items WHERE group_id = OLD.id;
    END;

As triggers "insert_groups_items" e "update_groups_items" verificam se o grupo realmente existe. Já a trigger "delete_groups_items" remove todos os registros pertencentes ao grupo que estamos tentando remover. Para vermos como isso funciona, vamos adicionar alguns registros.

INSERT INTO groups (name) VALUES ('Fruits');
INSERT INTO groups (name) VALUES ('Cars');
INSERT INTO items (name, group_id) VALUES ('Apple', 1);
INSERT INTO items (name, group_id) VALUES ('Orange', 1);
INSERT INTO items (name, group_id) VALUES ('Pineapple', 1);
INSERT INTO items (name, group_id) VALUES ('Ferrari', 2);
INSERT INTO items (name, group_id) VALUES ('Porsche', 2);

Listando os grupos e itens:

SELECT
    groups.name "group",
    items.name "item"
FROM
    groups, items
WHERE
    groups.id = items.group_id;

Isso irá exibir os registros que inserimos. Agora, vamos remover o grupo "Cars" e fazer a consulta novamente.

DELETE FROM groups WHERE id = 2;

SELECT
    groups.name "group",
    items.name "item"
FROM
    groups, items
WHERE
    groups.id = items.group_id;

Muito bom, né? Visto aqui.