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 (https://blog.sqlauthority.com) , BOL
Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.
Once you learn my business secrets, you will fix the majority of problems in the future.
Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.
Essentially I share my business secrets to optimize SQL Server performance.
18 Comments. Leave new
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.
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?
Make use of unique contraint
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.
Hi,
I’ve a question that in sql server, How to find how many tables are there in a Database.how to find how many database are there?
Regards,
Pasha
This will list all Tables in a Database. Hope this helps :)
USE DBName
SELECT * FROM sys.tables
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
can anyone help me how can i set a compound key for my table
A tip: set a unique index, they allow nullable columns.
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.
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.
The only difference is the index name
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. :)
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
Thanks, nice post
what if the primary key is non-clustered ?