how does 0 value_id work?

[hydrargyrum]

The file_tag table contains value_id INTEGER NOT NULL column and FOREIGN KEY (value_id) REFERENCES value(id) constraint so it seems a file-tag link MUST have a value. But after using tmsu to create file-tag links without a value, I have:

sqlite> select count(*) from file_tag where value_id = 0;
4
sqlite> select count(*) from value where id = 0;
0

So how can file_tag.value_id reference a non-existing value.id, what sorcery is this?

[0ion9]

As far as I can see, this is the answer:

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.

My grepping suggests that TMSU never enables the above pragma. I'm not sure what it is worth that entering PRAGMA foreign_keys; to query the value returns 0 on a freshly initialized TMSU db, but I guess it counts as some evidence that it is not enabled by default on the database; I'm unsure whether 'enabled by default on the database' is a sensible possibility or not (maybe it's strictly the application runtime has to choose every time)

Now, it is the case that turning this pragma ON in sqlite3 has no immediate effects. I'm not interested in testing further, but given the description of how foreign key constraints work, you may find that turning the pragma ON and then making some modification to the file_tag table triggers an error.