I’m using the following query:
INSERT INTO role (name, created) VALUES ('Content Coordinator', GETDATE()), ('Content Viewer', GETDATE())
However, I’m not specifying the primary key (which is id
). So my questions is, why is sql server coming back with this error:
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'id', table 'CMT_DEV.dbo.role'; column does not allow nulls. INSERT fails.
The statement has been terminated.
asked Apr 4, 2012 at 14:33
0
I’m assuming that id
is supposed to be an incrementing value.
You need to set this, or else if you have a non-nullable column, with no default value, if you provide no value it will error.
To set up auto-increment in SQL Server Management Studio:
- Open your table in
Design
- Select your column and go to
Column Properties
- Under
Indentity Specification
, set(Is Identity)=Yes
andIndentity Increment=1
answered Apr 4, 2012 at 14:36
CurtisCurtis
101k65 gold badges270 silver badges351 bronze badges
3
use IDENTITY(1,1)
while creating the table
eg
CREATE TABLE SAMPLE(
[Id] [int] IDENTITY(1,1) NOT NULL,
[Status] [smallint] NOT NULL,
CONSTRAINT [PK_SAMPLE] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
)
Nick
1,1583 gold badges24 silver badges36 bronze badges
answered Jan 9, 2018 at 19:41
If the id
column has no default value, but has NOT NULL
constraint, then you have to provide a value yourself
INSERT INTO dbo.role (id, name, created) VALUES ('something', 'Content Coordinator', GETDATE()), ('Content Viewer', GETDATE())
answered Apr 4, 2012 at 14:40
Andy IrvingAndy Irving
2,6571 gold badge14 silver badges11 bronze badges
Encountered the same issue. This is something to do with your table creation. When you created table you have not indicate ‘ID
‘ column to be Auto Increment
hence you get this error. By making the column Primary Key
it cannot be null
or contain duplicates hence without Auto Increment
pretty obvious to throw column does not allow nulls. INSERT fails
.
There are two ways you could fix this issue.
1). via MS SQL Server Management Studio
-
Got to MS SQL Server Management Studio
-
Locate your table and right click and select Design
-
Locate your column and go to Column Properties
-
Under Indentity Specification: set (Is Identity)=Yes and Indentity
Increment=1
2). via ALTER SQLs
ALTER TABLE table DROP COLUMN id; // drop the existing ID
ALTER TABLE table ADD id int IDENTITY(1, 1) NOT NULL; // add new column ID with auto-increment
ALTER TABLE table ADD CONSTRAINT PK_ident_test PRIMARY KEY CLUSTERED (id); // make it primary key
answered Dec 15, 2022 at 7:02
Du-LacosteDu-Lacoste
11.3k2 gold badges67 silver badges51 bronze badges
0
You either need to specify an ID in the insert, or you need to configure the id column in the database to have Identity Specification = Yes.
answered Apr 4, 2012 at 14:38
JupiterP5JupiterP5
3181 silver badge10 bronze badges
As id is PK it MUST be unique and not null.
If you do not mention any field in the fields list for insert it’ll be supposed to be null or default value.
Set identity (i.e. autoincrement) for this field if you do not want to set it manualy every time.
answered Apr 4, 2012 at 14:38
quzaryquzary
2851 silver badge4 bronze badges
You need to set autoincrement property of id column to true when you create the table or you can alter your existing table to do this.
answered Apr 4, 2012 at 14:44
you didn’t give a value for id. Try this :
INSERT INTO role (id, name, created) VALUES ('example1','Content Coordinator', GETDATE()), ('example2', 'Content Viewer', GETDATE())
Or you can set the auto increment on id field, if you need the id value added automatically.
answered May 9, 2017 at 2:04
I had a similar problem and upon looking into it, it was simply a field in the actual table missing id
(id
was empty/null
) — meaning when you try to make the id
field the primary key
it will result in error because the table contains a row with null
value for the primary key
.
This could be the fix if you see a temp table associated with the error. I was using SQL Server Management Studio.
answered Nov 1, 2019 at 15:22
WARNING! Make sure the target table is locked when using this method
(As per @OnurOmer’s comment)
if you can’t or don’t want to set the autoincrement property of the id, you can set value for the id for each row like this:
INSERT INTO role (id, name, created)
SELECT
(select max(id) from role) + ROW_NUMBER() OVER (ORDER BY name)
, name
, created
FROM (
VALUES
('Content Coordinator', GETDATE())
, ('Content Viewer', GETDATE())
) AS x(name, created)
answered Apr 16, 2018 at 2:13
robotikrobotik
1,8181 gold badge20 silver badges25 bronze badges
2
RULE: You cannot IGNORE those colums that do not allow null values, when inserting new data.
Your Case
- You’re trying to insert values, while ignoring the
id
column, which does not allow nulls. Obviously this won’t work. - Gladly for you the «Identity Specification» seems to automatically fill the not nullable
id
values for you (see selected answer), when you later execute the insert query.
My Case
- The problem (while using SSMS): I was having this error when trying to add a new non-nullable column to an already existing table with data. The error I’d got was:
Cannot insert the value NULL into column ‘id_foreign’, table ‘MyDataBase.dbo.Tmp_ThisTable’; column does not allow nulls. INSERT fails.
The statement has been terminated.
- The solution:
- I created the column I needed
id_foreign
, allowing nulls. - I edited/inserted all the required values for
id_foreign
. - Once the values where in place, I went back and unchecked the «Allow Nulls» checkbox. Now the error was gone.
- I created the column I needed
answered Jul 26, 2022 at 14:54
carloswm85carloswm85
1,28913 silver badges21 bronze badges
I’m using the following query:
INSERT INTO role (name, created) VALUES ('Content Coordinator', GETDATE()), ('Content Viewer', GETDATE())
However, I’m not specifying the primary key (which is id
). So my questions is, why is sql server coming back with this error:
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'id', table 'CMT_DEV.dbo.role'; column does not allow nulls. INSERT fails.
The statement has been terminated.
asked Apr 4, 2012 at 14:33
0
I’m assuming that id
is supposed to be an incrementing value.
You need to set this, or else if you have a non-nullable column, with no default value, if you provide no value it will error.
To set up auto-increment in SQL Server Management Studio:
- Open your table in
Design
- Select your column and go to
Column Properties
- Under
Indentity Specification
, set(Is Identity)=Yes
andIndentity Increment=1
answered Apr 4, 2012 at 14:36
CurtisCurtis
101k65 gold badges270 silver badges351 bronze badges
3
use IDENTITY(1,1)
while creating the table
eg
CREATE TABLE SAMPLE(
[Id] [int] IDENTITY(1,1) NOT NULL,
[Status] [smallint] NOT NULL,
CONSTRAINT [PK_SAMPLE] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
)
Nick
1,1583 gold badges24 silver badges36 bronze badges
answered Jan 9, 2018 at 19:41
If the id
column has no default value, but has NOT NULL
constraint, then you have to provide a value yourself
INSERT INTO dbo.role (id, name, created) VALUES ('something', 'Content Coordinator', GETDATE()), ('Content Viewer', GETDATE())
answered Apr 4, 2012 at 14:40
Andy IrvingAndy Irving
2,6571 gold badge14 silver badges11 bronze badges
Encountered the same issue. This is something to do with your table creation. When you created table you have not indicate ‘ID
‘ column to be Auto Increment
hence you get this error. By making the column Primary Key
it cannot be null
or contain duplicates hence without Auto Increment
pretty obvious to throw column does not allow nulls. INSERT fails
.
There are two ways you could fix this issue.
1). via MS SQL Server Management Studio
-
Got to MS SQL Server Management Studio
-
Locate your table and right click and select Design
-
Locate your column and go to Column Properties
-
Under Indentity Specification: set (Is Identity)=Yes and Indentity
Increment=1
2). via ALTER SQLs
ALTER TABLE table DROP COLUMN id; // drop the existing ID
ALTER TABLE table ADD id int IDENTITY(1, 1) NOT NULL; // add new column ID with auto-increment
ALTER TABLE table ADD CONSTRAINT PK_ident_test PRIMARY KEY CLUSTERED (id); // make it primary key
answered Dec 15, 2022 at 7:02
Du-LacosteDu-Lacoste
11.3k2 gold badges67 silver badges51 bronze badges
0
You either need to specify an ID in the insert, or you need to configure the id column in the database to have Identity Specification = Yes.
answered Apr 4, 2012 at 14:38
JupiterP5JupiterP5
3181 silver badge10 bronze badges
As id is PK it MUST be unique and not null.
If you do not mention any field in the fields list for insert it’ll be supposed to be null or default value.
Set identity (i.e. autoincrement) for this field if you do not want to set it manualy every time.
answered Apr 4, 2012 at 14:38
quzaryquzary
2851 silver badge4 bronze badges
You need to set autoincrement property of id column to true when you create the table or you can alter your existing table to do this.
answered Apr 4, 2012 at 14:44
you didn’t give a value for id. Try this :
INSERT INTO role (id, name, created) VALUES ('example1','Content Coordinator', GETDATE()), ('example2', 'Content Viewer', GETDATE())
Or you can set the auto increment on id field, if you need the id value added automatically.
answered May 9, 2017 at 2:04
I had a similar problem and upon looking into it, it was simply a field in the actual table missing id
(id
was empty/null
) — meaning when you try to make the id
field the primary key
it will result in error because the table contains a row with null
value for the primary key
.
This could be the fix if you see a temp table associated with the error. I was using SQL Server Management Studio.
answered Nov 1, 2019 at 15:22
WARNING! Make sure the target table is locked when using this method
(As per @OnurOmer’s comment)
if you can’t or don’t want to set the autoincrement property of the id, you can set value for the id for each row like this:
INSERT INTO role (id, name, created)
SELECT
(select max(id) from role) + ROW_NUMBER() OVER (ORDER BY name)
, name
, created
FROM (
VALUES
('Content Coordinator', GETDATE())
, ('Content Viewer', GETDATE())
) AS x(name, created)
answered Apr 16, 2018 at 2:13
robotikrobotik
1,8181 gold badge20 silver badges25 bronze badges
2
RULE: You cannot IGNORE those colums that do not allow null values, when inserting new data.
Your Case
- You’re trying to insert values, while ignoring the
id
column, which does not allow nulls. Obviously this won’t work. - Gladly for you the «Identity Specification» seems to automatically fill the not nullable
id
values for you (see selected answer), when you later execute the insert query.
My Case
- The problem (while using SSMS): I was having this error when trying to add a new non-nullable column to an already existing table with data. The error I’d got was:
Cannot insert the value NULL into column ‘id_foreign’, table ‘MyDataBase.dbo.Tmp_ThisTable’; column does not allow nulls. INSERT fails.
The statement has been terminated.
- The solution:
- I created the column I needed
id_foreign
, allowing nulls. - I edited/inserted all the required values for
id_foreign
. - Once the values where in place, I went back and unchecked the «Allow Nulls» checkbox. Now the error was gone.
- I created the column I needed
answered Jul 26, 2022 at 14:54
carloswm85carloswm85
1,28913 silver badges21 bronze badges
- Remove From My Forums
-
Question
-
N0ob here.
This error message keeps getting generated when i try to submit a particular form on my site. This used to work fine until i changed servers and exported my database to the new server. As far as i can tell everything is fine but then again, like i said, i’m a total newbie so i’m not seeing something i’m sure…
Here is the full Error message..
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column ‘ReviewID’, table ‘reviews.dbo.OR_Comments’; column does not allow nulls. INSERT fails.
The statement has been terminated.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): Cannot insert the value NULL into column 'ReviewID', table 'reviews.dbo.OR_Comments'; column does not allow nulls. INSERT fails. The statement has been terminated.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1005 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +149 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +404 System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values) +447 System.Web.UI.DataSourceView.Insert(IDictionary values, DataSourceViewOperationCallback callback) +72 System.Web.UI.WebControls.DetailsView.HandleInsert(String commandArg, Boolean causesValidation) +390 System.Web.UI.WebControls.DetailsView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +602 System.Web.UI.WebControls.DetailsView.OnBubbleEvent(Object source, EventArgs e) +95 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.DetailsViewRow.OnBubbleEvent(Object source, EventArgs e) +109 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +132 System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +177 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746
Any help appreciated!
Answers
-
Here’s what you should be paying attention to:
Exception Details: System.Data.SqlClient.SqlException: Cannot insert the value NULL into column ‘ReviewID’, table ‘reviews.dbo.OR_Comments’; column does not allow nulls. INSERT fails.
The statement has been terminated.This means that ReviewID column of OR_Comments table doesn’t allow NULL values. You should either:
-
Allow NULL values to be inserted in this column
-
Configure your data source not to insert anything in this column and let server generate the value (if this is an identity column)
-
-
You can allow NULLs on the table by using Management Studio. Right click on the table and choose design, and check the box under the «Allow Nulls» next to the column name you want to allow nulls on.
SQL Server Error Messages — Msg 515 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 )
On Transact SQL language the Msg 515 Level 16 — Cannot insert the value NULL into column means that the column does not allow nulls and the insert fails.
Msg 515 Level 16 Example:
We have the table teachers:
USE model;
GO
CREATE TABLE teachers(
ID INT IDENTITY NOT NULL PRIMARY KEY,
Name VARCHAR(250) NOT NULL,
Department VARCHAR(250) NOT NULL);
GO
Invalid insert:
USE model;
GO
INSERT INTO teachers(id, name, department)
VALUES ('Olivia Wilson', null), ('Ethan Davis', null);
GO
Message |
---|
Msg 515, Level 16, State 2, Line 1 |
Cannot insert the value NULL into column ‘Department’, table ‘model.dbo.teachers’; column does not allow nulls. INSERT fails. The statement has been terminated. |
Correct insert:
USE model;
GO
INSERT INTO teachers(name, department)
VALUES ('Olivia Wilson', 'Anthropology'), ('Ethan Davis', 'Biology');
GO
Message |
---|
(2 row(s) affected) |
Other error messages:
- Conversion failed when converting date and/or time from character string
- Is not a defined system type
- Conversion failed when converting the varchar value
- Unknown object type used in a CREATE, DROP, or ALTER statement
- Cannot insert explicit value for identity column in table
- The INSERT statement conflicted with the FOREIGN KEY constraint
- The DELETE statement conflicted with the REFERENCE constraint