Foreign Key vs Delete Trigger

Question

Post reply

Foreign Key vs Delete Trigger

K Currie

SSCommitted

Points: 1662
More actions
July 20, 2015 at 10:27 am

#319117

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?

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 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.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

K Currie SSCommitted Points: 1662 More actions · Answer 3

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