The Case Against Compound/Natural Keys

I've been on a crusade against my developers lately after having had enough of fields being added to already-insane primary keys.

Today I was given a script to add a field to make what is now a 15-field primary key, all natural data. The first 9 fields of the key also provide the foreign key into the parent table (although a foreign key constraint is, of course, not used), and it carries on down the line, growing worse and worse. They've all heard me calling for surrogate keys, but they say they need uniqueness among this set of fields. Then when they discover duplicates, they just add another field.

I even suggested having NO primary key, just a non-unique key on the first 4 or 5 fields. But again they say they need to guarantee uniqueness. These tables are bulk-loaded and can contain over 150 million records. There is no query that even comes close to utilizing these fields, it is purely a unique constraint. However, since a unique constraint also creates an index, I didn't see any advantage there.

I'm looking for the words to basically doom this practice once and for all. I've already told them about the degradation of normalization, the storage needed for these unused indexes, etc. They claim there's nothing they can do for now. Migrating to surrogate keys would be non-trivial and isn't a priority, it seems.

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