Running foreign keys in SQLite

SQLite comes with genfkey utility that will generate triggers to enforce foreign key constraints. Here is the source. There is also a README, just change the previous url to f = sqlite / tool / genfkey.README (stackoverflow only allows me to submit one url)

Two trigger pairs are generated behind FK: BEFORE INSERT AND BEFORE UPDATE on the reference table, and BEFORE DELETE and AFTER UPDATE on the reference table. I cannot understand why the last trigger is AFTER and not BEFORE, like others. See line 741 in the source, or just search for "AFTER", this is the only instance in the file.

It's not a huge deal - if you're in a transaction and the AFTER trigger generates an error, you can still backtrack. I'm just wondering if anyone has any idea why this is different.

+2
sqlite triggers foreign-keys


source to share


3 answers


Because it needs the ROWID of the inserted row.

The ROWID is generated when a row is inserted, so it will not be available in a BEFORE trigger.

I don't know exactly how this module works, but the ROWID reference is on line 755:



", '/on_update/', on_update"

", '/name/',   'genfkey' || min(rowid)"
", '/tbl/',    dq(from_tbl)"

      

See also: http://linuxgazette.net/109/chirico1.html

+2


source to share


Please note that true foreign key constraints are finally supported in the stable release of sqlite, v3.6.19 and later: http://www.sqlite.org/foreignkeys.html



+6


source to share


AFTER UPDATE A trigger means that Oracle will fire this trigger after an UPDATE operation.

From Oracle / PLSQL: AFTER UPDATE Trigger after a quick google.

This should be a replication of an ANSI SQL query (I think) that runs after the UPDATE completes. There are BEFORE, AFTER, and INSTEAD OF triggers that SQLite does not support out of the box.

+1


source to share







All Articles
Loading...
X
Show
Funny
Dev
Pics