Primary Key should allow null values - by xor88

Status : 


Sign in
to vote
ID 641362 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 2/4/2011 10:10:28 AM
Access Restriction Public


The primary key of a table does not allow null values. If there is no technical reason for that it should allow null values. This scenario is relevant for multi-column primary keys.
Sign in to post a comment.
Posted by Microsoft on 4/13/2011 at 4:39 PM
Thanks again for taking the time to file this issue. This is being tracked via Connect Feedback Id 299229.

SQL Development.
Posted by Microsoft on 2/8/2011 at 10:20 AM

Thanks for the feedback. We'll keep it on file for consideration in a future release.

Eric Hanson
Program Manager
SQL Server Query Processing
Posted by xor88 on 2/6/2011 at 11:17 AM
I know that I can do this with a unique constraint and that the standard does not allow this. I have a response for both points:

1. Many tools use the PK to do something with it. For example sqlmetal and other ORM tools use the PK metadata to maintain their identity cache. When you only have a unique constraint these tools will not pick up the PK automatically. Very annoying.

2. SQL Server apparently does not have standard compliance as an internal goal (and that is fine with me). I also think that extending the standard is safe, just reducing or changing the standardized spec is dangerous.

Posted by MichaelSmith on 2/4/2011 at 2:59 PM
The technical reason is known as "the SQL standard." I think what you're looking for is the (non-standard behavior) UNIQUE constraint... which treats NULL = NULL.