SQLite many-to-many relationship?

Question

I'm trying to set up a SQLite3 database with foos and bars and a many-to-many relation between them. This is what I've got so far:

CREATE TABLE foo(
    id INTEGER PRIMARY KEY NOT NULL,
    foo_col INTEGER NOT NULL
);
CREATE TABLE bar(
    id INTEGER PRIMARY KEY NOT NULL,
    bar_col TEXT NOT NULL
);
CREATE TABLE foobar(
    foo_id INTEGER,
    bar_id INTEGER,
    FOREIGN KEY(foo_id) REFERENCES foo(id) ON DELETE CASCADE,
    FOREIGN KEY(bar_id) REFERENCES bar(id) ON DELETE CASCADE
);
CREATE INDEX fooindex ON foobar(foo_id);
CREATE INDEX tagindex ON foobar(tag_id);

...but it doesn't seem to be working. I can delete a row from foo and it doesn't affect foobar. What am I doing wrong?

SQLite many-to-many relationship?

1 Answers