How to Resolve the 'Cannot Insert Explicit Value for Identity Column' Error?

Resolving "Unable to insert explicit value for identity column" error

When you insert records into a table that contains an identity column (such as an auto-incrementing primary key), be sure to let the database automatically generate a value for this column. If you try to manually specify a value for an identity column when the IDENTITY_INSERT property is set to OFF, an error occurs.

Solution

To resolve this issue, you need to set IDENTITY_INSERT to ON before inserting the record:

<code class="language-sql">SET IDENTITY_INSERT 表名 ON</code>

This will allow you to insert your own specified identity value. Remember to set IDENTITY_INSERT back to OFF after inserting to prevent future problems:

<code class="language-sql">SET IDENTITY_INSERT 表名 OFF</code>

Modified insert statement

Now that IDENTITY_INSERT is enabled, you can insert records as expected:

<code class="language-sql">SET IDENTITY_INSERT 表名 ON

INSERT INTO 表名(OperationID, OpDescription, FilterID)
VALUES (20, 'Hierachy Update', 1)

SET IDENTITY_INSERT 表名 OFF</code>

With these steps, you can insert records into a table that contains an identity column without triggering the "Cannot insert explicit value for identity column" error.

The above is the detailed content of How to Resolve the 'Cannot Insert Explicit Value for Identity Column' Error?. For more information, please follow other related articles on the PHP Chinese website!