I'm Getting a Cannot Insert the Value NULL Into Column 'ID' Message in SQL

Author : Tim Corey
Date : August 21, 2019
Categories :
Tags : sql

This is a fairly common issue. You go to insert data into SQL, either directly or through C# or another programming language only to get the error “Cannot insert the value NULL into column ‘id’”. It probably continues by saying “column does not allow nulls. INSERT fails.” Something like this:

Error: Cannot insert the value null into column ID

You look at your SQL statement first, to see if that is the issue. Here is what mine looks like:

SQL insert statement that inserts into the FirstName and LastName columns

Pretty simple, right? Now if you look at the table, you see the following:

Table definition with ID, FirstName, and LastName columns

OK, so there is the Id column that it says is the problem but it is the primary key and an int. Doesn’t that mean it will automatically count up as we insert records? Hint: nope.

Now that we know the issue, let’s discuss the options. Your first instinct might be to try to insert an Id in the Id column manually. Trust me, that’s not a great idea. Couple reasons for that. First, the primary key column must be unique. That means you will need to insert a new value every time. Now you could just get the last value and add one, but that means looking up the value every time. It also means if you delete a record (don’t do that) and it was the last one in the table, you are going to use that Id over again (not a good thing).

The next reason why it isn’t a good idea is because the table is stored based upon the order of the primary key. If you add a new value before the last value in the table (say you use a unique number like phone number or social security number to identify a person), SQL will need to reorder your table in memory and then re-store it to disk. That can get really expensive. Besides, those natural keys aren’t a great idea for other reasons, including the fact that they aren’t as unique as you think and they are potentially sensitive and thus not allowed to be displayed.

So, how do we set up our Id field to handle the key value automatically? Well, if you go into the table designer, in the properties for the Id column you will see the Identity Specification property set like so:

Select the Identity Specification property

Expand that out and set (Is Identity) to Yes. This will set the defaults for Identity Increment and Identity Seed to 1, which you can leave as they are. That looks like this:

Now save the table and you are all set. Now you can run that same query and it will work correctly.