SQL Autonumbers

SQL AutoNumbering

In most databases, you can automatically assign a new number to a primary key column. This is then used as the surrogate key for the record.

• In SqlServer and Sybase, a column can be marked as IDENTITY
• In DB2, a column can be marked as GENERATED BY DEFAULT AS IDENTITY
• In MySQL, a column can be marked as AUTO_INCREMENT
• In PostgreSQL, a column can be marked as SERIAL or can use a SEQUENCE.
• In Oracle, the table has a INSERT trigger which reads a new value from a SEQUENCE.
• In Oracle 12c+. the table can be marked as GENERATED BY DEFAULT AS IDENTITY
• In SqlServer 2012+, the table has a DEFAULT which reads a new value from a SEQUENCE.

NB: in NHibernate it is not recommended to use database autonumbering. It is better to a NHibernate generated surrogate key- either a GUIComb, or a HiLo implementation.

DatabaseSchemaReader

The DatabaseSchemaReader recognises these patterns and the schema model includes the following:

• DatabaseColumn has a boolean property IsAutoNumber
• DatabaseColumn also has a property IdentityDefinition which either has NULL or a DatabaseColumnIdentity. This is only set if the column is an Identity type (not AutoNumbering with a sequence via the Default value or a Trigger).
• DatabaseTable has a boolean property HasAutoNumberColumn (In versions prior to v1.3, the equivalent properties were named IsIdentity and HasIdentityColumn)

For Oracle, there is a helper ProviderSchemaReaders.OracleSequenceTrigger, which tries to recognise the autonumber trigger.

var seqTrig = OracleSequenceTrigger.FindTrigger(table); //static factory
if (seqTrig == null) return; //could not find an autonumber trigger
Console.WriteLine(seqTrig.DatabaseTrigger.TriggerBody); //access the trigger
Console.WriteLine(seqTrig.DatabaseSequence.Name); //access the sequence

Identity AND Sequences

• In addition to Identity, SQLServer 2012 also introduced Sequences, which are assigned using column DEFAULT (not triggers).
• In addition to Sequences, Oracle 12c (2013) also introduced Identity columns (in the form GENERATED BY DEFAULT AS IDENTITY). Sequence values can also be assigned using DEFAULT, as well as triggers.

The IsAutoNumber story is therefore a little more complicated with these databases.

The DatabaseSchemaReader should recognize all these patterns and will assign IsAutoNumber = true. If you use the DSR's SqlGen (TableGenerator, Migrations), the original form should be preserved.

In both Oracle and SqlServer, if the DatabaseColumn has an IdentityDefinition, it is an identity-type autonumbering.

If IdentityDefinition is null, but IsAutoNumber is true, autonumbering is handled by the DatabaseColumn.DefaultValue or triggers (OracleSequenceTrigger.FindTrigger can help in the later case).