I'm trying to set up a SQLite3 database with foo
s and bar
s 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?
Each post can reference many authors, and each author can reference many posts. Each post can have many authors, and each author can write many posts. Therefore, there is a many-to-many relationship between posts and authors.
To create many-to-many relationships, you need to create a new table to connect the other two. This new table is called an intermediate table (or sometimes a linking or junction table).
Python Flask and SQLAlchemy ORM Many to Many relationship between two tables is achieved by adding an association table such that it has two foreign keys - one from each table's primary key.
SQLite has supported foreign key constraint since version 3.6. 19.
Taken from this site, http://www.sqlite.org/foreignkeys.html.
Assuming the library is compiled with foreign key constraints enabled, it must still be enabled by the application at runtime, using the PRAGMA foreign_keys command. For example:
sqlite> PRAGMA foreign_keys = ON;
Foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection separately. (Note, however, that future releases of SQLite might change so that foreign key constraints enabled by default. Careful developers will not make any assumptions about whether or not foreign keys are enabled by default but will instead enable or disable them as necessary.) The application can can also use a PRAGMA foreign_keys statement to determine if foreign keys are currently enabled. The following command-line session demonstrates this:
sqlite> PRAGMA foreign_keys;
0
sqlite> PRAGMA foreign_keys = ON;
sqlite> PRAGMA foreign_keys;
1
sqlite> PRAGMA foreign_keys = OFF;
sqlite> PRAGMA foreign_keys;
0
Tip: If the command "PRAGMA foreign_keys" returns no data instead of a single row containing "0" or "1", then the version of SQLite you are using does not support foreign keys (either because it is older than 3.6.19 or because it was compiled with SQLITE_OMIT_FOREIGN_KEY or SQLITE_OMIT_TRIGGER defined).
It is not possible to enable or disable foreign key constraints in the middle of a multi-statement transaction (when SQLite is not in autocommit mode). Attempting to do so does not return an error; it simply has no effect.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With