Click here to Skip to main content
15,890,825 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
This is in regard to SQL Server (i.e., not MySql or other SQL flavors).

It seems to me that a primary key is really just a clustered index, unique constraint, and not null constraint. Do primary keys add any real value, aside from the side effects of combining all those concepts into one concept (e.g., allowing ORM's to work easily and making it possible for newbies to easily create tables easily)?
Posted
Comments
Abhinav S 11-Apr-11 13:37pm    
"really just a clustered index, unique constraint, and not null constraint"

I would think that is a lot.
:)

First of all I agree with Abhinav, it's quite a lot :).

From the theory point of view, primary key is always unique and not null. Index isn't 'theoretical' part of the primary key. However, all the databases (well, at least the ones that are actually usable) do create an unique index to quickly enforce the uniqueness.

Primary key is also in a different position when it comes to optimization, cursor handling etc. So to answer a bit differently, not having a primary key may lead to performance problems and/or even obstacles when creating the software (depending on the technologies used).

And about the indexes. Index type doesn't matter at all. Clustered is the default type but if you like, you can use non-clustered and actually this would be my decision in certain situations.

[Addition]
When creating a primary key, the DBMS decides how it's implemented (most efficiently). This behavior may change between versions. So when defining the primary key as a constraint, you define the need, not it's implementation. On the other hand if you define how uniqueness is enforced you also define (partly) the implementation.

[Addition]
create table pk_test (
   val1  bigint not null
)
set nocount on
declare @counter bigint;
begin
   set @counter = 1;
   while @counter <= 1000000 begin
      insert into pk_test values (@counter, @counter);
      set @counter = @counter + 1;
   end;
end;
alter table pk_test add constraint uq unique (val1);
select * from pk_test where val1 = 654321;
alter table pk_test add constraint pk primary key nonclustered (val1);
select * from pk_test where val1 = 654321;
 
Share this answer
 
v3
Comments
AspDotNetDev 11-Apr-11 13:58pm    
Ah, so there may be optimization behind the scenes so SQL Server treats primary keys different in some way? Can you give any specific examples, or is this just a theory of yours?
Wendelius 11-Apr-11 14:19pm    
No, it's not just a theory, but I'm not meaning that there would be anything 'hidden' inside Sql Server (I wouldn't know if there is) :) If you think about the cardinality of the primary key, it's always as high as possible and containing actual values, guaranteed by the DBMS. Basically the treatment is quite the same, but the effect is more on the calculated cost for a query.
AspDotNetDev 11-Apr-11 14:34pm    
I'm not sure what you mean by "the effect is more on the calculated cost for a query".
Wendelius 11-Apr-11 15:59pm    
For example if a single row fetch is going to be done, the permutation where PK is involved likely gets the lowest cost (of course not in all cases) since it's typically the most efficient access path for a single row.
AspDotNetDev 11-Apr-11 16:36pm    
Are you saying that would be different for a unique not null constraint? Or are you saying the primary key is just a convenient way for the database engine to know which fields to use when optimizing query plans?
After doing some research, I found one difference. According to Microsoft TechNet, "all published tables in transactional replication must contain a declared primary key." So transactional replication is one thing that requires a primary key rather than a unique/not null constraint. Not sure if there is a conceptual difference, but it seems that there is at least one practical difference.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900