AX1826

Using column constraints

You can define a column constraint to restrict the column contents to a specific set of valid values. This feature is similar to using a lookup column, except that the valid values are not defined in a separate lookup table. Instead, you define an expression that is used to restrict the column values.

For example, imagine that you have a Status column where you want to restrict the column values to either Active or Inactive. When using a lookup column, you would need to define a separate lookup table with key column values of Active and Inactive. Creating a separate lookup table may be unnecessary overhead for this particular use case. Instead, you can define a simple column constraint of In ('Active','Inactive') to achieve the same effect.

If a column has a defined constraint, then when data is saved to the column via Axiom features such as save-to-database or imports, the data is checked against this constraint. If the data doesn't meet the defined constraint, an error occurs.

Column constraint syntax

A column constraint is an expression using one of the following operators: =, >, >=, <, <=, <>, !=. The operators IN and BETWEEN can also be used. For example, the following column constraint expressions are valid:

  • =1
  • < 10000
  • IN ('a', 'b', 'c')
  • BETWEEN 1 and 5
Example Expression Column Behavior

=1

Only the value 1 can be saved to the column.

<10000

 Only values less than 10000 can be saved to the column.

IN ('Active','Inactive')

The values Active or Inactive can be saved to the column.
BETWEEN 1 and 5

Any value between 1 and 5 can be saved to the column.

NOTES:  

  • Column constraints must be valid within the context of the column data type. For example, if the column is an integer column, the constraint must allow integer values.
  • String columns cannot use greater than or less than operators.
  • String values must be enclosed in single quote marks. Do not use quote marks with numbers.

Defining a column constraint

Column constraints are defined in the column properties. You can define a column constraint when creating a new table, or when editing properties for an existing table. The following steps assume that you are modifying an existing table.

If the table does not have any data, then you can define any valid constraint on the column. If the table already has data, then any existing data in the column must match the constraint, or else the constraint cannot be saved on the column.

To define a column constraint:

  1. On the Axiom tab, in the Administration group, click Tables > Table Administration > Manage Tables.

    The Axiom Explorer dialog opens, with the focus on the Table Library.

    NOTE: If you are using an Axiom packaged product, you can access this feature from the Admin tab. Click System Browser to open Axiom Explorer, then navigate to the Table Library.

  2. In the Table Library folder, navigate to the table that you want to edit.

    You can view the list of tables by folder, by table, or by table type. By default, tables are displayed by folder. If you want to change the display, right-click the Table Library folder and then use the View menu to select the desired view.

  3. Right-click the table and then click Edit table structure.

  4. In the Edit Table dialog, click the Columns tab.

  5. On the left side of the Columns tab, select the column that you want to edit, or create a new column. Then on the right side of the tab, scroll down the column properties until you locate the CheckConstraintExpression property.

  6. Type the desired constraint into the CheckConstraintExpression property.

    Example column constraint

  7. Review the Default Value column property and make sure that the default value is valid within the column constraint. If it is not valid, edit the default value so that it meets the constraint.

  8. Click Apply or OK to save your changes.

You can also define a column constraint when using Save Type 4 to Axiom.Columns, by modifying the CheckConstraintExpression column.