Are 'Primary Keys' obligatory in SQL Server Design?
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 u开发者_运维技巧sed, so something wrong? Or could something be more right?
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
.
精彩评论