SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


No PK or FK's in Warehouse


No PK or FK's in Warehouse

Author
Message
waqqas.zia
waqqas.zia
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 79
Good Morning, Afternoon and Evening (Depending on where in the world you are)

This is not a query just something which I'd like to know the pro's and cons for.

I have inherited a datawarehouse and was using the code here: http://www.sqlservercentral.com/scripts/Primary+Key+(PK)/93288/

to find the Primary Keys, but it appears that there are none.

Within Each table there is always a column called "Locator", eg. Job Tables has Job_locator, stock table has stock_locator. These "Locators" are used to link the a job with its relevant stock data, hence are the keys within the table.

What are the benefits of not stating PK and FK, and using this locator technique?

Surely there must be a method to the madness??
Animal Magic
Animal Magic
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1598 Visits: 13731
All that PK and FK will do for you is introduce data integrity. Depending on the source of the warehouse data the creator may already know that the data is consistent and therefore doesn't need to add any extra checks. or maybe they are not too worried if a header record doesn't exist and their are orphaned child records.

Assuming the tables are indexed i would guess that the no PK/FK is by design, but its a rather large assumption.
Jason-299789
Jason-299789
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2087 Visits: 3232
When you say data warehouse do you mean a Kimbal Star/snowflake Schema?

Depending on the original developer you may find PK's but no FK's, although its not unheard of to have no PK's either (and rather shody IMHO).

In the case of missing PK's I suspect there will be a clustered/unique indexes on either the natural/business key columns and/or the surrogate key column.

In regards to FK's it depends on the type of DW load process, if its a full refresh (Ie Truncate and Reload every table) then you are unlikely to find FK's on the tables due to the extra effort needed to drop all the FK's prior to the clear out, and re initiate them after the load, as well as the headache associated with issues arising when RI is blown.

Ifs its an Incremental load then FK's are likely to be in place as theres no need to drop and recreate them as part of the ETL process, but they may be set up with NoCheck.

_________________________________________________________________________
SSC Guide to Posting and Best Practices
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40086 Visits: 32653
Animal Magic (9/18/2012)
All that PK and FK will do for you is introduce data integrity. Depending on the source of the warehouse data the creator may already know that the data is consistent and therefore doesn't need to add any extra checks. or maybe they are not too worried if a header record doesn't exist and their are orphaned child records.

Assuming the tables are indexed i would guess that the no PK/FK is by design, but its a rather large assumption.


Not at all true. Constraints are used by the optimizer to make choices in how it runs the queries that you submit to it. Those constraints can absolutely affect performance in a positive manner. I have a small example on my blog.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search