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_at_seiler.us>

Sent by: oracle-l-bounce_at_freelists.org
01/29/2007 09:25 AM
Please respond to
don_at_seiler.us

To
"jaromir nemec" <jaromir_at_db-nemec.com>

cc
oracle-l <oracle-l_at_freelists.org>
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.

--
http://www.freelists.org/webpage/oracle-l