columnDefinition simpleColumnName [ dataType ] [ columnLevelConstraint ]* [ [ WITH ] DEFAULT defaultConstantExpression | generatedColumnSpec | generationClause ] [ columnLevelConstraint ]*

The syntax of dataType is described in . The dataType can be omitted only if you specify a generationClause. If you omit the dataType, the type of the generated column is the type of the generationClause. If you specify both a dataType and a generationClause, the type of the generationClause must be assignable to dataType.

The syntaxes of columnLevelConstraint and tableLevelConstraint are described in .

Column default

For the definition of a default value, a defaultConstantExpression is an expression that does not refer to any table. It can include constants, date-time special registers, current schemas, users, roles, and null:

defaultConstantExpression: NULL | CURRENT { SCHEMA | SQLID } | USER | CURRENT_USER | SESSION_USER | CURRENT_ROLE | DATE | TIME | TIMESTAMP | CURRENT DATE | CURRENT_DATE | CURRENT TIME | CURRENT_TIME | CURRENT TIMESTAMP | CURRENT_TIMESTAMP | literal

For details about Derby literal values, see .

The values in a defaultConstantExpression must be compatible in type with the column, but a defaultConstantExpression has the following additional type restrictions:

  • If you specify USER, CURRENT_USER, SESSION_USER, or CURRENT_ROLE, the column must be a character column whose length is at least 8.
  • If you specify CURRENT SCHEMA or CURRENT SQLID, the column must be a character column whose length is at least 128.
  • If the column is an integer type, the default value must be an integer literal.
  • If the column is a decimal type, the scale and precision of the default value must be within those of the column.