Are 'Primary Keys' obligatory in SQL Server Design?

Question

Observe the following table model:

CREATE TABLE [site].[Permissions] (
    [ID]     INT REFERENCES [site].[Accounts]( [ID] ) NOT NULL,
    [Type]   SMALLINT NOT NULL, 
    [Value]  INT NULL
);

The site.Accounts->site.Permissions is a one-to-many relationship so 'ID' cannot be made a primary key due to the uniqueness that a PK imposes.

The rows are selected using a WHERE [ID] = ? clause, so adding a phoney IDENTITY column and making it the PK yields no benefit at the cost of additional disk space.

Its my understanding that the targeted platform - SQL Server (2008) - does not support composite PKs. These all add up to my question: If a Primary Key is not used, so something wrong? Or could something be more right?

Answer 1

Your understanding is not correct, SQL Server does support composite primary keys!

The syntax to add one would be

ALTER TABLE  [site].[Permissions] 
ADD CONSTRAINT PK_Permissions PRIMARY KEY CLUSTERED (id,[Type])

Regarding the question in the comments "What is the benefit of placing a PK on the entire table?"

I'm not sure from your description though what the PK would need to be on. Is it all 3 columns or just 2 of them? If it's on id,[Type] then presumably you wouldn't want the possibility that the same id,[Type] combo could appear multiple times with conflicting values.

If it is on all 3 columns then to turn the question around why wouldn't you want a primary key?

If you are going to have a clustered index on your table you could just make that the primary key. If say you made a clustered index on the id column only SQL Server would add in uniqueifiers anyway to make it unique and your columns are so narrow (int,smallint,int) this just seems a pointless addition.

Additionally the query optimiser can use unique constraints to improve its query plans (though might not apply if the only queries on that table really are WHERE [ID] = ?) and it would be pretty wasteful to allow duplicates that you then have to both store and filter out with DISTINCT.