A table check constraint specifies a search condition that is enforced for each row of the table on which the table check constraint is defined. Once table check constraints are defined to the database manager, an insert or update to the data within the tables is checked against the defined constraint.
Completion of the requested action depends on the result of the constraint checking.
Procedure:
You create a table check constraint on a table by associating a check-constraint definition with the table when the table is created or altered. This constraint is automatically activated when an INSERT or UPDATE statement modifies the data in the table. A table check constraint has no effect on a DELETE or SELECT statement. A check constraint can be associated with a typed table.
A constraint name cannot be the same as any other constraint specified within the same CREATE TABLE statement. If you do not specify a constraint name, the system generates an 18-character unique identifier for the constraint.
A table check constraint is used to enforce data integrity rules not covered by key uniqueness or a referential integrity constraint. In some cases, a table check constraint can be used to implement domain checking. The following constraint issued on the CREATE TABLE statement ensures that the start date for every activity is not after the end date for the same activity:
CREATE TABLE EMP_ACT
(EMPNO CHAR(6) NOT NULL, PROJNO CHAR(6) NOT NULL, ACTNO SMALLINT NOT NULL, EMPTIME DECIMAL(5,2),
EMSTDATE DATE, EMENDATE DATE,
CONSTRAINT ACTDATES CHECK(EMSTDATE <= EMENDATE) ) IN RESOURCE
Although the previous example uses the CREATE TABLE statement to add a table check constraint, the ALTER TABLE statement can also be used.
Related concepts:
v “Constraints” in the SQL Reference, Volume 1 Related tasks:
v “Adding a table check constraint” on page 191 Related reference:
v “CREATE TABLE statement” in the SQL Reference, Volume 2 v “ALTER SERVER statement” in the SQL Reference, Volume 2
Defining an informational constraint
An informational constraint is a rule that can be used by the SQL compiler but is not enforced by the database manager. The SQL compiler includes a rewrite query stage which transforms SQL statements into forms that can be
optimized and improve the access path to the required data. The purpose of the constraint is not to have additional verification of data by the database manager, rather it is to improve query performance.
Procedure:
You define informational constraints using the CREATE TABLE or ALTER TABLE statements. Within those statements you add referential integrity or check constraints. You then associate constraint attributes to them specifying whether you want the database manager to enforce the constraint or not; and, whether you want the constraint to be used for query optimization or not.
Related concepts:
v “Constraints” in the SQL Reference, Volume 1
v “The SQL compiler process” in the Administration Guide: Performance v “Query rewriting methods and examples” in the Administration Guide:
Performance
Related reference:
v “ALTER TABLE statement” in the SQL Reference, Volume 2 v “CREATE TABLE statement” in the SQL Reference, Volume 2
Defining a generated column on a new table
A generated column is defined in a base table where the stored value is computed using an expression, rather than being specified through an insert or update operation.
Procedure:
When creating a table where it is known that certain expressions or predicates will be used all the time, you can add one or more generated columns to that table. By using a generated column there is opportunity for performance improvements when querying the table data.
For example, there are two ways in which the evaluation of expressions can be costly when performance is important:
1. The evaluation of the expression must be done many times during a query.
2. The computation is complex.
To improve the performance of the query, you can define an additional column that would contain the results of the expression. Then, when issuing a query that includes the same expression, the generated column can be used directly; or, the query rewrite component of the optimizer can replace the expression with the generated column.
It is also possible to create a non-unique index on a generated column.
Where queries involve the joining of data from two or more tables, the addition of a generated column can allow the optimizer a choice of possibly better join strategies.
The following is an example of defining a generated column on the CREATE TABLE statement:
CREATE TABLE t1 (c1 INT, c2 DOUBLE,
c3 DOUBLE GENERATED ALWAYS AS (c1 + c2) c4 GENERATED ALWAYS AS
(CASE WHEN c1 > c2 THEN 1 ELSE NULL END))
After creating this table, indexes can be created using the generated columns.
For example,
CREATE INDEX i1 ON t1(c4)
Queries can take advantage of the generated columns. For example, SELECT COUNT(*) FROM t1 WHERE c1 > c2
can be written as
SELECT COUNT(*) FROM t1 WHERE c4 IS NOT NULL
Another example:
SELECT c1 + c2 FROM t1 WHERE (c1 + c2) * c1 > 100
can be written as
SELECT c3 FROM t1 WHERE c3 * c1 > 100
Generated columns will be used to improve performance of queries. As a result, generated columns will likely be added after the table has been created and populated.
Related tasks:
v “Defining a generated column on an existing table” on page 195
Related reference:
v “CREATE INDEX statement” in the SQL Reference, Volume 2 v “CREATE TABLE statement” in the SQL Reference, Volume 2 v “SELECT statement” in the SQL Reference, Volume 2