"Cannot insert explicit value for identity column in table when IDENTITY_INSERT is set to OFF" with composite key

Question

We have recently added a new "level" to our database - added a key "Company_ID" to be above/before the existing ID Identity field in the tables throughout the database.

For example, if a Table had ID then fields, it now has Company_ID, then ID, then the fields. The idea is that this allows ID to auto-increment for each different Company_ID value that is provided to the functionality (Company_ID 1 can have ID 1, 2, 3 etc ; Company_ID 2 can have ID 1, 2, 3, etc).

The auto-increment field remains as ID. An example table is :

    [dbo].[Project](
      [Company_ID] [int] NOT NULL,
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [DescShort] [varchar](100) NULL,
      [TypeLookUp_ID] [int] NULL,
      [StatusLookUp_ID] [int] NULL,
      [IsActive] [bit] NOT NULL,
    CONSTRAINT [PK_Project] PRIMARY KEY CLUSTERED 
    (
      [Company_ID] ASC,
      [ID] ASC
    )

Before the Company_ID was introduced, to perform a CREATE, we simply populated the DescShort, TypeLookUp_ID, StatusLookUp_ID and IsActive fields, and left ID to be whatever it was by default, possibly 0.

The record was saved successfully, and ID was auto-populated by the database, and then used to perform a SHOW via a View, and so on.

Now, however, we want to set Company_ID to a specified value, leave ID, and populate the fields as before.

    _db.Project.Add(newProject);
    _db.SaveChanges();

Yes, we want to specify the Company_ID value. We want the ID to be auto-populated, as per before. We are getting the error message :

Cannot insert explicit value for identity column in table "Project" when IDENTITY_INSERT is set to OFF

Is this caused by specifying the Company_ID, or by the ID field? Do you know how we can rectify this issue?

Answer 1

The problem is on ID. If you set a field as IDENTITY you can't normally assign it a value - the IDENTITY property marks it as allowing the database to automatically assign an incrementing value to the column.

To solve this problem, either remove the automatic IDENTITY property from ID (if you want to auto-increment it, you can always do this in your handling code - get the highest value in the field, add one to it and then assign that value) or go to the DB and set IDENTITY _INSERT on the table, which temporarily allows you to assign values to IDENTITY fields.

SET IDENTITY_INSERT [yourTableName] ON

--go back and run your C# code>

SET IDENTITY_INSERT [yourTableName] OFF