1

I am doing a exercise to change a column's constraint between null to not null. Table name: Production.categoriesTest. Please review my question and give me suggestion on what I did wrong and how to fix the issue. Thanks!

  1. when table was created, the description column is set to NOT NULL
  2. I used the update clause to set description column to NULL
  3. When using alter table command to set the column description back to NOT NULL, I received an error message.

Error message:

Msg 515, Level 16, State 2, Line 98
Cannot insert the value NULL into column 'description', table 'TSQL2012.Production.categoriesTest'; column does not allow nulls. UPDATE fails.

Create table command:

 create table Production.categoriesTest
    categoryid INT not null identity,
    categoryname nvarchar(15) not null,
    description nvarchar(200) not null,
    constraint PK_Categories1 Primary Key (categoryid)

--update clause make all description column becomes 'NULL"
  update Production.categoriesTest
  set description = NULL
  where categoryid = 8;

The description column became 'NULL'

I did insert a value for description column in the able after the column set to Null.

1
  • 3
    This doesn't make a lot of sense as written because you have created a column declared as NOT NULL and immediately try to update the same field to NULL. Of course it fails. What you haven't done is to is to issue an ALTER TABLE command like so: ALTER TABLE Production.categoriesTest ALTER COLUMN description NVARCHAR(200) NULL to make the column NULLable. An UPDATE statement cannot alter the definition, only the data and it is bound by the constraint. Apr 14, 2014 at 8:16

1 Answer 1

3

When trying to return the column back to NOT NULL SQL Server immediately checks the constraint, which fails because the rows for that column were set to NULL.

For an existing column, you have to update the table to remove any NULL values before you can set it as NOT NULL.

1
  • Thanks for the reply AlwaysLoadingData and Steve. I used update clause to set each row to a value that is not null. I was able to update the column constraint back to 'NOT NULL' Apr 15, 2014 at 3:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.