November 29, 2002 at 3:32 pm
I recently became the DBA for my group and found out that most the databases used by other developers do not have any table relations at all. I was curious, other than obviating the purpose of an RDBMS, what kind of impact (no primary keys, no foreign keys, no explicit relations at all) this will have on performance? Actually, another way of looking at it: what would be the reasons for not setting primary keys and foreign keys (if any)?
November 29, 2002 at 4:28 pm
I cant think of a good reason not to use a primary key, but omitting the foreign keys has its place for reducing server load. Having an fkey adds a join to every operation. Most times its efficient, sometimes you need every bit of perf you can get. Then too you sometimes find apps that came with the schema, adding fkeys may break things depending on how the app was written.
Andy
December 2, 2002 at 5:16 am
Depedning on the FK constraint I rarely use FKs to enforce itegrity. Usuall they are for silly things like a support table of options with a range or values 1-n. I usually do a check constraint in this place. Has the same effect as an FK constriant but without the extra reads induced by FKs. They can slow dwn large processes and alternatives should be looked for before an FK is used. In other instances where data must be check against data in another table I may check in an SP or beforehand to work with a set within the app and if I need to cascade a delete or update to another table I write triggers instead to handle this. These are ways to avoid FKs, but I am sure there are others.
December 9, 2002 at 3:12 am
quote:
if I need to cascade a delete or update to another table I write triggers instead to handle this. These are ways to avoid FKs, but I am sure there are others.
I always thought that Cascade Update & Delete thru FK in SQL2000 induce less overhead than using triggers since this was a new feature in SQL2000. Before that, I always use trigers in SQL7.
Antares686, Could you please explain me why you think cascade update/delete using FK causes more overhead than using its trigger counterpart?
Thanks
Hendry
December 9, 2002 at 8:31 am
I am currently suffering through an application where NO foreign keys are defined. Ya --- the data base is fast but we have bug city from broken pointers out the wazoo.
I say create FK constraints when they exist. It's easier to add a row in the FK table than it is to update your check constraints. Also, you can put some words behind your unique values.
If you have a constraint that says this column can be 1-5, what does 1 mean? What does 2 mean?
If you have FK table, you can see that 1 = Employee, 2=Former Employee, 3=Contracter, 4=Vendor, 5=Unknown.
Maybe a small thing but there is value in leaving this intelligence behind when you move on to bigger and better things. Just like SQL Server has system tables to define its internal structures.
December 9, 2002 at 5:00 pm
Check constraints are highly recommended always over FK constraints for specifically for performance, especially in high transaction environments. Check constraints are easy to alter and like anything you should always keep you DB documented. I would much rather read from memory a list of possible values rather than making the IO trip to the drive or using extra memory to cache a large table unneeded set for FK comparison. I will say Dons example of 5 possible items is not a good reason to put in an FK as opposed to a check constraint and if you are going to remove a group of data for relational reasons from the DB it is to save space so you should be using numeric values not words in any relationship. This is not to say there are not specific situations that require the attention of an FK.
As for their being the possiblity of errors without and FK. This is possible even with FKs. Sure they cannot choose a value outside the range but they can choose the wrong value. But the same is true with a Check constraint without the extra reads. You can only choose a value that is within the reange set forth by the Check constraint. And as for adding items to the Check constraint there are ways of dealing with this as well.
Now the reason I mentioned Cascading Updates and deletes thru triggers. Although SQL 2000 does implement Cascading updates and deletes quite well it does have an extra requirement which when choosing not to use an FK is key. FKs to perform cascading Updates and Deletes must be set to "Enforce relationships for INSERTs and UPDATEs", so for me to get around the INSERTs I must write my own triggers for Updates and Deletes. Now understanding cascading is more or less an internal trigger created within SQL 2000 so I am really doing no extra work, except I have to do it for me, not SQL. And coming from the SQL 7 world I am fully aware of exactly how to do this without SQLs help.
In summary, everyone has to make their own decission about whether or not to use FKs and your experience should have some berring on that choice. But there are ways to avoid it thru Check constraints and good solid coding that can provide many bennifits in high transaction environments.
December 9, 2002 at 6:22 pm
quote:
In summary, everyone has to make their own decission about whether or not to use FKs and your experience should have some berring on that choice.
Too true. To quote a truism: "Blanket statements are always false."
I think having multiple ways to accomplish the same end is a good thing. You can use what makes sense for your particular situation. You can choose between clarity, documentation, ease of maintenance, performance, etc. on a case-by-case basis and use whatever best fits your need.
December 9, 2002 at 7:13 pm
Good discussion. I think I disagree with Antares, I value the look up values on small "code" tables more than I do a fkey into a million record table, and the small tables are pretty cheap to fkey to in terms of disk io.
As far as bad pointers, we get them too, just meant we had to run jobs to find them and fix where possible, report where we couldnt automate the fix. Usually the result of errors rather than outright bad code. Network connection drop, deadlock, etc. Our newest app doesnt use fkeys either (its build on data model from older app and the old app still runs), we just put a lot of work into keeping things consistent. We should pick up some speed and have a better app because of it...I hope!
As I write this though, I'd say there is a case to be made for having the fkeys in place in dev/QA to help catch problems even if you dont use in production.
Andy
December 10, 2002 at 5:32 am
I do agree with the last statement on in QA/Dev test with them but also test without if you are considering. On small tables it is just simplier to as a check constraint and get performance improvements when the FK table is high transaction. But I still would especially on a large table that would be referenced do the same as long as there is no major concern with bad data or missing pots in a INDENTITY column. There are several tricks I use to keep things in order that I will try to get out soon. But, I personally prefer no PK-FK relationships to keep my transactions up for the needs I have.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply