Overhead of triggers and foreign key constraints?

Question

I have a sqlite database which is accessed by two programs. There are a number of invariants/constraints which should always hold. In addition to the usual foreign key constrains between tables, there is a table where once a row is inserted the row should never be deleted and several of its columns should never be changed.

The programs are written to maintain these conditions. For example, there is no code which deletes from the table mentioned above.

I could also add triggers to the DB to enforce the required conditions by aborting statements which would violate them. This seems like a reasonable idea to me, "belt and suspenders"-wise. Also it is some additional "living documentation" of the DB requirements.

I am assuming, but I can not find and concrete evidence, that these triggers would add minimal overhead as they should never be invoked (why would it even look at a BEFORE DELETE or BEFORE UPDATE trigger on a SELECT or INSERT?).

I am less sure about the likely overhead of foreign key constraints as these would seem to get checked on every INSERT/UPDATE/DELETE to their parent table.

Questions:
(1) Are the triggers/foreign key constraints a good idea even if I am very confident that my code will never attempt to violate them?
(2) Where can I get information on their overhead?

Answer 1

Are the triggers/foreign key constraints a good idea ...

Short answer: Yes.

... even if I am very confident that my code will never attempt to violate them?

Two things:

Firstly, it's not always going to be "your" code. Times change, people move on, stuff happens. If someone else comes along and "inherits" your code base, you cannot be "very confident" that they will do as good a job as you. Constraints in the database will protect them from themselves.

Secondly, What's to stop someone hooking up their favorite sqlite client and poking around in the database directly? If all the data integrity is done in the Application, then going "under the radar", into the database directly, gets around that and allows someone to fill the database with complete rubbish, potentially breaking the Application in the process. Again, constraints in the database will protect them from themselves.

Where can I get information on their overhead?

It's called Testing.

Whilst you can get general advice about such things, you won't really know how it affects your database until you try it out in your [test] database.

Benchmark your Tests with and without the constraints. See if the difference, if any, is acceptable [to you and your users].