Foreign key constraints...WHY??

Question

Foreign key constraints...WHY??

Viewing 15 posts - 16 through 30 (of 70 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

sumit.joshij (4/2/2010)
instead of you can use it in the Interface level handling.

I disagree. The application should not manage database integrity.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

sumit.joshij (4/2/2010)
SO when we create the relation between the PK and FK it check the statistics for PK and FK. it can take time for a huge tranasaction database because it check all the time for its PK and FK relation and after the Every DML command it again resuffual the database table tree.

:blink: could you please elaborate?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 3

CirquedeSQLeil (4/2/2010)
sumit.joshij (4/2/2010)
instead of you can use it in the Interface level handling.
I disagree. The application should not manage database integrity.

BWAA-HAAA!!!! Correct.. let the ORM do it! :-):-D:-P:hehe::w00t:;-)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sumit.joshij Ten Centuries Points: 1040 More actions · Answer 4

Yes, there is not any use to maintain the FK in the table. to some extend it effect the database performance if the table size large and the huge data is maintain.

sumit.joshij Ten Centuries Points: 1040 More actions · Answer 5

I am totally agree with your point that database should maintain its integrity itself. but if interface should have that much of control that it can handle and check the integrity.

if the size of the database table grows and heavy where a huge record to be maintained then it effect the performance while we maintain the FK in the database.

So in each case we have to control the FK constrain in the interface.

Well You should maintain the PK in the database for better application performance.

sumit.joshij Ten Centuries Points: 1040 More actions · Answer 6

Jeff Moden (4/2/2010)
CirquedeSQLeil (4/2/2010)
sumit.joshij (4/2/2010)
instead of you can use it in the Interface level handling.
I disagree. The application should not manage database integrity.
BWAA-HAAA!!!! Correct.. let the ORM do it! :-):-D:-P:hehe::w00t:;-)

I am totally agree with your point that database should maintain its integrity itself. but if interface should have that much of control that it can handle and check the integrity.
if the size of the database table grows and heavy where a huge record to be maintained then it effect the performance while we maintain the FK in the database.
So in each case we have to control the FK constrain in the interface.
Well You should maintain the PK in the database for better application performance.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 7

sumit.joshij (4/2/2010)
I am totally agree with your point that database should maintain its integrity itself. but if interface should have that much of control that it can handle and check the integrity.
if the size of the database table grows and heavy where a huge record to be maintained then it effect the performance while we maintain the FK in the database.
So in each case we have to control the FK constrain in the interface.
Well You should maintain the PK in the database for better application performance.

I completely disagree with you.

If you query a large table joined to another table that does not have proper referential integrity in the database, data accuracy is thrown out the window. Furthermore, performance will be worse. Proper database design requires referential integrity and proper indexing. I have not seen a FK cause database performance issues such as you are indicating. However, I can show database performance that flat out sucks due to the lack of it. The worst of it all would be the inability to have reliable data.

There is a good reason that the application layer and data layer are separate layers - this is one of them.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Paul White SSC Guru Points: 150468 More actions · Answer 8

PaulB-TheOneAndOnly (4/2/2010)
sumit.joshij (4/2/2010)
SO when we create the relation between the PK and FK it check the statistics for PK and FK. it can take time for a huge tranasaction database because it check all the time for its PK and FK relation and after the Every DML command it again resuffual the database table tree.
:blink: could you please elaborate?

I believe sumit.joshij is referring to the overhead of checking foreign key constraints when modifying many records in an operation.

Even with proper indexing to support key relationship checking, the SQL Server optimizer is quite capable of producing poor plans on occasion. Often, this is due to poor cardinality estimation, though I have seen cases where it appears to be doing it just to make my life hard 🙂

It can occasionally be difficult to work around a poor plan since there are no hints that specifically affect the FK-checking portion of the execution plan.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Paul White SSC Guru Points: 150468 More actions · Answer 9

CirquedeSQLeil (4/3/2010)
If you query a large table joined to another table that does not have proper referential integrity in the database, data accuracy is thrown out the window. Furthermore, performance will be worse. Proper database design requires referential integrity and proper indexing.

I am intrigued why "performance will be worse" without PK-FK relationships.

It is certainly true that the optimizer can occasionally make use of a checked and enabled relationship to add extra implied predicates, or (very, very, rarely) to eliminate a join altogether...but it is also true that, in general, PK-FK relationships are a net cost overall - and potentially a very significant one for OLTP workloads. In fact many cases I see of extra 'implied predicates' add nothing of value and simply 'state the obvious'.

I have not seen a FK cause database performance issues such as you are indicating. However, I can show database performance that flat out sucks due to the lack of it.

Again, this is not my experience, as indicated above. Proper design, appropriate indexing, keeping transactions short, (and so on) are much more important for overall performance. I still cannot conceive of a database design that, while otherwise sound, would produce sucky performance simply because a PK-FK relationship did not exist.

PK-FK relationship checking can also be the cause of hard-to-track down deadlocks.

There is a good reason that the application layer and data layer are separate layers - this is one of them.

It simply isn't good enough to state that entity integrity must be enforced by the database layer, and anything else is somehow irresponsible or dangerous. The reality of things is such that many fine systems are built with integrity enforcement built into the mid-tier. You may not like it, I may not like it, but there we are, nevertheless.

I don't mean to rant, but the situation is not at all as clear-cut as database people frequently imagine it to be.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 10

Paul White NZ (4/3/2010)
PaulB-TheOneAndOnly (4/2/2010)
sumit.joshij (4/2/2010)
SO when we create the relation between the PK and FK it check the statistics for PK and FK. it can take time for a huge tranasaction database because it check all the time for its PK and FK relation and after the Every DML command it again resuffual the database table tree.
:blink: could you please elaborate?
I believe sumit.joshij is referring to the overhead of checking foreign key constraints when modifying many records in an operation.

I agree that's what poster should have said but I'm not totally sure that was what poster intended to say.

Either poster has poor comm skills, has no clue about what he's talking about or is the genious that wrote sql kernel 😀

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Paul White SSC Guru Points: 150468 More actions · Answer 11

PaulB-TheOneAndOnly (4/3/2010)
Either poster has poor comm skills, has no clue about what he's talking about or is the genious that wrote sql kernel 😀

:laugh: genious? :laugh:

Actually, I rather suspect that English is not his first language, and you are all being very mean 😛

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Paul White NZ (4/3/2010)
It simply isn't good enough to state that entity integrity must be enforced by the database layer, and anything else is somehow irresponsible or dangerous. The reality of things is such that many fine systems are built with integrity enforcement built into the mid-tier. You may not like it, I may not like it, but there we are, nevertheless.
I don't mean to rant, but the situation is not at all as clear-cut as database people frequently imagine it to be.

Fair enough. I have yet to see an application do it correctly though. Recently I just converted such a heinous scheme to the database layer. In the discovery process we discovered that we had 250K duplicate orders, 1 such order was duplicated 65k times. Querying the database for order records was pitiful and there was no accuracy in reporting. Why were there so many duplicates - the app layer managed the PK and FK architecture. Luckily the records were not true duplicates but that their "FK" and "PK" values were all crud. To rectify that problem took a week of my time to correct the data. Then correcting the code, and moving relationship management to the database also took its time. After moving relationship management to the database, amazingly the performance improved ten fold and there were no more duplicates.

I think the final statement should be more readily applied to developers in this situation - many don't understand database relationship and should not be trying to move database side principles to the application.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Paul White SSC Guru Points: 150468 More actions · Answer 13

CirquedeSQLeil (4/3/2010)
Fair enough. I have yet to see an application do it correctly though.

It does happen. I'm still a 'database person' though, and would prefer to do it in the database - I am just very aware of the disadvantages as well as the advantages!

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

vidya_pande SSCertifiable Points: 5275 More actions · Answer 14

I have seen huge CRM application (Product) is with out FK's. These databases are in hundreds of GB's.

One advandate is definitely there - hiding the DB design.

Also i have seen large data warehouse database without FK's. Huge data loading is done in to these databases on daily basis.

May be giving better performance.

:w00t:

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 15

I'd argue that a large CRM/ERP application without FKs is just poorly designed. There are all sorts of support/administrative contracts in place to prevent you from working with the data because they have poorly designed things and are depending on the application to handle all integrity.

A poor method, IMHO.

It's better to design good RI into your system. You can still have a multi-TB system that runs well with RI in place.