INCREMENT
Option
The optional INCREMENT
clause allows you to specify another non-zero step value than 1.
Warning
|
The SQL standard specifies that if |
Renaming a Column: the TO Clause
Changing the Data Type of a Column: the TYPE Clause
Changing the Position of a Column: the POSITION Clause
The DROP DEFAULT and SET DEFAULT Clauses
The SET NOT NULL and DROP NOT NULL Clauses
The COMPUTED [BY] or GENERATED ALWAYS AS Clauses
INCREMENT
OptionThe optional INCREMENT
clause allows you to specify another non-zero step value than 1.
Warning
|
The SQL standard specifies that if |
Computed columns can be defined with the COMPUTED [BY]
or GENERATED ALWAYS AS
clause (the SQL standard alternative to COMPUTED [BY]
).Specifying the data type is optional;if not specified, the appropriate type will be derived from the expression.
If the data type is explicitly specified for a calculated field, the calculation result is converted to the specified type.This means, for instance, that the result of a numeric expression could be converted to a string.
In a query that selects a computed column, the expression is evaluated for each row of the selected data.
Tip
|
Instead of a computed column, in some cases it makes sense to use a regular column whose value is calculated in triggers for adding and updating data.It may reduce the performance of inserting/updating records, but it will increase the performance of data selection. |
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
.
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).