SQL SERVER – Primary Key Must Not Contain NULL – Primary Key are NOT NULL

While reviewing the search engine log for this blog I found lots of search regarding Nullable Primary Key. It is not possible. This post is especially to clear the Not Nullable Primary Key Property. The Allow Nulls property can’t be set on a column that is part of the primary key. All columns that are part of a table’s a primary key must contain aggregate unique values other than NULL.

Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL

About these ads

16 thoughts on “SQL SERVER – Primary Key Must Not Contain NULL – Primary Key are NOT NULL

  1. I don’t understand why a table cannot have a nullable column as part of a COMPOUND primary key.

    In my case, I have a set of a certain kind of attribute that apply either to a specific row of another table, or to all rows of that table. I use null in the foreign key column to indicate all rows.

    Naturally, I also want this foreign key constraint as part of the primary key of the table, such that different rows can have their own attributes.

    Anyway it seems silly to require that all parts of a primary key be non-nullable. Maybe I can see it if there is only one column to a primary key, but not in the case of compound keys.

  2. Hello,

    I disagree with having NULL in primary key. There are plenty of reasons for Primary Key to not having NULL values.

    NULL indicates non-existence of value. When Primary Key contains NULL values it affects the performance as well as keys are not indexed due to NULL values of the column.

    All the issues of the NULL operations are now attached to the primary key which is unique indication of the row. I have seen enough problems in my career where NULL in primary key has created architectural disasters.

    Any operations on NULL results in NULL (besides ISNULL, COALESCE), JOIN clause on NULL values filters out all the NULL values. Index seek suffers due to NULL values in Primary Key.

    I strongly recommend against it.

    Regards,
    Pinal Dave (SQLAuthority.com)

  3. I agree with Logan.. Not allowing NULL’s to be part of PK is just a limitation of the database. The argument that says it slows down the index seek is just saying that db can’t cope with that. Funnily enough putting an empty string will solve the issue so really the issue is the way DB treats NULL’s.

    The perfect example for PK that needs a NULL’s is an entity let’s say Person with relationship to 2 other Entities(GroupRole, Role) when a Person can belong either to a Group of Roles or an individual Role.

    Relationship like that can be easily defined in UML as it describes a real life situation, but can’t be ‘easily’ defined on the database level.

    We ‘developers’ should build tools that help us design a real world situation and we should not be limited by the existing tools like (SQL Server) and we need to demand more from PPL like Microsoft!

    • @developer or @ Pinal:
      For the scenario that developer pointed out, do we have a good solution that will allow Primary key with one null field?

    • Your post suggest that the identity of a person entity should include the role or role group? This is a big mistake. At the very least, it is better to have an unnatural pk such as PersonID and make role and role group a NULLable foreign key column on the person entity. PersonID, with appropriate datatype, would be sufficient to correctly identify each entity without needing to include role.

  4. When logan says that they are using NULL to signify ALL VALUES of certain aggregation function then he is correct with the scenario in a sense that it is quiet possible in case of using CUBE or ROLLUP functions but Pinal is true basically because using a NULL (NO VALUE) in primary key which is mostly non clustered index can create lots of issues in the index data structures and this is the main reason why most of the RDBMS package restrict the feature of using NULL in indentity attributes or including NULLABLE column in composite keys

    FOr logan, it is quiet possible to avoid NULL(ALL VALUES) by uding GROUPING function in the SELECT LIST

  5. this is a question for Pinal. suppose to uniquely define each row, we have a set of columns which one may take null values. I can create a segreggate key (one column) as PK. In my application I set the value of this segreggate value out of those set of columns and take care of null values when happen. This is how I have solved the problem. The question is that if I define a UQ clustered index on this table that consist of all the columns I used to make up PK segreggate key, what is the impact of having NULL in clustered index, isn’t that all nulls will be packed together but still the order for other columns will be preserved as if the null has its own value in ordering? in a way because clustering the PK in this case is not helping me in retrieving data, I wanted to make the clustered index on columns that I always search by. would this clustered index, having one nullable column, help?
    thanks.

  6. As we know, primary key creates unique clustered index automatically , i want to know is there any difference between such created clustered index and normally created clustered index(with create clustered index) ???

    Please clarify.

  7. Hi guys. How can I ignore cascaded field when it is not needed although the field is set as primary key to another table? Hope you can help me or at least give me some advise. thanks. :)

  8. Hi Pinal,

    I need to add one PrimaryKey in my existing table and also in that table 500 records exist..

    I have used below query and it’s working fine for me, can you please explain me how it’s possible

    ALTER TABLE dbo.Employee
    ADD UniqueID int NOT NULL IDENTITY(1,1)
    CONSTRAINT PK_UniqueID PRIMARY KEY NONCLUSTERED

    Thanks
    Mitesh Darji

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s