• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. Development and Design Proposal
  5. Database Object Design
  6. Constraint Design

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, because doing so will generate unexpected results 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 Key

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 DWS. 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 Constraint

  • [Notice] Unique constraints can be used in row-store tables but not in column-store tables.
  • [Proposal] The constraint name should indicate that it is a unique constraint, for example, UNIIncluded columns.

Primary Key Constraint

  • [Notice] Primary key constraints can be used in row-store tables but not in column-store tables.
  • [Proposal] The constraint name should indicate that it is a primary key constraint, for example, PKIncluded columns.

Check Constraint

  • [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.