Constraint Design
DEFAULT and NULL Constraints
- [Proposal] If all the column values can be obtained from services, you are not advised to use the DEFAULT constraint. Otherwise, unexpected results will be generated during data loading.
- [Proposal] Add NOT NULL constraints to columns that never have NULL values. The optimizer automatically optimizes the columns in certain scenarios.
- [Proposal] Explicitly name all constraints excluding NOT NULL and DEFAULT.
Partial Cluster Keys
A partial cluster key (PCK) is a local clustering technology used for column-store tables. After creating a PCK, you can quickly filter and scan fact tables using min or max sparse indexes in openGauss. Comply with the following rules to create a PCK:
- [Notice] Only one PCK can be created in a table. A PCK can contain multiple columns, preferably no more than two columns.
- [Proposal] Create a PCK on simple expression filter conditions in a query. Such filter conditions are usually in the form of col op const, where col specifies a column name, op specifies an operator (such as =, >, >=, <=, and <), and const specifies a constant.
- [Proposal] If the preceding conditions are met, create a PCK on the column having the most distinct values.
Unique Constraints
- [Notice] Unique constraints can be used in row-store tables and column-store tables.
- [Proposal] The constraint name should indicate that it is a unique constraint, for example, UNIIncluded columns.
Primary Key Constraints
- [Notice] Primary key constraints can be used in row-store tables and column-store tables.
- [Proposal] The constraint name should indicate that it is a primary key constraint, for example, PKIncluded columns.
Check Constraints
- [Notice] Check constraints can be used in row-store tables but not in column-store tables.
- [Proposal] The constraint name should indicate that it is a check constraint, for example, CKIncluded columns.
Feedback