Per this other SO answer, it appears that SQLite does not automatically enforce foreign key relationships, and this must be explicitly enabled per connection. And this is a reported issue with ServiceStack.OrmLite. I've reproduced this very behavior myself with ServiceStack.OrmLite 3.9.33 and SQLite 1.0.84, where unit tests succeed (allowing delete of a record with a foreign key reference) but the real application (legitimately) fails.
What I'm unclear on is how/where to execute the necessary "PRAGMA foreign_keys = ON;" SQL command so that it applies to any connections for my SQLite unit/integration tests but not for my primary SQL Server connections. It seems that it would ideally be part of the IDbConnectionFactory registration.
Can anyone help?
Edit: Cascading deletes would be another way to handle this situation, but despite setting this correctly on my FK columns using the ForeignKey attribute, it appears ServiceStack.OrmLite doesn't honor this when used with SQLite (noted here), despite the latter's support for it. It's possible that this is tied to the original issue, with SQLite's FK enforcement being disabled by default, and it would work properly if that were enabled first.
