Constraints Form | Database Design | Teradata Vantage - 17.10 - Constraints Form - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
User Guide
Publication ID
B035-1094-171K
Language
English (United States)

Database constraints and validity checks are sometimes loosely referred to as business rules (see Semantic Data Integrity Constraints and Semantic Constraint Specifications). They define conditions that must be met before a given value is permitted to be written to a column such as value ranges, equality or inequality conditions, and intercolumn dependencies. Vantage supports constraints at both the column and table levels. Constraints are defined using the CREATE TABLE and ALTER TABLE SQL statements.

You cannot define any type of database constraint on columns defined with a UDT, XML, BLOB, or CLOB data type.

Database triggers, too, are often referred to as business rules. They define certain actions that are to be taken when a particular condition occurs during the update of the table on which they are defined.

The Constraints form:
  • Lists all constraints, triggers, and validity checks defined for a database.
  • Provides input to the Table form.

This form supports the second step in the ATM process (see Goals of the ATM Process).

Information Recorded on the Constraints Form

The Constraints form records the following information about each constraint, trigger, and validity check.

Information Recorded Description
ELDM Page Page of the Extended Logical Data Model to which this constraint pertains.
System Name of the business system in which the applications using this constraint are defined.
Constraint Number The number that applies to this constraint. This number is used by other forms as a “foreign key” value to refer to the constraints defined by this form.
Constraint Description Full text description of the constraint.

Constraint Codes

Code Definition
FK Foreign key
ID Identity column
NC No changes permitted
ND No duplicates permitted
NN Not null
PK Primary key
SA System-assigned
UA User-assigned

Example: Constraint Codes

Constraints

Page: ___    of: ___

ELDM Page: _____

System: ________

Constraint Number Constraint Description
001 ND, NN, NC, PK
002 Must be greater than zero (whole positive integer)
003 Mutually exclusive: One required
004 Mutually exclusive: All required
005 No recursion and no loops
006 Excludes Saturdays, Sundays, and legal holidays
007 Prevent delete rule
008 Reassign delete rule
009 Nullify delete rule
010 Cascade delete rule
011 Copy rows to History table before deleting
012 Trigger update to OrderPart Category table on insert to Order or Part tables.