We can use “NOT NULL Constraint” to prevent values in a column from containing null data.
Example:
In the following example, when creating a table, we set the NOT NULL Constraint to the [Name] column and add a few records to the table.
|
1 2 3 4 5 6 7 8 |
USE [TestDB] GO CREATE TABLE [dbo].[MyTable1]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NOT NULL ) ON [PRIMARY] GO INSERT INTO [dbo].[MyTable1] VALUES ('Nurullah CAKIR'),('Faruk ERDEM') |
When we try to add data with a NULL value to the [Name] column that we have defined as “NOT NULL”, we receive the following error.
|
1 |
INSERT INTO [dbo].[MyTable1] VALUES (NULL) |
Msg 515, Level 16, State 2, Line 2
Cannot insert the value NULL into column ”, table ”; column does not allow nulls. INSERT fails.
The statement has been terminated.
Drop the table and recreate it as follows. So the [NAME] column can contain NULL values. Then add NULL data to the table with the script below.
|
1 2 3 4 5 6 7 8 9 10 |
USE [TestDB] GO DROP TABLE [dbo].[MyTable1] GO CREATE TABLE [dbo].[MyTable1]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NULL ) ON [PRIMARY] GO INSERT INTO [dbo].[MyTable1] VALUES ('Nurullah CAKIR'),(NULL) |
Then, when we try to add NOT NULL Constraint to the [NAME] column with the following command, we will get an error again. Because the [Name] column contains NULL data.
|
1 |
ALTER TABLE [dbo].[MyTable1] ALTER COLUMN [Name] [varchar](50) NOT NULL |
Msg 515, Level 16, State 2, Line 12
Cannot insert the value NULL into column ”, table ”; column does not allow nulls. UPDATE fails.
The statement has been terminated.
After you have updated the NULL data, “ALTER TABLE …” command will be completed successfully.
|
1 2 3 4 5 |
UPDATE [dbo].[MyTable1] SET [Name]='This value was previously NULL' WHERE [Name] is null GO ALTER TABLE [dbo].[MyTable1] ALTER COLUMN [Name] [varchar](50) NOT NULL GO Select * FROM [dbo].[MyTable1] |
