Informational constraints for Datalake tables

Constraints are associated with tables and are defined as part of the table creation process (by using the CREATE DATALAKE TABLE statement) or are added to a table definition after the table has been created (by using the ALTER DATALAKE TABLE statement).

You can also use the ALTER DATALAKE TABLE statement to modify constraints. In most cases, existing constraints can be dropped at any time; this action does not affect a table's structure or its data.

Constraints that are associated with Datalake tables are considered to be informational only; they are created by specifying the NOT ENFORCED clause. An informational constraint is a constraint attribute that can be used by the SQL compiler to improve access to data. Informational constraints are not used for additional verification of data. They are used only to improve query performance.

Important: Because these constraints are not enforced, no error is returned if the table data violates an informational constraint, and incorrect results might occur as a result. When using an informational constraint, it is critical to ensure that your data adheres to the constraint definition outside of Db2 by using extract, transform, and load (ETL) tools or logic if the data is sourced from a database system that has a similar (but enforced) constraint.

For more information about constraints, see Constraints. For more information about the performance benefits of using informational constraints, see Improve performance by using informational constraints in Db2 Big SQL. The preceding article pertains to Big SQL, but it is applicable to Db2 usage of Datalake tables.