SQL Reference Guide

Previous Topic

Next Topic

Assign Values to Auto-Increment Fields in INSERT

SQL - auto_increment fields enhanced

IDENTITY column support is now "smart" and automatically inserts values when explicitly specified and auto-generates values when they are not (the default existing behavior).

The syntax/definition of auto_increment fields in this release is identical to the current IDENTITY syntax except INSERT statements are smarter. When an INSERT statement specifies a value for an IDENTITY column, it inserts the specified value; when the value is omitted, it is generated automatically.

Prior to this modification, it was necessary to explicitly disable IDENTITY insertion with
SET identity_insert <table> on | off.

Only one table at a time can be set to identity_insert on.

No table definition changes are required to enable this new support. It enables compatibility with many external SQL frameworks such as SQLAlchemy.