Cannot define PRIMARY KEY constraint on nullable column in table ‘%1!’.

Error Message:
Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table ‘%1!’.

Severity level:
16.

Description:
This error message appears, when you try within a CREATE TABLE statement to create a PRIMARY KEY constraint on a column or a set of columns that allow NULLs.

Consequences:
The T-SQL statement can be parsed, but causes the error at runtime.

Resolution:
Error of the Severity level 16 are generated by the user and can be fixed by the SQL Server user. In this case all columns over which the PRIMARY KEY constraint should be declared needs to be explicitly created as NOT NULL.

Versions:
All versions of SQL Server.

Example(s):
USE tempDB
GO
CREATE TABLE #t
(
 c1 INT NULL PRIMARY KEY
)
GO

Remarks:
In the above example the NULL keyword has been specified for the column c1. This raises the error. In contrast to a UNIQUE constraint where one and only one NULL is allowed does a PK constraint not allow any NULL markers. Removing the NULL keyword turns the above example into a valid statement.




Related Articles :

  • No Related Articles Found

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |