Re: The Case Against Compound/Natural Keys

Don,

If an FK constraint would help preserve data integrity, then it is called 
for.  Sounds like one of the FK column sets in your large "child" tables 
serves as the leading columns for those tables' PK column sets.  In those 
cases, you already have the FK column sets indexed, so there's no extra 
space cost.  If there are other FK columns or column sets, then adding FK 
constraints to ensure data integrity would necessitate indexes for them to 
facilitate joins and prevent the table-locking that I seem to recall 
happens when inserts and deletes are done on parent tables of child tables 
with unindexed FKs.  I may be out-of-date on that, though - no time to 
check right now.

Surprised you got my email.  I never saw it (them, actually, I resent it) 
come through Oracle-L.

Jack C. Applewhite - Database Administrator
Austin (Texas) Independent School District
512.414.9715 (wk)  /  512.935.5929 (pager)

 Same-Day Stump Grinding!  Senior Discounts!
         -- Mike's Tree Service





"Don Seiler" <don@xxxxxxxxx> 
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
01/29/2007 09:25 AM
Please respond to
don@xxxxxxxxx


To
"jaromir nemec" <jaromir@xxxxxxxxxxxx>
cc
oracle-l <oracle-l@xxxxxxxxxxxxx>
Subject
Re: The Case Against Compound/Natural Keys




My database is actually more of an all-purpose hybrid.  We have OLTP
data where data is entered by sales and updated by customer service,
etc.  We then bulk-load call records and processed billing information
(we are a telecom) that the customer service app uses when customers
call about their bills or question a call.  So we don't have fact vs
dimension tables as you might find in an ideal DW instance.

To be precise, I don't hate natural keys for the sake of hating
natural keys.  It's the composite keys that I hate, and especially
when there are no queries that such a large index would address.

These tables are already partitioned with local indexes.  We are
running the "rolling window" scenario, keeping the most recent 4
months.

Jack: are you suggesting that I put a foreign key constraint/index on
the leading X number of fields already in my primary key
constraint/index?  Because that is what it would be, and is yet
another exhibit of my frustration with this design (or lack thereof).

Don.

Other related posts: