Overhead of triggers and foreign key constraints

performancesqlitetrigger

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?

Best Answer

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].

Related Solutions

Mysql – Derived value not calculating as expected using combination of FK and Triggers (MySQL)

reviews_has_comments (rhc) exists because comments can be added to several other types of content.

If I understand correctly, that is not a justification for responses_has_comments table - you can have several tables with 1-to-many relationships with the same comments table. The only reason for introducing responses_has_comments would be if the same comment can be attached to more than one response, as well as more than one comment being attached to each response, ie it is a many-to-many relationship - I don't think that is what you are trying to achieve.

This does not answer your main question but may sidestep the problem in your updated trigger. Your comments trigger then becomes:

CREATE TRIGGER `dec_n_comments`
    AFTER DELETE ON `comments` FOR EACH ROW
    BEGIN
        UPDATE responses
        SET n_comments = n_comments - 1
        WHERE id_responses = @id_responses;
        -- rinse and repeat for all linked "commentable" table data...        
    END;
$$

Mysql – Tablestructure for fast inserts/deletes with foreign keys

First, if your tables are InnoDB, and column c.P_ID is the reference of column p.P_ID (note actual column names may be different), then you absolutely should use foreign keys to avoid orphans, but know that you will have to explicitly state ON DELETE CASCADE, since the default is ON DELETE SET NULL, retaining orphans.

But this doesn't really have anything to do with the 'single query' to select the products and information about the competitors.

To achieve:

I want to achieve, that with only one query i got the product information from p an also the competitor ones from c where the product id is the same.

your query would use a join:

SELECT products.P_ID, products.product_name, competitors.info
 FROM products
 LEFT JOIN competitors ON competitors.P_ID=products.P_ID

This will get you repeat information for the products (competitor info will be different). And then you will use your application to parse that info into the array you need.

If you want a single row for the products, you can use the GROUP_CONCAT function for the competitors:

SELECT products.P_ID, products.product_name, 
  GROUP_CONCAT(competitors.info SEPARATOR ',') as competitor_info
 FROM products
 LEFT JOIN competitors ON competitors.P_ID=products.P_ID
 GROUP BY products.P_ID;

A major caveat you should be aware of: ON UPDATE|DELETE CASCADE does not fire triggers on the rows that were deleted/updated by the cascade:

Note

Currently, cascaded foreign key actions do not activate triggers.

This shouldn't stop you from using CASCADE if you want to remove orphan rows. But you should be aware of it.

Best Answer

Related Solutions

Mysql – Derived value not calculating as expected using combination of FK and Triggers (MySQL)

Mysql – Tablestructure for fast inserts/deletes with foreign keys

Related Question