|
|
Error Message
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Column Name',
table 'Table Name'; column does not allow nulls.
INSERT fails. The statement has been terminated.
Causes:
As the message suggests, you are trying to insert a new record into a table and one of the columns is being assigned a NULL value but the column does not allow NULLs.
|
To illustrate, let’s say you have the following table definitions:
CREATE TABLE [dbo].[Users] (
[UserName] VARCHAR(10) NOT NULL,
[FullName] VARCHAR(100) NOT NULL,
[Email] VARCHAR(100) NOT NULL,
[Password] VARCHAR(20) NOT NULL,
[CreationDate] DATETIME NOT NULL DEFAULT(GETDATE())
)
There are three ways that the error can be encountered. The first way is when a column is not specified as one of the columns in the INSERT clause and that column does not accept NULL values.
INSERT INTO [dbo].[Users] ( [UserName], [FullName], [Email] )
VALUES ( 'MICKEY', 'Mickey Mouse', 'mickeymouse@disney.com' )
Given this INSERT statement, the [Password] column is not specified in the column list of the INSERT INTO clause. Since it is not specified, it is assigned a value of NULL. But since the column does not allow NULL values, the following error is encountered:
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Password', table 'SQLServerHelper.dbo.Users';
column does not allow nulls. INSERT fails.
The statement has been terminated.
The second way that the error can be encountered is when a NULL value is explicitly assigned to the column that does not allow NULLs.
INSERT INTO [dbo].[Users]([UserName], [FullName], [Email], [Password])
VALUES ( 'MICKEY', 'Mickey Mouse', NULL, 'Minnie' )
As can be seen from this INSERT command, the [Email] column is being assigned a NULL value during the insert but since the column does not allow NULL values, the following error is generated:
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Email', table 'SQLServerHelper.dbo.Users';
column does not allow nulls. INSERT fails.
The statement has been terminated.
The third possible way that the error is encountered is similar to the second one, which is by explicitly assigning a NULL value to a column, as shown below:
INSERT INTO [dbo].[Users] ( [UserName], [FullName], [Email], [Password], [CreationDate] )
VALUES ( 'MICKEY', 'Mickey Mouse', 'mickeymouse@disney.com', 'Minnie', NULL )
The only difference is that the column being assigned to has a default value (in this case, the default value of the [CreationDate] column is current system date and time as generated by the GETDATE() function). Since the column has a default value, you would think that if a NULL value is assigned to it, it will assign the default value instead. However, the following error is encountered:
Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'CreationDate', table 'SQLServerHelper.dbo.Users';
column does not allow nulls. INSERT fails.
The statement has been terminated.
Solution / Work Around:
Regardless of the way on how the error is encountered, if a column does not accept NULL values, always assign a value to it when inserting new records to the table.
INSERT INTO [dbo].[Users] ( [UserName], [FullName], [Email], [Password], [CreationDate] )
VALUES ( 'MICKEY', 'Mickey Mouse', 'mickeymouse@disney.com', 'Minnie', GETDATE() )
If the column does not accept NULL values but has a default value assigned to it and you want that the default value be used for the newly inserted record, just do not include that column in the INSERT statement and the default will automatically be assigned to the column.
INSERT INTO [dbo].[Users] ( [UserName], [FullName], [Email], [Password] )
VALUES ( 'MICKEY', 'Mickey Mouse', 'mickeymouse@disney.com', 'Minnie' )
In this example, since the [CreationDate] column has a default value of GETDATE(), since it is not included in the column list in the INSERT INTO clause, the default value gets assigned to the column.
Another way of explicitly assigning the default value is by using the reserved word DEFAULT in the VALUES list, as can be seen in the following:
INSERT INTO [dbo].[Users] ( [UserName], [FullName], [Email], [Password], [CreationDate] )
VALUES ( 'MICKEY', 'Mickey Mouse', 'mickeymouse@disney.com', 'Minnie', DEFAULT )
|