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

Question

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

User · Answer

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

User · Answer

You can insert a value manually in the ID column  here I call it  PK     insert into table1  PK  var1  var2  values   select max PK  1 from table1   123  456

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

In my case    I was trying to update my model by making a foreign key required  but the database had  null  data in it already in some columns from previously entered data  So every time i run update-database   i got the error    I SOLVED it by manually deleting from the database all rows that had null in the column i was making required

User · Answer

curt is correct  but I ve noticed that sometimes even this fails with NULL disallowed errors  and it seems to be intermittent  I avoided the error at all times  by also setting the Indenty Seed to 1 and IDENTITY 1  1  NOT FOR REPLICATION

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

if use entityframework  open migration  set value nullable  true  and update database

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to sql-server