Running Out Of Foreign Keys

Question

Running Out Of Foreign Keys

Viewing 9 posts - 31 through 38 (of 38 total)

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Nice experiment Hugo. Saves me the effort!!

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

Ken SSCoach Points: 17191 More actions · Answer 2

Interesting question Andy! I hope to never work on a table where 253 FKs is a reality. I think the most I have ever seen was about 10.

Eric M Russell SSC Guru Points: 125561 More actions · Answer 3

I've seen fact tables in a star schema model with 20+ foreign keys. By default, foreign keys are not indexed, and nor should they generally speaking. Actually, there are cases where you may want to disable the keys, allowing them to serve for declarative purposes only.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 4

Eric M Russell (5/21/2014)
I've seen fact tables in a star schema model with 20+ foreign keys. By default, foreign keys are not indexed, and nor should they generally speaking. Actually, there are cases where you may want to disable the keys, allowing them to serve for declarative purposes only.

Unfortunately, this is not uncommon when the entire schema is "serialized" from ORMs such as EF, Hibernate and such. A serious culprit is Code-first (rough low whiskey voice whispers in the background) "I got some stories for you young man"

😎

Toreador SSChampion Points: 11365 More actions · Answer 5

Eric M Russell (5/21/2014)
By default, foreign keys are not indexed, and nor should they generally speaking.

If the table is large and the referenced table allows deletion, then foreign keys should always be indexed.

Eric M Russell SSC Guru Points: 125561 More actions · Answer 6

Toreador (5/22/2014)
Eric M Russell (5/21/2014)
By default, foreign keys are not indexed, and nor should they generally speaking.
If the table is large and the referenced table allows deletion, then foreign keys should always be indexed.

Generally speaking, any table, especially reference tables, should not be deleted, only inserted and updated. There is rarely a good reason to delete or update something like zip codes, categories, or purchase orders. For example, if I have something like a Customer_Address table, and the customer changes address, then I'll update the effective_to date column on the original row and insert another row for the new address. If a table is routinely deleted, then I suspect there must be something deficient about it's data model (it's not properly retaining history) or the ETL process is poorly implemented (it should be performing a merge, not delete/re-insert).

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

PChiragS SSCarpal Tunnel Points: 4965 More actions · Answer 7

PChiragS

SSCarpal Tunnel

Points: 4965

May 22, 2014 at 11:32 am

#1715796

nice question Andy.

thanks.

webrunner SSC-Dedicated Points: 31862 More actions · Answer 8

Andy Warren (5/19/2014)
Comments posted to this topic are about the item <A HREF="/questions/foreign+keys/109289/">Running Out Of Foreign Keys</A>

Thanks - great question. I got it wrong, so back to the books!

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Andre Ranieri SSCrazy Points: 2819 More actions · Answer 9

Andre Ranieri

SSCrazy

Points: 2819

January 21, 2015 at 11:02 am

#1771940

+2