SQLite Forum

.selecttrace unknown and strange errors
You do not have a UNIQUE index on the parent A1 (A1_INDEX).  This is required.

You do not have an index on the child C1 where the leftmost column is C1_INDEX.

The UNIQUE index on the parent is required (not optional -- without it, the constraint is invalid and will throw errors).  The index on the child is only necessary if you want foreign key enforcement to use index lookup (fast) rather than table scans (slow).

The relationship parent:child must be 1:N.  This means that the parent key must be constrained UNIQUE (or be the PRIMARY KEY, which is the same thing) in order to enforce the 1: part of the relationship.  You need an index on the child key to be able to find children related to the parent unless you want to always be doing a brute-force table scan when enforcing integrity.  If the child key columns are a candidate key (and thus constrained UNIQUE) then the :N part of the relationship is constrained :1, otherwise it is :N.

These requirements apply to every Relational Database Management System ever created and are not unique to SQLite3.

On a second note, SQLite3 (and some other RDBMS) permit or default to having recursive triggers disabled.  This often leads to unclear thinking about the implementation of trigger programs in particular by failing to consider all the conditions which may cause the condition to fire.

Also note the order in which triggers fire.  This is very important.  When you do an operation on a "row" (insert/update/delete) the order of operations is as follows:

fire BEFORE triggers (for tables only)  
perform OPERATION or, for views only, an INSTEAD OF trigger  
fire AFTER triggers (for tables only)  

The purpose of the BEFORE trigger is to "put the database into the state at which the requested operation can be completed successfully or to abort the operation if it is not possible for this state to exist".

The purpose of the AFTER trigger is to "perform additional operations on the database which are required to implement collateral constraints only in the event we have successfully made it this far".

Also note that if you have **multiple** triggers of the same type, then all the applicable triggers will fire.  There is not, to my knowledge, any guarantee of the order in which the triggers will fire.  That is to say that if you have 15 BEFORE triggers of which 5 have applicable WHEN conditions, ALL 5 WILL RUN, although you have no control over the order in which they run.  If order of operations is important to you for some reason then you must put those order dependent operations within the same trigger.

Be aware also that the NEW and OLD pseudo tables contain information relative to the particular context when the trigger was fired, and not the current condition of the processing or database.  This is especially import to remember and to take into account in whether you require recursive triggers to be specifically enabled or disabled.  If you do not explicitly state whether you want trigger recursion on or off you should have designed your triggers to work correctly in both cases.