May 19, 2014 at 9:13 pm
Comments posted to this topic are about the item Running Out Of Foreign Keys
May 20, 2014 at 12:09 am
Nice question Andy, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 20, 2014 at 1:37 am
Good point to share regarding this QotD.
Biggest tables I've been working on are some with about 30-50 columns, 10-20 FKs, 150-400 million rows and sizes between 100-200 GB.
Let's see some other experiences...
Thanks,
Igor
Igor Micev,My blog: www.igormicev.com
May 20, 2014 at 2:01 am
Good question.Thanks for question Andy.
I have seen max 15 foreign keys per table in my database till now.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
May 20, 2014 at 3:04 am
This was removed by the editor as SPAM
May 20, 2014 at 3:14 am
Thank you for the post, Andy, interesting one.
"If 249 and 999 sound familiar those have been the limits on the number of indexes, not foreign keys making those answers incorrect,"... I was one of the victim who fell for this initially and was able to help myself to find the right one. I felt good and bad today... even though I managed to point the correct answer (which felt good) I had my doubts and had to check before submitting (felt bad) as I was not trusting my own knowledge.... pity.
-//edit: The biggest I came across is the: one master table containing 66 cols, 18 FKs, over 6 lakhs rows added yearly (and also archived yearly)
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
May 20, 2014 at 5:37 am
Interesting one, thank you Andy.
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
May 20, 2014 at 7:34 am
Thanks for the question, Andy!
However ... " (for once!)" - I resent that! 😉
I laughed when I read (in the generic maximum capacity specifications link): "the recommended maximum is 253 (...) specifying additional FOREIGN KEY constraints may be expensive for the query optimizer to process."
Just for laughs, create a table with, let's say, a hundred foreign key constraints. Or a table that is referenced by a hundred foreign keys. Then type a query for a simple INSERT (first table) or DELETE (second table), requested an estimated execution plan - and then dare repeat that "additional" FK constraints above this magic 253 number "may be expensive for the query optimizer to process".
(My speculation: due to some internal implementation boundaries, the processing cost for the optimizer changes from "expensive" to "extremely expensive" beyond this magic number of 253).
Oh, and before I forget - the first link in the explanation appears to be broken, I had to use copy and paste to grab the actual URL. Here is an easy link that should work: http://technet.microsoft.com/en-us/library/ms189049.aspx.
May 20, 2014 at 7:42 am
We currently have a poorly designed audit system that requires 391 foreign keys to a single table. The interesting thing is once you get too many foreign keys SQL Server refuses to do DELETEs on that table, because of having to check so many foreign keys. So, the only way to delete rows is to remove and reapply the foreign keys.
May 20, 2014 at 7:58 am
Jedak (5/20/2014)
We currently have a poorly designed audit system that requires 391 foreign keys to a single table. The interesting thing is once you get too many foreign keys SQL Server refuses to do DELETEs on that table, because of having to check so many foreign keys. So, the only way to delete rows is to remove and reapply the foreign keys.
Oh wow - that is so awesome! 😉
What error message do you get - directly related to the foreign keys, or just the optimizer crashing or running out of resources?
When you re-create the FKs, do you use WITH CHECK or WITH NOCHECK, and how long does that take? Have you considered/tried to not remove but just disable the FKs, then enable them again after the removal? (If you do this, make sure to use ALTER TABLE xxx WITH CHECK CHECK CONSTRAINT yyy - the double CHECK is not an error, you actually need this to prevent getting non-trusted constraints).
Are there any plans to redesign this mess into something more manageble?
May 20, 2014 at 8:57 am
I'd say 319 FK's is a poor design, I've got a pretty big Data Warehouse and the most I have is 20.
May 20, 2014 at 9:19 am
Good question, thanx Andy 🙂 but I don`t recall I ever seen more than 10 FK on a single table.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
May 20, 2014 at 9:26 am
Hugo Kornelis (5/20/2014)
Thanks for the question, Andy!Oh, and before I forget - the first link in the explanation appears to be broken, I had to use copy and paste to grab the actual URL. Here is an easy link that should work: http://technet.microsoft.com/en-us/library/ms189049.aspx.
+1, thanx for correcting the link in the answer.
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
May 20, 2014 at 9:27 am
It may not still be the case in current versions of SQL Server, but I thought that was a limit of 255 tables in a query, including tables referenced in a query.
If that is the case, I can't see how an INSERT or DELETE would work if you had 300 foreign key references.
May 20, 2014 at 9:28 am
Hugo Kornelis (5/20/2014)
Thanks for the question, Andy!However ... " (for once!)" - I resent that! 😉
I laughed when I read (in the generic maximum capacity specifications link): "the recommended maximum is 253 (...) specifying additional FOREIGN KEY constraints may be expensive for the query optimizer to process."
Just for laughs, create a table with, let's say, a hundred foreign key constraints. Or a table that is referenced by a hundred foreign keys. Then type a query for a simple INSERT (first table) or DELETE (second table), requested an estimated execution plan - and then dare repeat that "additional" FK constraints above this magic 253 number "may be expensive for the query optimizer to process".
:w00t: That sounds like a really fun experiment. I have a really expensive query I could alter to include some sort of reference to a table with 245 FKs and see what happens.:hehe:
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
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply