Foreign Key vs Delete Trigger

  • I have two lookup tables and one table that stores data. I want to ensure that no data from either of the two lookup tables is deleted from the database.

    I have decided I can either add two Foreign Keys to the table that stores the data OR add triggers to the two very small lookup tables to prevent deletions.

    I can't decide which one is better to do. The lookup tables rarely have inserts, updates, deletes, so I'm thinking the trigger might actually be the better way to go. The table that stores data is accessed all the time via selects, inserts and updates. Would adding the Foreign Keys help or hurt in terms of performance?

  • Foreign keys can improve performance. It might not always happen, but there's a possibility.

    http://www.scarydba.com/2010/11/22/do-foreign-key-constraints-help-performance/

    Triggers can prevent deletes in general. This is good if you want that. If you only want to maintain referential integrity (only prevent deletes from values used in the data table), then you should use the FKs.

    You must know that both triggers and FKs can be disabled, so you should also control that with permissions on the corresponding objects/schema/db.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • In addition to what Luis has said, foreign keys also ensure that no rows can be inserted into the "data" table without a corresponding row in the lookup tables. In order to enforce this with triggers you also have to make sure you have an INSERT/UPDATE trigger on the "data" table that checks for a corresponding value in the lookup table.

  • Both are good points and that's the recommendation I am making.

    (That and I might actually have a need to delete something in my lookup table and since it's never been used I can do this with my ForeignKey. I'd have to disable my trigger first and I'm too lazy to do that.)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply