SQL: To primary key or not to primary key?

Should I create a primary key on all three columns (userid, set, and key) Make this one Using surrogate primary key will result in an extra column which is not used for other purposes Creating a UNIQUE INDEX along with surrogate primary key is same as creating a non-clustered PRIMARY KEY and will result in an extra KEY lookup which is worse for performance Creating a UNIQUE INDEX without a PRIMARY KEY will result in a HEAP-organized table which will need an extra RID lookup to access the values: also not very good.

Should I create a primary key on all three columns (userid, set, and key) Make this one. Using surrogate primary key will result in an extra column which is not used for other purposes. Creating a UNIQUE INDEX along with surrogate primary key is same as creating a non-clustered PRIMARY KEY, and will result in an extra KEY lookup which is worse for performance.

Creating a UNIQUE INDEX without a PRIMARY KEY will result in a HEAP-organized table which will need an extra RID lookup to access the values: also not very good.

Having composite unique keys is mostly not a good idea. Having any business relevant data as primary key can also make you troubles. For instance, if you need to change the value.

If it is not possible in the application to change the value, it could be in the future, or it must be changed in an upgrade script. It's best to create a surrogate key, a automatic number which does not have any business meaning. Edit after your update: In this case, you can think of having conceptually no primary key, and make this three columns either the primary key of a composite unique key (to make it changeable).

1 Yes. Imagine the following problems that could arise from a composite key situation. What happens if some of the data in the key needs to change?

What if you need to join to this table? You would then need to duplicate the business data in the joining table – 1800 INFORMATION May 6 '09 at 8:32 @1800: why change the data in the key? If you want to assign a new value to the new (user, set, key), you just insert or update it, you do not reassign the existing value from other user :) And a surrogate PRIMARY KEY will just result in an extra KEY LOOKUP/RID LOOKUP.

– Quassnoi May 6 '09 at 8:38 I've added an update to my question, stating that the information in the settings table, will not be used by any other table, there will be no joins. In the words of Jonathan: this is an end of the line table. – Gidon May 6 '09 at 8:55 Updated my answer according to your update.

– Stefan Steinegger May 6 '09 at 9:14 I still think it would not be a good idea to use those columns as a primary key, even if there are no joins. At least there is the "join" to the GUI. If you later decide one of (user,set,key) is mutable you might be in trouble.

If you put this data in a cookie this might be a bigger security risk than putting a surrogate primary key there. And so forth. The teensy bit of performance might not be worth it.

– hstoerr May 6 '097 at 7:38.

If you can convert this Set and Key into SetId and KeyId then you can create your primary key on the 3 integer values which will be much faster.

The number of keys and sets are not set in advance, it's pretty dynamic. I understand your lookup table idea, but for manageability purposes went for this "quick-and-dirty" solution. – Gidon May 6 '09 at 8:59 I know it's almost two years later, but if the sets are dynamic then you must use an identity column as the primary key.

I mean, seriously. – Theodore R. Smith Jan 27 at 14:15.

I would probably try to make sure that UserID was a unique identifier, rather than having duplicates of UserID throughout the code. Composite keys tend to get confusing later on in your code's life. I'm assuming this is a lookup field for config values of some kind, so you could probably go with the composite key if this is the case.

The data is already there. You can guarantee it's uniqueness using the primary key. If you change your mind and decide later that it isn't appropriate for you, you can easily add a SettingId and make the original composite key a unique index.

Create one, separate primary key. No matter what how bussines logic will change, what new rules will have to be applied to your Key VARCHAR(50) field - having one primary key will make you completly independent of bussines logic.

In my experience it all depends how many tables will be using this table as FK information. Do you want 3 extra columns in your other tables just to carry over a FK? Personally I would create another FK column and put a unique constraint over the other three columns.

This makes foreign keys to this table a lot easier to swallow.

You could create a compound (composite) key on all three columns. But I would be tempted to add an identity field, as using a compound key would give you a mix of an INT field with 2 VARCHAR fields making up that key, which doesn't sit well with me.

I'm not a proponent of composite keys, but in this case as an end of the line table, it might make sense. However, if you allow nulls in any of these three fields becasue one or more of the values is not known at the time of the insert, there can be difficulty and a unique index might be better.

Better have UserID as 32 bit newid() or unique identifier because UserID as int gives a hint to the User of the probable UserID. This will also solve your issue of composite key.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.