15.00 - Database Constraints and Enterprise Business Rules - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Database Constraints and Enterprise Business Rules

Constraints are a physical implementation of the business rules under which an enterprise operates. Integrity constraints are a method of restricting database updates to a set of specified values or ranges of values. They ensure not only that bad data does not get into the database, but also that intertable relationships do not become corrupted by the improper deletion or updating of data from the existing database.

The basic types of integrity constraints are:

  • Semantic
  • Physical
  • Teradata Database also provides a referential constraints, which provides the Optimizer with a means for devising better query plans, but which does not enforce the referential integrity of the database. See “CREATE TABLE” in SQL Data Definition Language for more information.

    Definitions

     

    Term

    Definition

    Business rule

    A component of the business model that defines specific conditional modes of activity.

    Business rules are expressed in natural language and are represented in a relational database by integrity constraints. They are the ultimate determining factor for proper database design.

    Constraint

    A predicate that must evaluate to TRUE if a database DELETE, INSERT, or UPDATE operation is to be permitted.

    Integrity constraint

    A component of the logical database model that formalizes business rules by specifying the boundary conditions and ranges permitted for various database parameters.

    Integrity constraints are one of the four components of an integrity rule and are expressed in the language of databases: tables, columns, rows, and so on.

    Integrity rule

    A set of rules for ensuring the integrity of a database. Each integrity rule is composed of a:

  • Name
  • Integrity constraint set
  • Checking time
  • Violation response
  • The checking time specifies the processing point at which the constraint is checked. In the ANSI/ISO SQL standard, a checking time has either of the following possible values:

  • Immediate
  • Deferred
  • All Teradata Database constraints have an immediate checking time. Deferred constraint checking, which is never a good way to ensure integrity, is not permitted.

    The violation response specifies the action to be taken when an integrity constraint is violated. In the ANSI/ISO SQL standard, a violation response has either of the following possible values:

  • Reject
  • Compensate
  • All Teradata Database constraints have a reject violation response. Compensate violation responses are not permitted.

    Integrity rules are specified by the SQL CREATE TABLE and ALTER TABLE statements.