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.
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.
IDENTITY(1,1) while creating the table
CREATE TABLE SAMPLE( [Id] [int] IDENTITY(1,1) NOT NULL, [Status] [smallint] NOT NULL, CONSTRAINT [PK_SAMPLE] PRIMARY KEY CLUSTERED ( [Id] ASC ) )
if use entityframework. open migration, set value nullable: true, and update database
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.
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())
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)
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.
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)
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.
@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.
I had a similar problem and upon looking into it, it was simply a field in the actual table missing
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
This could be the fix if you see a temp table associated with the error. I was using SQL Server Management Studio.
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.