database - Can't work around this foreign key constraint rule using TRIGGER in SQLite? -

first, want talk little foreign key constraint rule , how helpful is. suppose have 2 tables, primary table primary column called id, other table foreign 1 has primary column called id. column in foreign table refers id column in primary table. if don't establish foreign key relation/constraint between tables, may fall foul of many problems related integrity.

if create foreign key relation them, changes id column in primary table 'auto' reflect id column in foreign table, changes here can made delete, update queries. moreover, changes id in foreign table should constrained id column in primary table, example there shouldn't new value inserted or updated in id column of foreign table unless does exist in id column of primary table.

i know sqlite doesn't support foreign key constraint (with full functions detailed above) , have use trigger work around problem. have used trigger work around in 1 way (any changes id column in primary table refect id column in foreign table) reverse way (should throw/raise error if there confict occurs, example, there values 1,2,3 in id column of primary table, value 2 in id column of foreign table updated 4 -> not exist in primary table -> should throw error) not easy. difficult sqlite doesn't support if statement , raiserror function. if these features supported, work around easily.

i wonder how can use sqlite if doesn't support important features? working around using trigger not easy , think it's impossible, except don't care reverse way. (in fact, reverse way not necessary if set sql queries carefully, can make sure? raising error mechanism reminding fix , correct , making work without corrupting data , bugs can't invisible.

if still don't know want, have last words, purpose achieve full functionality of foreign key constraint not supported in sqlite (even can create such relationship it's fake, not real can benefit in sql server, sql server ce, ms access or mysql).

your highly appreciated.

ps: sqlite because file-based, easy deploy, supports large file size (an advantage on sql server ce) missing features have made me re-think many times, i'm afraid if going it, application may unreliable , corrupt unpredictably.

to answer question have skillfully hidden in rant: sqlite allows raise function inside triggers; because of lack of control flow statements, must used select:

create trigger check_that_id_exists_in_parent before update of id on child_table each row begin     select raise(abort, 'parent id not exist')     not exists (select 1                       parent_table                       id = new.id); end;