13.19.2 Defining check constraints
(1) Limitations on tables for which check constraints are defined
This subsection describes limitations that apply to the definition of tables for which check constraints are defined and to modification of the definitions of such tables.
(a) During table definition (CREATE TABLE)
- Check constraints cannot be defined for a falsification prevented table.
- You can define a maximum of 254 check constraints per table; you must be careful not to define more than 254 check constraints. The following shows an example of a table definition that is not valid:
![[Figure]](figure/zu120306.gif)
This definition is invalid because there are more than 254 check constraints. This example would result in an error during table definition.
- For each table, you can define a maximum of 254 check constraints separated by ANDs and ORs, including the ANDs and ORs of search conditions in the individual check constraints (this number does not include ANDs and ORs for search conditions in CASE expressions and in those search conditions). The following shows an example of a table definition that is not valid:
![[Figure]](figure/zu120307.gif)
This example contains two check constraints, plus there are 200 ANDs in the search conditions in the constraint named CHECK_T1_C1 and 53 ANDs in the search conditions in the constraint named CHECK_T1_C2. The sum of the number of check constraints and the number of ANDs and ORs in the search conditions in the check constraints is 255 (2 + 200 + 53), which is greater than 254. Therefore, this definition is invalid and would result in an error during table definition.
The sum of the number of check constraints defined for the table and the number of ANDs and ORs in the search conditions in each check constraint is stored in the N_CHECK_LIMIT column of the SQL_TABLE data dictionary table.
(b) During table modification (ALTER TABLE)
- You cannot use the DROP and RENAME clauses in modifying the table definition of a check constraint table.
- You cannot use the CHANGE clause to modify a constraint table in the following ways:
- Changing the data type and data length
- Changing SPLIT
- Setting and releasing the default value
- Setting WITH DEFAULT
- The RENAME clause cannot be used to rename columns of a check constraint table.
(2) Notes when defining a check constraint
- Estimating the size of the SQL object buffer length
When you perform operations on a check constraint table, HiRDB generates triggers to check constraint conditions. Therefore, you must take into account the SQL objects of the constraint conditions generated by HiRDB when you specify the SQL object buffer. For details about how to estimate the SQL object buffer length (pd_sql_object_cache_size), see the manual HiRDB Version 8 System Definition.
- Backing up data
The extent of data that is backed up differs depending on the check pending status at backup time. For details about the backup time and extent, see RDAREAs to be backed up together in the manual HiRDB Version 8 System Operation Guide.
- Reorganizing data dictionary RDAREAs
When you repeat definition and deletion of check constraint tables, storage efficiency of the data dictionary RDAREA decreases. In such a case, use the database condition analysis utility (pddbst) to check the storage efficiency of the data dictionary RDAREA and reorganize the area as necessary.