CODEWITHSUNDEEP
sql-serverprimary-key
Lokomotywa
Lokomotywa

Reputation: 2844

SQL Server 8111 - but column is NOT NULLABLE

I want to add a primary key constraint to an existing column on an existing table that contains data. The column is not nullable.

However, when I call 

alter table mytable add primary key (mycolumn)

I get an 8111:

Msg 8111, Level 16, State 1, Line 2 Cannot define PRIMARY KEY constraint on nullable column in table 'mytable'

Even if I call both instructions in a row:

alter table mytable alter column mycolumn INT NOT NULL;
alter table mytable add primary key (mycolumn)

I still get an 8111 - and the column description in SQL Server Management Studio confirms, that mycolumn is set to NOT NULL

What can I do this?

Upvotes: 1

Views: 1472

Answers (1)

SqlZim
SqlZim

Reputation: 38023

You need to separate your batches. It would be best to include the schema name as well. 

alter table dbo.mytable alter column mycolumn INT NOT NULL;
go
alter table dbo.mytable add primary key (mycolumn);

rextester demo: http://rextester.com/TZLEWP56616

Upvotes: 1

Related Questions