T-SQL Tutorial

Msg 8111 Level 16 - Cannot define PRIMARY KEY constraint on nullable column in table


Transact sql error message Msg 8111 Level 16 - Cannot define PRIMARY KEY constraint on nullable column in table - means that the column was not defined not null.

Msg 8111 Level 16 Example:

USE model;
GO
CREATE TABLE students( id INT, first_name CHAR(50), last_name CHAR(50),
gender CHAR(1), city CHAR(100), country CHAR(50), dep_id INT);
GO

Invalid alter table:

USE model;
GO
ALTER TABLE students ADD PRIMARY KEY (id);
GO

Message
Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'students'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

Correct alter table:

USE model;
GO
ALTER TABLE students ALTER COLUMN id INT NOT NULL;
GO
ALTER TABLE students ADD PRIMARY KEY (id);
GO

Message
Command(s) completed successfully.

Other error messages: