Defining An Array Column

Defining an Array Column

If the column is to be an array, the base type can be any SQL data type except BLOB and array.
The dimensions of the array are specified between square brackets.
For each array dimension, one or two integer numbers define the lower and upper boundaries of its index range:
- By default, arrays are 1-based.The lower boundary is implicit and only the upper boundary need be specified.A single number smaller than 1 defines the range num…1 and a number greater than 1 defines the range 1…num.
- Two numbers separated by a colon (‘:’) and optional whitespace, the second greater than the first, can be used to define the range explicitly.One or both boundaries can be less than zero, as long as the upper boundary is greater than the lower.
When the array has multiple dimensions, the range definitions for each dimension must be separated by commas and optional whitespace.
Subscripts are validated only if an array actually exists.It means that no error messages regarding invalid subscripts will be returned if selecting a specific element returns nothing or if an array field is NULL.

Constraints

Five types of constraints can be specified.They are:

Primary key (PRIMARY KEY)
Unique key (UNIQUE)
Foreign key (REFERENCES)
CHECK constraint (CHECK)
NOT NULL constraint (NOT NULL)

Constraints can be specified at column level (“column constraints”) or at table level (“table constraints”).Table-level constraints are required when keys (unique constraint, primary key, foreign key) consist of multiple columns and when a CHECK constraint involves other columns in the row besides the column being defined.The NOT NULL constraint can only be specified as a column constraint.Syntax for some types of constraint may differ slightly according to whether the constraint is defined at the column or table level.

A column-level constraint is specified during a column definition, after all column attributes except COLLATION are specified, and can involve only the column specified in that definition
A table-level constraints can only be specified after the definitions of the columns used in the constraint.
Table-level constraints are a more flexible way to set constraints, since they can cater for constraints involving multiple columns
You can mix column-level and table-level constraints in the same CREATE TABLE statement

The system automatically creates the corresponding index for a primary key (PRIMARY KEY), a unique key (UNIQUE), and a foreign key (REFERENCES for a column-level constraint, FOREIGN KEY REFERENCES for table-level).