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

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.

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.

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

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.

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.

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

WARNING! Make sure the target table is locked when using this method (As per @OnurOmer's comment)

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)

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 id0 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.

RULE: You cannot IGNORE those colums that do not allow null values, when inserting new data.

Your Case

• You're trying to insert values, while ignoring the id column, which does not allow nulls. Obviously this won't work.
• Gladly for you the "Identity Specification" seems to automatically fill the not nullable id values for you (see selected answer), when you later execute the insert query.

My Case

• The problem (while using SSMS): I was having this error when trying to add a new non-nullable column to an already existing table with data. The error I'd got was:

Cannot insert the value NULL into column 'id_foreign', table 'MyDataBase.dbo.Tmp_ThisTable'; column does not allow nulls. INSERT fails. The statement has been terminated.

• The solution:
  1. I created the column I needed id_foreign, allowing nulls.
  2. I edited/inserted all the required values for id_foreign.
  3. Once the values where in place, I went back and unchecked the "Allow Nulls" checkbox. Now the error was gone.