| Author |
Topic |
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
 Posted - 2009-09-30 : 09:51:28
|
I have never been a fan of using fk's. I prefer to just properly code my database's instead of using one. I still have not found a real need for them, since more times than not they limit my capabilities more than improve.Is there really a need for fk's that are benefitial, if you are programming your database accuratly for consistancy?Also, is there any performance gain doing a inner join to a relationship table that has nulled out values on the other columns of that record vs's using a left join and just not have a record or fk on the table?Thanks!
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-09-30 : 10:28:41
|
| There is no need for FK’s if you and every other developer who works on the system or ever will work on the system always produce absolutely perfect code and never make mistakes.If your development team is not quite up to those standards, you should really have foreign keys.CODO ERGO SUM |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-09-30 : 10:48:10
|
A foreign key is also beneficial if you are using a case sensitive collation. As you know, it distinguished between 'a' and 'A'.Also, a foreign key will prohibit you from delete the primary key record by mistake, if a related record exists.
N 56°04'39.26"E 12°55'05.63" |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-09-30 : 11:00:21
|
| It is rare, I think, that data will only ever be changed via the application SPs. So even with a perfect development team there are times when DBAs are requested to apply manual DML statements. FKs will prevent inadvertent violations. I guess there's always exceptions but my two cents is that if you're asking about "best practices" then go with FKs.Be One with the OptimizerTG |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2009-09-30 : 11:09:34
|
quote:
There is no need for FK’s if you and every other developer who works on the system or ever will work on the system always produce absolutely perfect code and never make mistakes.
To add to this, if you and your developers like to clean up other people's data entry mistakes, by all means don't use foreign keys.quote:
I still have not found a real need for them, since more times than not they limit my capabilities more than improve.
Can you elaborate on what "capabilities" are being limited? The only one I can think of is allowing invalid data in the database. That's a "capability" I'm willing to limit. |
|
|
|
Vinnie881
Master Smack Fu Yak Hacker
1231 Posts |
Posted - 2009-09-30 : 13:45:38
|
So what you are trying to say is do not use foreign keys[:-)]j/k. The capabiltiies I was limiting myself on was it sometimes caused errors when wanting to start a database over from scratch for a new deployment, and wanting to truncate the data to start fresh in certain tables. Obviously there are workarounds to it, but it was just a snag. You have all made some very good points and I will start trying to improve my practices and use fk contraints when benefitial.Also regarding the question of:If I have two tables.PK table - > FK table.When I create a record in PK a Null record is created in the FK table with the corresponding key. Does this improve performance over not having a FK on the table, and simply ommiting adding a row to the fk table (In essance I would then use a left join instead of a Inner JOin to that table when looking for records that do not exists.) Obviously if this scenerio arose where I was needing to join to find out this data, that would be a indication of WHY I should have a FK rather then none, but just for argument purposes will the inner join perform quicker connecting to records that are nulled except for the FK relationship rather then ommiting the record completly and using a left join?Thanks
Success is 10% Intelligence, 70% Determination, and 22% Stupidity.\_/ _/ _/\_/ _/\_/ _/ _/- 881 |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-09-30 : 14:03:18
|
| Well I have felt the pain of having to organize deletes in the proper sequence but I don't look at that "inconvenience" as a reason to remove RI from the model.I don't follow the second point - When you create a row in PK why is a "null record" created in FK?Be One with the OptimizerTG |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2009-09-30 : 14:22:54
|
| You can take a look at this link if you need to determine the order to load or delete data from tables to prevent FK violations.Find Table Reference Levelshttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957Remember to create indexes on the FK columns. SQL Server does not do this automatically, and you can have serious performance problems when the FK table has many rows.CODO ERGO SUM |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2009-09-30 : 14:37:55
|
| Foriegn Keys also add a lot of clarity.I too made an app once without FKs and didn't have any problems. But I was the only Developer.When a new Developer comes on board, he or she does not have to guess at how the Tables are Related when writing a join. The FKs make it clear, almost like self-documenting. You can make a Diagram in SQL server drop the tables on the diagram and the relationships magically appear, as long as you are using FKs that is. |
|
|
|
|
best practice, to use or not to use Fk's