Bob Hovious (2/11/2009)
A useful general rule, but not always possible to implement:
* When one of the columns in the candidate key is nullable
* When there is no candidate key (no unique combination of columns)
It may sound like I'm talking out of both sides of my mouth now, but if the table in question doesn't have a VERY short half-life, those are two reasons to simply create an identity key, either as an identity integer or a GUID.
It seems to me that the only thing a primary key constraint gives me that I don't get from a unique key is support for replication. A unique key can ibe the traget of a foreign key constraint, it can include nullable columns, it can be supported by a clustered or a non-clustered index. So if I don't want replication, why should I create an extra column which is in prcatise meaningless to act as primary key?
Here's some code that does nothing useful except sho that unique keys work quite happily:
use tempdb
create table target (
a int unique clustered
)
insert target
select 1 union all
select null
create table pointer (
a int references target(a) on delete cascade,
b int,
c int,
constraint uq_pointer unique clustered (a,b,c)
)
insert pointer
select 1,2,3 union all
select 1,2,null union all
select 1,null,3 union all
select null,2,3 union all
select null,null,3 union all
select null,2,null union all
select 1,null,null union all
select null,null,null
select * from pointer -- returns 8 rows
insert pointer select null,null,null -- this statement fails demonstrating that a
-- unique constraint requires only absence of equality,
-- not presence of inequality
drop table pointer, target
I don't understand why you would want the table to have a very short half-life either. As far as I can see, it's perfectly OK if the table has a very long half-life - in fact it may as well last for ever. If its rows have a very short half life that might be interesting - I have seen some cases where a table is not worth replicating for standby since the time required to reach a decision to cut over to the standby system and do it is high enough that the replicated data in that table would be worthless - the table itself would still be needed because new items would appear in it and be dealt with (quickly).
Tom