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.

Answer 1

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.

Answer 2

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.