SQL Server, Can't Insert Null into Primary Key Field

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

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.

Why is SQL server throwing this error: Cannot insert the value NULL into column 'id'?

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 and Indentity Increment=1

Why I can add null value to primary key in SQLite?

Thanks to @astentx advice, I check documentation and it said that:

According to the SQL standard, PRIMARY KEY should always imply NOT NULL. Unfortunately, due to a bug in some early versions, this is not the case in SQLite. Unless the column is an INTEGER PRIMARY KEY or the table is a WITHOUT ROWID table or the column is declared NOT NULL, SQLite allows NULL values in a PRIMARY KEY column. SQLite could be fixed to conform to the standard, but doing so might break legacy applications. Hence, it has been decided to merely document the fact that SQLite allowing NULLs in most PRIMARY KEY columns.

SQL/Oracle - Cannot insert null into primary key

Oh I found it, the NOT NULL constraint still works fine, but my Column is actually called 'Schema' (other language) and apparently this has a definition in Oracle. So it's never altered as a primary key. Changing the table his name should work.

Thanks for the fast responds and help!

DONT USE RESERVED TERMS FOR YOUR TABLE NAMES!

Why primary key cannot contain null values?

A PRIMARY KEY column is equivalent to UNIQUE and NOT NULL and is indexed column by default.

It should be UNIQUE because a primary key identifies rows in a table so 2 different row should not have the same key.

In addition a primary key may be used a FOREIGN KEY in other tables and that's why it cannot be NULL so that the other table can fin the rows in the referenced table.

For example:

CREATE person{   
   id INT PRIMARY KEY,  -- equals UNIQUE NOT NULL   
   name VARCHAR(20)   
};   

CREATE family{   
   id INT PRIMARY KEY,  -- equals UNIQUE NOT NULL   
   menber_id INT FOREIGN KEY REFERENCE person(id)   
};   

Can not Insert the NULL value into Auto Increment PrimaryKey Column

NOT NULL means you can't insert NULL into those columns you need to explicitly insert it.

Setting IDENTITY(1,1) will insert item_id automatically by seed of 1.

Change your definition like this

CREATE TABLE [dbo].[Items] (
[item_id]       INT          NOT NULL IDENTITY(1, 1),
[item_name]     VARCHAR (50) NULL,
[item_model]    VARCHAR (50) NULL,
[item_price]    VARCHAR (50) NULL,
[item_quantity] VARCHAR (50) NULL,
[entry_date]    VARCHAR (50) NULL,
[user_id]       VARCHAR (50) NULL,
PRIMARY KEY CLUSTERED ([item_id] ASC)
);

OR

explicitly insert it into database

string query = @"Insert into Items(item_id,item_name,item_model,item_price,item_quantity,entry_date,user_id)" +
                "VALUES(@item_id,@name, @model, @price, @quantity, @date,@user)";

Stop using .AddWithValues
Source

Can't insert null value into a foreign key field in MS Access

Okay, I've worked out how to get around this problem - disable Referential Integrity on the relationship between LanguageCodeValue and Sense_LanguageSource. Probably not the best solution, but it does allow me to input 0 on the Foreign Key field.

Edit: I've found a better way. Create a "Null" record on the LanguageCodeValue table, with blank fields, and refer to that whenever I want to indicate that there is no LanguageCode attached to the Sense_LanguageSource table.

---

Related Topics