CODEWITHSUNDEEP
sqlindexingprimary-key
Gideon
Gideon

Reputation: 18501

SQL: To primary key or not to primary key?

I have a table with sets of settings for users, it has the following columns:

UserID INT
Set VARCHAR(50)
Key VARCHAR(50)
Value NVARCHAR(MAX)
TimeStamp DATETIME

UserID together with Set and Key are unique. So a specific user cannot have two of the same keys in a particular set of settings. The settings are retrieved by set, so if a user requests a certain key from a certain set, the whole set is downloaded, so that the next time a key from the same set is needed, it doesn't have to go to the database.

Should I create a primary key on all three columns (userid, set, and key) or should I create an extra field that has a primary key (for example an autoincrement integer called SettingID, bad idea i guess), or not create a primary key, and just create a unique index?

----- UPDATE -----

Just to clear things up: This is an end of the line table, it is not joined in anyway. UserID is a FK to the Users table. Set is not a FK. It is pretty much a helper table for my GUI. Just as an example: users get the first time they visit parts of the website, a help balloon, which they can close if they want. Once they click it away, I will add some setting to the "GettingStarted" set that will state they helpballoon X has been disabled. Next time when the user comes to the same page, the setting will state that help balloon X should not be shown anymore.

Upvotes: 3

Views: 1596

Answers (8)

Vinay Pandey
Vinay Pandey

Reputation: 8923

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.

Upvotes: 0

HLGEM
HLGEM

Reputation: 96640

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.

Upvotes: 0

Stefan Steinegger
Stefan Steinegger

Reputation: 64648

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).

Upvotes: 7

Robin Day
Robin Day

Reputation: 102568

How many Key's and Set's do you have? Do these need to be varchar(50) or can they point to a lookup table? 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.

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425813

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.

Upvotes: 2

rein
rein

Reputation: 33465

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.

Upvotes: 0

smok1
smok1

Reputation: 2950

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.

Upvotes: 0

Jonathan
Jonathan

Reputation: 26649

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.

Upvotes: 0

Related Questions