Occasionally the stored proc gets a deadlock - I believe I've built an appropriate error handler; however I'm interested to see if this methodology works as I think it does, or if I'm barking up the wrong tree here.
I'm fairly certain there should be a way to access this table without any deadlocks at all.
In this article Syntax Remarks Example A constraint is similar to an index, although it can also be used to establish a relationship with another table.
You use the syntax for a multiple-field constraint whenever you use the reserved word CONSTRAINT outside a field-definition clause in an ALTER TABLE or CREATE TABLE statement.Using CONSTRAINT you can designate a field as one of the following types of constraints: To prevent the automatic creation of indexes for foreign keys, the modifier NO INDEX can be used.There are two types of CONSTRAINT clauses: one for creating a constraint on a single field and one for creating a constraint on more than one field.The Microsoft Access database engine does not support the use of CONSTRAINT, or any of the data definition language (DDL) statements, with non-Microsoft Access database engine databases. You use the syntax for a single-field constraint in the field-definition clause of an ALTER TABLE or CREATE TABLE statement immediately following the specification of the field's data type.Given that multiple persons/processes are updating data in a database, it is only a matter of time before two separate persons/processes will try to update the same piece of data.
Because a typical update cycle consists of: there will be occasions where two users will both read the same data into memory.
Net-PCL is one of the best SQLite client/ORM out there in a PCL form supporting Windows Store and Universal apps.
It's open source, but it's not feature complete, and you may learn the hard way that some things are missing.
This form of foreign key definition should be used only in cases where the resulting index values would be frequently duplicated.
Where the values in a foreign key index are frequently duplicated, using an index can be less efficient than simply performing a table scan.
Maintaining this type of index, with rows inserted and deleted from the table, degrades performance and does not provide any benefit.