|
|
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/Workaround:
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 )
|