Triggers vs. Foreign-keys?
-
First, everyone -- Thanks for lending your brains to this issue! I didn't expect so many replies, and I'm learning much myself as I go. Some observations, though, about the answers...
My main intents are to (1) learn if there's truth to his concern about lockouts re: FKs, on MSSQL v2000, and (2) if there's no truth (or some truth but the problem is easily avoided) to demonstrate that to him so that we don't start redesigning our DBs on a misconception.
Many of you have chimed in with excellent quotes from key documentation. But I should have been clearer. The question is, is this a problem specifically on SQL Server 2000? Oracle documentation doesn't help me argue my case, though its otherwise exactly on point. It helps me argue the *theory* that FKs are better than triggers, but not that FKs are better than triggers on Microsoft SQL Server. (An obvious counter to any Oracle documentation: "Yeah, that's how it should work. But Microsoft screwed it up, and triggers are better on MSSQL.")
quote:
You can precisely replicate the functionality of a FK constraint using a trigger. But you will effectively be rewriting Sybase/MS C (or whatever it is) code, using T-SQL. I can't imagine that that could be more efficient.An excellent point, no matter what platform! I will remember this.
quote:
However, if you want to enforce cascade update/delete operations in SQL 7-, or to enforce integrity across databases, you would need to use a trigger instead of a FK.As someone else mentioned in this thread, you may have intuited something here: this colleague might have done a lot of work in v7.0 of MSSQL. But the issue isn't whether UPDATES and DELETES can be handled and cascaded via a foreign-key, it's the issue of lockouts.
quote:
You should ask your colleague for his reasons for preferring triggers.It's in my original post: He argues that FKs cause timeouts/lockouts, but you can avoid this with triggers. Ergo, we shouldn't use FKs and should instead build triggers to replicate the FK data-integrity functionality.
Another element of his argument is that if TableA has the primary key, and TableB has the foreign key, and TableB has another primary key that TableC has the foreign key for, and so on, daisy-chaining several levels of FK relationships...he says that an update to TableA will lock all the other tables, heightening the chance of a timeout/lockout on one of the others. Again, it seems to me extremely unlikely that Microsoft would have built such a gigantic snare into their premier database product. If FKs were so fraught with dangers, there should be DBAs storming the gates (pun intended) of Microsoft. Can anyone comment on this cascading-lockout issue? Is there any truth to it?
-
DALEK,
All this stuff about cascade operations is off-topic in any case, but surely the point of the site is to give info you couldn't find just by searching MSDN. For example, I'm not sure about documentation for the cascade triggers, but I have done them before (I am an 'old' SQL 7 programmer upgrading soon) and they worked fine - and I have code for anyone that wants it. Don't overestimate MSDN - some of it is just plain wrong (but I don't have any documentation for that, I afraid!).
In terms of your imaginary recruitment decision:
quote:
I think it may be best for some folks to revisit Creating and Maintaining Databases, Designing Databases and Designing Tables on MSDN. If I knew you were trading off triggers for foreign keys as a database developer, I would think twice about hiring you. Seriously.You needn't worry - in the unlikely event of your having the opportunity to hire any of us, you need only observe that NO-ONE in this thread is suggesting that using triggers instead of ref. integrity is a good idea (except where it is unavoidable, as in the sql 7- cases I pointed out.)
Tim
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Oh sorry. Must have hit a sore nerve. Some of us need to not take things so personally. I'll leave the lock out issue to the experts that you think you are. Have at it.
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
-
quote:
He argues that FKs cause timeouts/lockouts, but you can avoid this with triggers. Ergo, we shouldn't use FKs and should instead build triggers to replicate the FK data-integrity functionality.OK - so he needs to argue:
1.FKs cause blocking
2.Triggers don't
-------------------
..use triggers instead of FKs.
And he falls down on premise 2.
How does he think a trigger will be able to perform the necessary checks without contributing to blocking problems?
You have a problem in arguing this: the issue is not extensively documented, for the same reason that there are very few msdn articles about the dangers of pouring soup into the server.
You need to get him on the back foot (if possible) - make him explain how this is supposed to work, before he mullers your system.
quote:
You can precisely replicate the functionality of a FK constraint using a trigger. But you will effectively be rewriting Sybase/MS C (or whatever it is) code, using T-SQL.Ask this bloke how he thinks the locking and speed problems could be improved by using his own high-level code instead of efficient, well-tested lower level code.
Tim
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Did you see the SQLServer Community update for today?
It was advertising Kim DeLaney's new book:
Hands-On SQL Server 2000 : Troubleshooting Locking and Blocking
Kind of ironic, don't you think?
Sorry, just had to come back and share that. Hey, it's only $22. Might be a good investment.
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
-
quote:
Did you see the SQLServer Community update for today?It was advertising Kim DeLaney's new book...
Fantastic. I will give it a look over, thanks!
And folks...it seems like some feathers are getting ruffled in all this. Everyone's working hard to spiral in towards the answers, and together we're tracing out the problem-space and solution-space. I'm taking it all in, and it all helps. Yeah, MSDN has a lot that's wrong but a heck of a lot more that's right. (Just be glad *I'm* not authoring the MSDN docs or a lot more would be wrong!) Offer what you can, comment on each other where it's needed, but don't lose focus.
- Tom
-
Okay, back on topic:
Here's some good advise from The Code Project (Visual Studio.NET Homepage) for SQL Server DO's and DONT's By Daniel Turini http://www.codeproject.com/cs/database/sqldodont.asp%5B/url%5D (we are assuming SQL 2000 here):
quote:
DO use referential integrityThis can be a great time saver. Define all your keys, unique constraints and foreign keys. Every validation you create on the server will save you time in the future.
quote:
DO beware of deadlocksAlways access your tables on the same order. When working with stored procedures and transactions, you may find this soon. If you lock the table A then table B, always lock them in this very same order in all stored procedures. If you, by accident, lock the table B and then table A in another procedure some day you'll have a deadlock. Deadlocks can be tricky to find if the lock sequence is not carefully designed.
quote:
DO beware of timeoutsWhen querying a database, the default timeout is often low, like 15 seconds or 30 seconds. Remember that report queries may run longer than this, specially when your database grows.
All the best,
DaleAuthor: An Introduction to SQL Server 2005 Management Studio
-
Because I am stepping in here now I would point it this way.
1) If the number of items will always be fixed or rarely add to then use a CONSTRAINT simple because it has to do no data validation with real concern for the other table. For example if I have a list of States in a table StateID, StateName, PostalAbrev I know there will be 50 states that can be choosen. So instead of an FK or Trigger I write a constraint saying in the main table ColStateIDRef BETWEEN 1 and 50 to keep them from using an invalid option. Much faster and is the preferred where possible.
2) If the data is not fixed and I need to insure referential integrity between the main table and the resource table I use an FK to enforce the data most times (I may have reasons not to do it including performance and the fact the app only presents valid options anyway). In truth and FK is a fancy trigger doing a lookup on the value of the data in the reference table (it just looks at the primary key index or unique index and not actually sample the data). But think of it as doing an
if exists (conditionquery)
which is basically what you will do with a trigger. The was a thread on Unique Index versus Unique Constraint a while back which would relate a little to this.
3) A trigger may be substitued if performance is a concern when realtively few changes occurr to the reference table. You can use the (NOLOCK) hint to alleviate some locking at insert on the main table. In an FK you don't have that ability (at least not yet but who knows maybe they will enhance).
4) Other times I have reference tables where data is not active and I want to enforce referential integrity based on the active data in the reference table (this is an awesome thing to do). I keep full details for historical records this way. I use a trigger to do an exists on the table where the IsActive field (my common field name) = 1 (bit field). You cannot do that kind of relationship with a Foreign Key.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply