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!
- when table was created, the
description
column is set toNOT NULL
- I used the update clause to set
description
column toNULL
- When using alter table command to set the column
description
back toNOT 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.
NOT NULL
and immediately try to update the same field toNULL
. Of course it fails. What you haven't done is to is to issue anALTER TABLE
command like so:ALTER TABLE Production.categoriesTest ALTER COLUMN description NVARCHAR(200) NULL
to make the columnNULL
able. AnUPDATE
statement cannot alter the definition, only the data and it is bound by the constraint.