SQL Server – Understanding Composite Primary Keys

primary-keysql server

I have a small scenario here and I think using composite primary keys would be a good solution. I'm doing this in SSMS. Here is the code to create the table:

CREATE TABLE [PSDesk].[dbo].[Local](
    [UserID] [varchar](10) NOT NULL,
    [FullName] [varchar](50) NOT NULL,
    [ComputerName] [varchar](15) NOT NULL,
    [IPAddress] [varchar](50) NOT NULL,
    [NETFramework] [varchar](50) NOT NULL,
    [ComputerModel] [varchar](50) NOT NULL,
    [UDrive] [varchar](50) NOT NULL,
    primary key (UserID, ComputerName)
)

I would like the UserID and ComputerName columns to be unique, meaning there will only be one record for each combination of UserID and ComputerName. Is this a good method to get the solution i'm looking for? I don't have much experience so i'm simply looking for advice, I threw this together just using my google-fu

Best Answer

Is that syntax correct? Yes. You could also create the PK afterward and with a few different syntax options, but this will build.

But there are a lot of answers to this question. And a lot of questions in this question. "Is this a good idea?" Well it depends – and that is sort of opinion and situation based so the question could be closed if not narrowed down a little with a more specific question.

When you define something as the primary key, you are stating that that is the fact or set of facts which describes one row and only one row.

So for what this table looks like the combination of UserID and ComputerName seem appropriate enough from a database design perspective alone.

There are still other questions though. Questions not in the question but things like:

  1. Why UserID as a varchar? Why not an INT that points back to a User(s) table? This would be a potentially more proper normal form.
  2. Same for ComputerName.
  3. What is the purpose of the table – seems like it describes a computer. Why have the user info in there at all? Why not have a User table and a Computer table and then a table which assigns a user to a computer? (Not saying one way is more right just a question.)
  4. The default behavior for SQL Server when creating a primary key is to make the index on the table clustered when doing it through the GUI – this isn't bad, but I like narrower clustered indexes and clustered indexes which are monotonously increasing and less likely to have index fragmentation and page splits. For that reason, I tend to be more often than not in the "composite clustered index" camp. Sometimes that means I make up a PK and let that be clustered like ComputerUserID – and have that be an identity column. Sometimes I just use that as the clustered and still use the natural key as the primary key as you are trying.
  5. If you created a surrogate key to be the PK for this table and be the clustered index, you could still create a unique index on the combination of ComputerName and UserID and enforce the uniqueness.

Just some food for thought. There are likely many answers to this question. Which is why I'm not 100% sure it will last, but here are some thoughts for now at least.

Related Question