Updating a composite primary key

I am struggling with the philosophical discussions about whether or not to use composite primary keys on my SQL Server database. I have always used the surrogate keys in the past and I am challenging myself by leaving my comfort zone to try something different. I have开发者_开发问答 read many discussion but can't come to any kind of solution yet. The struggle I am having is when I have to update a record with the composite PK.

For example, the record in questions is like this:

ContactID, RoleID, EffectiveDate, TerminationDT

The PK in this case is the (ContactID, RoleID, EffectiveDate). TerminationDT can be null.

If in my UI, the user changes the RoleID and then I need to update the record. Using the surrogate key I can do an Update Table Set RoleID = 1 WHERE surrogateID = Z. However, using the Composite Key way, once one of the fields in the composite key changes I have no way to reference the old record to update it without now maintaining somewhere in the UI a reference to the old values.

I do not bind datasources in my UI. I open a connection, get the data and store it in a bucket, then close the connection. What are everyone's opinions? Thanks.

What you're saying by referencing ideas like "the old record" is that the relationship has a semantic meaning independent of the composite key elements. The very fact that you NEED a key to refer to the old record means that the composite key elements are not sufficient. You still maintain foreign keys, of course, but it seems pretty clear to me that you need a primary key here, and it's not a surrogate key in the usual sense.

Use the surrogate key guilt-free, in my opinion.

You're on the right track, but looking at your primary keys, I suggest you look into normalization. I think you should try to stay away from using composite keys, and try using one primary key per table, unless necessary. You could take the RoleID, for example, and make it it's own table, with role descriptions, and whatever else defines a role, and put it as a foreign key reference in your other tables.

First the easy part:

It looks like your table is a cross-reference, no? In this case I have found composite keys to work extremely well, IF, and this is a big IF, If your UI and other libraries understand them. Many UIs these days do not, and it sounds like yours does not. Therefore:

1) You are correct to use a composite key for a cross-reference, that is one of the few places where it always makes sense, but:

2) You can just as well put a surrogate key in there if it makes things easier for the rest of the coding, but remember to add a unique constraint on those three columns.

Going further, you probably need a check constraint that makes sure there are no overlaps in the date ranges, since databases do not natively support "ranged primary keys" which is what it looks like you are doing.

Now with that being said, let me confuse things further. Why are you updating this row? Wouldn't the correct operation be to put a termination date on the original and force creation of a new row indicating the contact's new role? In which case you may get to keep the composite and monkey up the UI a bit to allow/disallow certain actions.