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

82

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.

This question is tagged with sql sql-server

~ Asked on 2012-04-04 14:33:46

The Best Answer is


148

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

~ Answered on 2012-04-04 14:36:40


7

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
)
)

~ Answered on 2018-01-09 19:41:03


Most Viewed Questions: