mathr / blog / #

Remember when they said that "every table in your database should have an 'id' field as primary key"? Well they lied, because in the case where you have a has_and_belongs_to_many relation, having a field called 'id' in the association table breaks code horribly. Somehow if you have an 'id' field in the groups_users table, doing

group.users.each{|user| user.id}

will not give you the id of the user, but some other value (probably the id of the association entry between the group and the user, but I haven't verified this). Strangely,

group.users.each{|user| user.user_id}

has the correct id instead, even though User should have no user_id field. Naturally, this causes no end of trouble!

It turns out it is safe to remove the 'id' field from the groups_users table entirely, leaving it without a primary key. And magically my code works as expected now, with user.id really being user.id everywhere.

Here is the SQLite3 schema for my project so far

BEGIN TRANSACTION;

CREATE TABLE users (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,
    login VARCHAR(255) NOT NULL ,
    password VARCHAR(255) NOT NULL
);

CREATE TABLE groups (
    id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE groups_users (
    user_id  INTEGER NOT NULL CONSTRAINT fk_user_id  REFERENCES users(id)  ON DELETE CASCADE ,
    group_id INTEGER NOT NULL CONSTRAINT fk_group_id REFERENCES groups(id) ON DELETE CASCADE
);


CREATE TRIGGER fki_users_uniqueness
BEFORE INSERT ON users
FOR EACH ROW BEGIN
    SELECT CASE
        WHEN ((SELECT id FROM users WHERE login = NEW.login) IS NOT NULL)
        THEN RAISE(ABORT,
            'insert on table "users" violates uniqueness constraint on "login"')
    END;
END;

CREATE TRIGGER fku_users_uniqueness
BEFORE UPDATE ON users
FOR EACH ROW BEGIN
    SELECT CASE
        WHEN ((SELECT id FROM users WHERE login = NEW.login) IS NOT NULL)
        THEN RAISE(ABORT,
            'update on table "users" violates uniqueness constraint on "login"')
    END;
END;

CREATE TRIGGER fki_groups_uniqueness
BEFORE INSERT ON groups
FOR EACH ROW BEGIN
    SELECT CASE
        WHEN ((SELECT id FROM groups WHERE name = NEW.name) IS NOT NULL)
        THEN RAISE(ABORT,
            'insert on table "groups" violates uniqueness constraint on "name"')
    END;
END;

CREATE TRIGGER fku_groups_uniqueness
BEFORE UPDATE ON groups
FOR EACH ROW BEGIN
    SELECT CASE
        WHEN ((SELECT id FROM groups WHERE name = NEW.name) IS NOT NULL)
        THEN RAISE(ABORT,
            'update on table "groups" violates uniqueness constraint on "name"')
    END;
END;

CREATE TRIGGER fki_groups_users_uniqueness
BEFORE INSERT ON groups_users
FOR EACH ROW BEGIN
    SELECT CASE
        WHEN ((SELECT user_id FROM groups_users WHERE user_id = NEW.user_id AND group_id = NEW.group_id) IS NOT NULL)
        THEN RAISE(ABORT,
            'insert on table "groups_users" violates uniqueness constraint on "user_id, group_id"')
    END;
END;

CREATE TRIGGER fki_groups_users_user_id
BEFORE INSERT ON groups_users
FOR EACH ROW BEGIN
    SELECT CASE
        WHEN ((SELECT id FROM users WHERE id = NEW.user_id) IS NULL)
        THEN RAISE(ABORT,
            'insert on table "groups_users" violates foreign key constraint on "user_id"')
    END;
END;

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

CREATE TRIGGER fku_groups_users_uniqueness
BEFORE UPDATE ON groups_users
FOR EACH ROW BEGIN
    SELECT CASE
        WHEN ((SELECT user_id FROM groups_users WHERE user_id = NEW.user_id AND group_id = NEW.group_id) IS NOT NULL)
        THEN RAISE(ABORT,
            'update on table "groups_users" violates uniqueness constraint on "user_id, group_id"')
    END;
END;

CREATE TRIGGER fku_groups_users_user_id
BEFORE UPDATE ON groups_users
FOR EACH ROW BEGIN
    SELECT CASE
        WHEN ((SELECT id FROM users WHERE id = NEW.user_id) IS NULL)
        THEN RAISE(ABORT,
            'update on table "groups_users" violates foreign key constraint on "user_id"')
    END;
END;

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

CREATE TRIGGER fkd_groups_users_user_id
BEFORE DELETE ON users
FOR EACH ROW BEGIN
    DELETE FROM groups_users WHERE user_id = OLD.id;
END;

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

INSERT INTO "users" (id, login, password) VALUES (0, 'admin', 'but-we-dont-want-to-give-you-that');
INSERT INTO "groups" (id, name) VALUES (0, 'admin');
INSERT INTO "groups_users" (user_id, group_id) VALUES (0, 0);

COMMIT;

Look at all those lovely triggers! Tasty referential data integrity!