For example, let's say I have two FKs which make up a composite PK. Would it be a good practice to have auto-generated surrogate keys for this table or leave it as it is? My perception is the former simply b/c it serves the purpose of uniquely identifying a record without complications (it could get worse if the composite PK contained more than two keys for example). Any thoughts?
Yes. Keys are basically used for,
1. Easy searching
2. Unique identification
3. Having a constraint while inserting a row into a particular table.
However primary keys(If we take surrogate, here) doesnt really bother what goes inside other columns. Your DB design is done in such a way, that you don't mind who the caller of the table is, who is going to do insert,update or delete rows from the tables. You see to that your DB design has absolutely all mappings correct, that no wrong data is inserted.
You cannot assume your Front end model to take care of all relations and insert only correct data. Consider a Link table, where a parent,child relation is maintained. If you have a composite primary key on <parent, child> you can be for sure that there is never an entry with just an entry in parent. if you prefer a surrogate key, of course, you can have an unique key, but they can sometime take null. Again you can make the column refering to child 'not null'. But that is where the classic composite keys do the work.
[ August 06, 2006: Message edited by: Arun Kumarr ]