This is the 15th day of my participation in the September Challenge.

Chapter 16 SQL > CREATE TABLE

Field data constraint

Data constraints control the allowed values for fields, the default values for fields, and the collation types used for data values. All of these data constraints are optional. Multiple data constraints can be specified in any order, separated by Spaces.

NULL and NOT NULL

NOT NULL Data constraint keyword specifies that the field does NOT accept NULL values. In other words, each record must specify a value for that field. NULL and empty string (“) IRIS are different values. You can enter an empty string in a field that accepts a string, even if the field defines a NOT NULL constraint. You cannot enter an empty string in a numeric field.

The NULL data constraint keyword explicitly specifies that this field can accept NULL values; This is the default definition of the field.

UNIQUE

Unique data constraint specifies that this field accepts only unique values. Therefore, no two records can contain the same value for that field. The SQL empty string (“) is treated as a data value, so no two records can contain the empty string value of this field when the UNIQUE data constraint is applied. NULL is not considered a data value, so the unique data constraint does not apply to more than one NULL. To restrict the use of NULL for fields, use the NOT NULL keyword constraint.

• Unique data constraints require that all values of a specified field be unique.
• UNIQUE fields constraint(using theCONSTRAINTKeyword) requires all values of a specified set of fields to produce unique values when concatenated. There is no need to restrict individual fields to unique values.

Tables defined as shard tables have additional restrictions on the use of UNIQUE data constraints. Unique constraints on fields or groups of fields that do not contain shard keys add significant performance costs for inserts and updates. Therefore, it is recommended to avoid this type of unique constraint when insert and update performance is an important consideration.

DEFAULT

The default data constraint specifies the default data value that IRIS automatically provides for this field during INSERT operations, if INSERT does not provide a value for this field. If the insert operation provides NULL for the field data value, NULL is used instead of the default data value. Therefore, it is common to specify both DEFAULT and NOT NULL data constraints for the same field.

Default values can be provided as literal values or keyword options. Strings supplied as literal defaults must be enclosed in single quotes. Numeric defaults do not require single quotes. Such as:

CREATE TABLE membertest
(MemberId INT NOT NULL,
Membership_status CHAR(13) DEFAULT 'M',
Membership_term INT DEFAULT 2)
Copy the code

The default values are not validated when the table is created. When defined, default values can ignore data type, data length, and data constraint constraints. However, when using INSERTS to provide data to a table, the default values are constrained; It is not limited by data type and data length, but by data constraints. For example, a field that defines Ordernum int Unique Default ‘No Number’ can take the Default value once, ignoring the int data type restriction, but cannot take the Default value a second time, as this would violate the Unique field data constraint.

If no default value is specified, the implied default value is NULL. If a field has a non-empty data constraint, you must specify a value for that field either explicitly or by default. Do not use SQL zero-length strings (empty strings) as non-empty defaults.

DEFAULT Keywords

The default data constraint can accept a keyword option to define its value. The following options are supported: NULL, USER, CURRENT_USER, SESSION_USER, SYSTEM_USER, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, SYSDATE and OBJECTSCRIPT.

USER, CURRENT_USER, and SESSION_USER Default keyword sets the field value to the ObjectScript $USERNAME special variable.

CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, GETDATE, GETUTCDATE, and SYSDATE SQL functions can also be used as defaults. They are described in their respective reference pages. When used as the default, you can specify CURRENT_TIME or TIMESTAMP functions with no precision value. If no precision is specified, the SQL configuration is used to set the precision of “default time precision of GETDATE(), CURRENT_TIME, and CURRENT_TIMESTAMP”, which defaults to 0. The DEFAULT function uses a valid time precision setting when the CREATE TABLE statement is prepared/compiled, but not when the statement is executed.

You can specify CURRENT_TIMESTAMP as data type % library.positime or % library.timestamp; The default value for the field. The current date and time are stored in the format specified by the field data type. CURRENT_TIMESTAMP, GETDATE, GETUTCDATE, and SYSDATE can be specified as the default values for the % Library.timestamp field (data type TimeStamp or DATETIME). IRIS converts the date value into a format suitable for the data type.

CREATE TABLE mytest
(
    TestId INT NOT NULL,
    CREATE_DATE DATE DEFAULT CURRENT_TIMESTAMP(2),
    WORK_START DATE DEFAULT SYSDATE
)
Copy the code

You can use the TO_DATE function as the default data constraint for data type DATE. You can use the TO_TIMESTAMP function as the default data constraint for data type TIMESTAMP.

OBJECTSCRIPT literal keyword phrases enable you to generate default values by supplying a quoted string containing OBJECTSCRIPT code, as shown in the following example:

CREATE TABLE mytest
(
    TestId INT NOT NULL,
    CREATE_DATE DATE DEFAULT OBJECTSCRIPT '+$HOROLOG' NOT NULL,
    LOGNUM NUMBER(12.0) DEFAULT OBJECTSCRIPT '$INCREMENT(^LogNumber)'
)
Copy the code

ON UPDATE

The ON UPDATE clause causes the computed value of the field to be %%UPDATE. This is a shortcut syntax for defining a field that is always evaluated whenever a row in the table is updated. The most common use of this feature is to define a column in a table that contains the timestamp value of the last update to the row.

The options available for updating specifications are:

CURRENT_DATE | CURRENT_TIME[(precision)] | CURRENT_TIMESTAMP[(precision)] | GETDATE([prec]) | GETUTCDATE([prec]) | SYSDATE | 
USER | CURRENT_USER | SESSION_USER | SYSTEM_USER |
 NULL | <literal> | -<number> 
Copy the code

The following example sets the row field to the current timestamp value when inserting a row and each time the row is updated:

CREATE TABLE mytest
(
    Name VARCHAR(48),
    RowTS TIMESTAMP DEFAULT Current_Timestamp(6) ON UPDATE Current_Timestamp(6))Copy the code

In this case, if no explicit value is specified for the RowTS field, the DEFAULT keyword sets RowTS to the current timestamp at the time of insertion. If UPDATE specifies an explicit value for the RowTS field, the ON UPDATE keyword validates but ignores the specified value and updates the RowTS with the current timestamp. If the specified value is not validated, a SQLCODE-105 error is generated.

The following example sets the HasBeenUpdateed field to a Boolean value:

CREATE TABLE mytest
(Name VARCHAR(48),
 HasBeenUpdated TINYINT DEFAULT 0 ON UPDATE 1 )
Copy the code

The following example sets the whLastUpdateed field to the current user name:

CREATE TABLE mytest
(Name VARCHAR(48),
 WhoLastUpdated VARCHAR(48) DEFAULT CURRENT_USER ON UPDATE CURRENT_USER )
Copy the code

If the field also has a COMPUTECODE data constraint, the ON UPDATE clause cannot be specified. Attempting to do so will result in a SQLCODE-1 error at compile/prepare time.

Collation Parameters

The optional collation parameter specifies the type of string collation to use when sorting the value of a field. SQL supports ten types of collation. If no collation is specified, the default is the %SQLUPPER collation, which is case insensitive.

For programming purposes, it is recommended that you specify the optional keyword COLLATE before the COLLATION parameter, but this keyword is not required. The percent sign (%) prefix for various sort parameter keywords is optional.

%Exact collation follows ANSI(or Unicode) character collation sequences. This provides case-sensitive string sorting and recognizes leading and trailing Spaces as well as tabs.

%SQLUPPER categorize converts all letters to uppercase for categorization.

The %SPACE and %SQLUPPER collations append a SPACE to the data. This enforces string sorting for null and numeric values.

The %SQLSTRING, %SQLUPPER, and %TRUNCATE collation rules provide an optional maxlen parameter, which must be enclosed in parentheses. Maxlen is a truncated integer that specifies the maximum number of characters to consider when performing a sort. This parameter is useful when creating indexes for fields that contain large data values.

The %PLUS and %MINUS collation rules treat NULL as a 0(0) value.

Note: Shard key fields can only accept %EXACT, %SQLSTRING, or %SQLUPPER sorting, no truncation.

ObjectScript provides the Colation() method of the % system.util class for data collation conversions.

Note: To change the default namespace collation from %SQLUPPER(case insensitive) to another collation type, such as %SQLSTRING(case sensitive), use the following command:

  WRITE $$SetEnvironment^%apiOBJ("collation"."%Library.String"."SQLSTRING")
Copy the code

After issuing this command, you must clear the index, recompile all the classes, and then rebuild the index. Do not rebuild the index while other users are accessing the table’s data. Doing so may result in inaccurate query results.

%DESCRIPTION

You can provide description text for a field. This option follows the same convention as providing descriptive text for the table. It is described above using other table elements.

Computed field

Instead of user-supplied fields, you can define one or more fields whose values are computed. The event that calculates the field value depends on the following keyword options:

• COMPUTECODE:Values are computed and stored at insert time, and unchanged at update.
• COMPUTECODE WITH COMPUTEONCHANGE: VALUEinINSERTWhen calculated and stored inUPDATEWhen recalculated and stored.
• COMPUTECODE WITH DEFAULT and COMPUTEONCHANGE:Default values are stored at insert time, and values are calculated and stored at update time.
• COMPUTECODE WITH COMPUTTECODE WITH COMPUTED or TRANSPENT:Values are not stored, but are generated each time a field is queried.

COMPUTECODE

COMPUTECODE Data constraint specifies ObjectScript code to compute the default data value for this field. The ObjectScript code is specified in braces. In ObjectScript code, you can specify SQL field names using curly brace delimiters. ObjectScript code can consist of multiple lines of code. It can contain embedded SQL. Allows Spaces and carriage returns before or after the brace delimiter in ObjectScript code.

COMPUTECODE Specifies the calculation of the SqlComputeCode field name and its value. When you specify the computed field name in a COMPUTECODE or SqlComputeCode class attribute, you must specify the SQL field name, not the corresponding generated table attribute name.

The default data values provided by the computer code must be in logical (internal storage) mode. Embedded SQL in computer code is automatically compiled and run in logical mode.

The following example defines the Birthday COMPUTECODE field. It uses ObjectScript code to calculate its default value from the Dobb field value:

CREATE TABLE MyStudents 
(
   Name VARCHAR(16) NOT NULL,
   DOB DATE,
   Birthday VARCHAR(12) COMPUTECODE {SET {Birthday}=$PIECE($ZDATE({DOB},9),",")},
   Grade INT
)
Copy the code

COMPUTECODE can contain pseudo-field reference variables {%%CLASSNAME}, {%%CLASSNAMEQ}, {%%OPERATION}, {%%TABLENAME}, and {%%ID}. These pseudo-fields are converted to specific values when the class is compiled. All of these pseudo-field keywords are case insensitive.

The COMPUTECODE value is the default; The value is returned only if no value is supplied to the field. COMPUTECODE values are not restricted by data type. COMPUTECODE values are limited by unique data constraints and other data constraint constraints. If both DEFAULT and COMPUTECODE are specified, the DEFAULT value is always used.

COMPUTECODE can optionally accept the COMPUTEONCHANGE, COMPUTEONCHANGE, or TEMPUTEONCHANGE keyword. Supports the following keyword combination behavior:

If there is an error in ObjectScript COMPUTECODE code, SQL will not detect it until the first time the code is executed. Therefore, if the value is evaluated first during the INSERT operation, the INSERT operation fails with SQLCODE-415 error. If the value is calculated first, the UPDATE operation fails and SQLCODE-415 is displayed. If the value is calculated first when querying, the SELECT operation fails with a SQLCODE-350 error.

COMPUTECODE store values can be indexed. It is the responsibility of the application developer to ensure validation and standardization of computed field stored values (numbers in normalized form) against the data types of computed field stored values, especially if indexes are defined (or intended to be defined) for computed fields.

COMPUTEONCHANGE

The COMPUTECODE itself causes field values to be computed during INSERT and stored in the database; This value remains the same in subsequent operations. By default, subsequent updates or trigger code actions do not change the calculated value. Specifying the COMPUTEONCHANGE keyword causes subsequent UPDATE or trigger code operations to recalculate and replace this stored value.

If you specify a field or comma-separated list of fields using the COMPUTEONCHANGE clause, any change to the value of one of the fields causes SQL to recalculate the COMPUTECODE field value.

If the fields specified in COMPUTEONCHANGE are not part of the table specification, SQLCODE-31 is generated.

In the example below, the birthday is calculated by insertion based on the DOB(date of birth) value. Recalculate birthdays when updating DOB:

CREATE TABLE SQLUser.MyStudents (
   Name VARCHAR(16) NOT NULL,
   DOB DATE,
   Birthday VARCHAR(40) COMPUTECODE {
        SET {Birthday}=$PIECE($ZDATE({DOB},9),",")
        _" changed: "_$ZTIMESTAMP }
        COMPUTEONCHANGE (DOB)
     )
Copy the code

COMPUTEONCHANGE Defines the SqlComputeOnChange keyword with the %%UPDATE value of the class attribute corresponding to the field definition. The value of this attribute is initially evaluated as part of the INSERT operation and recalculated during the UPDATE operation.

CALCULATED and TRANSIENT

Specifies the COMPUTECODE or TEMPUTE keyword specifies that the COMPUTECODE field value is not saved in the database; It is evaluated as part of each query operation that accesses it. This reduces the size of the data store, but may degrade query performance. Because these keywords cause IRIS to not store the COMPUTECODE field values, these keywords and the COMPUTEONCHANGE keyword are mutually exclusive. Here is an example of a computed field:

CREATE TABLE MyStudents (
   Name VARCHAR(16) NOT NULL,
   DOB DATE,
   Days2Birthday INT COMPUTECODE{SET {Days2Birthday}=$ZD({DOB},14)-$ZD($H,14)} CALCULATED
   )
Copy the code

Computed defines Computed Boolean keys for class attributes corresponding to field definitions. Transient defines the transient Boolean key for the class attribute corresponding to the field definition.

Computations and transients provide nearly the same behavior, but with the following differences. TRANSIENT means that IRIS does not store this property. Computation means that IRIS does not allocate any instance memory for attributes. Therefore, TRANSIENT is implicitly set when calculate is specified.

Transient properties cannot be indexed. Computed attributes cannot be indexed unless they are also SQLComputed.