Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Any Benefit to Defining Foreign Keys in SQL Server without enforcing foreign key constraint in SQL Server

3 posters

Go down

Any Benefit to Defining Foreign Keys in SQL Server without enforcing foreign key constraint in SQL Server Empty Any Benefit to Defining Foreign Keys in SQL Server without enforcing foreign key constraint in SQL Server

Post  Jeff Smith Fri Jan 24, 2014 12:40 pm

Is there any benefit to defining the Foreign Key on the fact table in SQL Server with the Foreign Key contrastraint turned off? Does it help SQL Server run queries faster?

The database is a Star Schema with a minimum of sno flaking.

Is there a benefit to defining the foreign key on a dimension table?

Jeff Smith

Posts : 471
Join date : 2009-02-03

Back to top Go down

Any Benefit to Defining Foreign Keys in SQL Server without enforcing foreign key constraint in SQL Server Empty Re: Any Benefit to Defining Foreign Keys in SQL Server without enforcing foreign key constraint in SQL Server

Post  ngalemmo Fri Jan 24, 2014 1:01 pm

Not enforcing constraints can significantly reduce the time required to load data (UPDATE/INSERT), not query it.

An enforced foreign key constraint requires the database to lookup against the referenced table every time a row is inserted or FK updated in the referencing table.  Even with caching this can consume to a considerable amount of time.

Constraint enforcement does not occur during queries (SELECT), however having FK constraints declared can sometimes help the optimizer in creating an appropriate query plan.  Most (probably all) BI tools also leverage FK declarations when reverse engineering the data model into its metadata repository to determine relationships between tables.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

Any Benefit to Defining Foreign Keys in SQL Server without enforcing foreign key constraint in SQL Server Empty Re: Any Benefit to Defining Foreign Keys in SQL Server without enforcing foreign key constraint in SQL Server

Post  BoxesAndLines Fri Jan 24, 2014 3:40 pm

It is also beneficial for the modeling tools. Without a defined FK constraint (enabled or disabled), the modeling tools bring in a list of tables with no relationships. It then takes hours/days to reconnect all of the tables manually based on the size of your data warehouse.

And to ngalemmo's point, I have seen performance improvements when defining PK's as well as FK's.
BoxesAndLines
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

Back to top Go down

Any Benefit to Defining Foreign Keys in SQL Server without enforcing foreign key constraint in SQL Server Empty Re: Any Benefit to Defining Foreign Keys in SQL Server without enforcing foreign key constraint in SQL Server

Post  Jeff Smith Sat Jan 25, 2014 11:41 am

OK so define the foreign keys without the foreign key constraint.

Jeff Smith

Posts : 471
Join date : 2009-02-03

Back to top Go down

Any Benefit to Defining Foreign Keys in SQL Server without enforcing foreign key constraint in SQL Server Empty Re: Any Benefit to Defining Foreign Keys in SQL Server without enforcing foreign key constraint in SQL Server

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum