Skip to main content
Previous sectionNext section

Field constraint

Specifies rules about a field’s contents.

Description

A field constraint specifies rules governing the data values permitted for a field. A field may have the following constraints:

  • NOT NULL: You must specify a value for this field in every record (empty strings acceptable).

  • UNIQUE: If you specify a value for this field in a record, it must be a unique value (one empty string acceptable). You can, however, create multiple records with no value (NULL) for the field.

  • DEFAULT: You must either specify a value or InterSystems IRIS provides a default for this field in every record (empty strings acceptable). The default may be NULL, an empty string, or any other value appropriate for the data type.

  • UNIQUE NOT NULL: You must specify a unique value for this field in every record (one empty string acceptable). Can be used as a primary key.

  • DEFAULT NOT NULL: You must either specify a value or InterSystems IRIS provides a default value for this field in every record (empty strings acceptable).

  • UNIQUE DEFAULT: Not Recommended — You must either specify a unique value or InterSystems IRIS provides a default value for this field in every record (one empty string acceptable). The default may be NULL, an empty string, or any other value appropriate for the data type. Use only if the default is a unique generated value (for example, CURRENT_TIMESTAMP), or if the default is intended to be used only once.

  • UNIQUE DEFAULT NOT NULL: Not Recommended — You must either specify a unique value or InterSystems IRIS provides a default value for this field in every record (one empty string acceptable). The default may be an empty string or any other value appropriate for the data type; it cannot be NULL. Use only if the default is a unique generated value (for example, CURRENT_TIMESTAMP), or if the default is intended to be used only once. Can be used as a primary key.

  • IDENTITY: InterSystems IRIS provides a unique, system-generated, non-modifiable integer value for this field in every record. Other field constraint keywords are ignored. Can be used as a primary key.

Data values must be appropriate for the field’s data type. An empty string is not an acceptable value for a numeric field.

These field constraints are further described in the page for the CREATE TABLE command.