title: Can't work around this foreign key constraint rule using TRIGGER in SQLite?
body: <p>First, I want to talk a little about the Foreign key constraint rule and how helpful it is. Suppose I have two tables, a primary table with the primary column called ID, the other table is the foreign one which also has a primary column called ID. This column in the foreign table refers to the ID column in the primary table. If we don't establish any Foreign key relation/constraint between those tables, we may fall foul of many problems related to integrity. </p>
  
  <p>If we create the foreign key relation for them, any changes to the ID column in primary table will 'auto' reflect to the ID column in the foreign table, changes here can be made by DELETE, UPDATE queries. Moreover, any changes to the ID in the foreign table should be constrained by the ID column in the primary table, for example there shouldn't any new value inserted or updated in the ID column of the foreign table unless it <strong>does exist</strong> in the ID column of the primary table.</p>
  
  <p>I know that SQLite doesn't support foreign key constraint (with full functions as detailed above) and I have to use TRIGGER to work around this problem. I have used TRIGGER to work around successfully in one way (Any changes to the ID column in the primary table will refect to the ID column in the foreign table) but the reverse way (should throw/raise any error if there is a confict occurs, for example, there are only values 1,2,3 in the ID column of the primary table, but the value 2 in the ID column of the foreign table is updated to 4 -> not exist in the primary table -> should throw error) is not easy. The difficult is <strong>SQLite doesn't also support IF statement and RAISERROR function</strong>. If these features were supported, I could work around easily.</p>
  
  <p>I wonder how you can use SQLite if it doesn't support some important features? Even working around by using TRIGGER is not easy and I think it's impossible, except that you don't care about the reverse way. (In fact, the reverse way is not really necessary if you set up your SQL queries carefully, but who can make sure? Raising error is a mechanism reminding us to fix and correct and making it work exactly without corrupting data and the <strong>bugs can't be invisible</strong>.</p>
  
  <p>If you still don't know what I want, I would like to have some last words, my purpose is to <strong>achieve the full functionality of the Foreign key constraint</strong> which is not supported in SQLite (even you can create such a relationship but <strong>it's fake</strong>, not real as you can benefit from it in SQL Server, SQL Server Ce, MS Access or MySQL).</p>
  
  <p>Your help would be highly appreciated. </p>
  
  <p>PS: I really like SQLite because it is file-based, easy to deploy, supports large file size (an advantage over SQL Server Ce) but some missing features have made me re-think many times, I'm afraid if going for it, my application may be unreliable and corrupt unpredictably.</p>