Data Constraints

From Open Risk Manual


Data validation is considerably aided by formal data constraints as those that are implemented in RDBMS systems (databases). The constraints fall generally into the following categories, grouped according to the aggregation level at which the constraints apply

Field Specific

  • Data-Type Constraints. Values in a particular table column must be of a particular datatype, e.g., Boolean, Numeric with specified precision (integer or real), Date, or Choice fields
  • Range Constraints: Numbers or Dates should fall within a certain range (have minimum and/or maximum permissible values)
  • Mandatory Constraints: Certain fields cannot be empty (Null), e.g Identifier columns
  • Set-Membership constraints: The values for a Choice field come from a set of discrete values or codes. For example, a person's gender may be Female, Male or Unknown (not recorded).
  • Regular Expression patterns: A text field may have to conform to some prescribed pattern (e.g a phone number)

Cross-Field Checks

  • Unique Constraints: A field, or a combination of fields, must be unique across a dataset (Primary Key)
  • Foreign-key constraints: The set of values in a column is defined in a column of another table that contains unique values. For example, in a US taxpayer database, the "state" column is required to belong to one of the US's defined states or territories: the set of permissible states/territories is recorded in a separate States table. The term foreign key is borrowed from relational database terminology.
  • Cross-field validation: Certain conditions that utilize multiple fields must hold. For example, a set of fields that represent the decomposition of a grouped item (e.g. short term / long term liabilities versus total liabilities) cannot have a sum exceeding the total.