SQL insert error: Cannot insert the value NULL into column 'Id'

Question

I would like to insert values in my database with this query:

INSERT INTO Users (pinConfirmed,Factor,DateUtc,LockoutEnabled,AccessCount,EmailConfirmed)
 values ('false', 'false','1/1/2018' ,'false','0','false')

But I get this error: Cannot insert the value NULL into column 'Id', table 'Users'; column does not allow nulls. INSERT fails.

But, there are already data in the DB among which Id which is not null. I checked with these two requests and I have no null for Id.

select * from Users where Id IS NULL;
select * from Users where Id = 'NULL';

I don't understand where the problem is. Need help please.

What is the table definition? If Id isn't set to produce a value (such as an incrementing integer identity) then it would need a value provided. You're not providing one, hence the error. — David
– David, Commented Dec 6, 2018 at 17:33
In fact I copy values from one table to another with an insert into select. There are already values in the field Id. Here is the definition: CREATE TABLE [dbo].[Users] ([Id] NVARCHAR (128) NOT NULL CONSTRAINT [PK_dbo.Users] PRIMARY KEY CLUSTERED ([Id] ASC) ); . I can not do a self increment for values values — kst92
– kst92, Commented Dec 6, 2018 at 17:40

Amir Touitou · Accepted Answer · 2019-11-19 08:35:26Z

5

if you go into the table designer, in the properties for the Id column you will see the Identity Specification property set like so:

Expand that out and set (Is Identity) to Yes. This will set the defaults for Identity Increment and Identity Seed to 1

Now save the table and you are all set.

answered Nov 19, 2019 at 8:35

Amir Touitou

3,5191 gold badge39 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

David · Accepted Answer · 2018-12-06 17:45:09Z

3

According to your comment on the question above, the Id column is defined as:

[Id] NVARCHAR (128) NOT NULL

And that's it. No default value, no identity/autoincrement, etc. Simply a character value which can not be NULL. But when you perform an insert you don't provide a value:

INSERT INTO Users (pinConfirmed,Factor,DateUtc,LockoutEnabled,AccessCount,EmailConfirmed)
values ('false', 'false','1/1/2018' ,'false','0','false')

That's why you get an error. NOT NULL means a value is required. You're not providing one.

You have a few options:

Provide a value for the column when you INSERT
Make the column generate its own value (this will depend on your RDBMS, for example in SQL Server you would make the column an IDENTITY or in MySQL you'd make it AUTOINCREMENT, though both of those are for integer columns, whereas yours is character data, so you'll need to change the data type as well)
Allow NULL, which of course will only work once because this is a primary key column and values must be unique.

answered Dec 6, 2018 at 17:45

David

221k42 gold badges247 silver badges338 bronze badges

1 Comment

kst92 Over a year ago

ok David, I did an insertion in two times after your explanation I tried to do it in a step and it works. thanks for your help

DFed · Accepted Answer · 2018-12-06 17:41:20Z

0

ID is the PRIMARY key , it is a unique identifier of the row. It Cannot be left null. That is why you have an error.

answered Dec 6, 2018 at 17:41

DFed

11 bronze badge

2 Comments

kst92 Over a year ago

There are values in the column Id.

David Over a year ago

@kst92: Not in the data you're trying to insert. Closely examine your INSERT statement. Where do you see an Id value?

Collectives™ on Stack Overflow

SQL insert error: Cannot insert the value NULL into column 'Id'

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related