Domains and Referential Integrity | Database Design | Teradata Vantage - Domains and Referential Integrity - Advanced SQL Engine - Teradata Database

Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
kko1591750222108.ditamap
dita:ditavalPath
kko1591750222108.ditaval
dita:id
B035-1094
lifecycle
previous
Product Category
Teradata Vantage™

This topic describes various domain rules for primary (more accurately candidate) and foreign keys.

Definition of a Domain

A domain is a data type defined with explicit constraints. A data type is a well-defined set of values. For example, the data type INTEGER represents all the possible integer numbers (using a 4-byte two’s complement representation), while an INTEGER column defined with an explicit CHECK constraint condition such as “count BETWEEN 25 AND 50” defines a domain on the set of numbers having the INTEGER data type. More unambiguous data types are usually application-specific and you can define them using the various facilities available to you for defining user-defined types and their associated constructs such as methods, orderings, and so on. See Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 and Teradata Vantage™ - SQL External Routine Programming, B035-1147 for more details about user-defined data types.

Table Definition for Examples

An Employee table is used for the examples described in this topic. The definition of the Employee table is given by the following table.

Employee
employee_number last_name supervisor_employee_number
PK,SA   FK
1 Smith null
2 Brown 1
3 White 1
4 Gibson 2
5 Black 2
6 Jones 3
7 Mason 3

The following domain rule applies to the employee_number column: Its data type is INTEGER and any value inserted into the column must have a value greater than zero.

Domain Rules for Primary Keys

The following rules apply to primary key domains and referential integrity. The domain rules for primary keys apply equally, the necessary changes being made, to all candidate keys:
  • New primary key values must be drawn from the domain set of all valid values for the column on which the primary key value is defined.
  • You cannot define a primary key using any column defined with an XML, BLOB, or CLOB data type.
  • You cannot delete a primary key that references foreign keys because that violates the referential integrity rule.

    The rules for deleting a primary key that has foreign key references are as follows:

    Rule Name                                                    Rule Description
Prevent
  • Do not delete a PK if it references an existing FK.
  • Do not change the value of PK if it references an existing FK.
Reassign
  • Change the value of FK to a different PK value before deleting the old PK row.
  • Change the value of FK to a different PK value before changing the old PK value.
Nullify
  • Change the FK value to NULL before deleting the old PK row.
  • Change the FK value to NULL before changing the old PK value.
Cascade
  • Delete the FK row before deleting the old PK row.
  • Delete the FK row before changing the old PK value.

Each of these rules preserves the referential integrity of a PK-FK relationship.

Questions and Answers for Primary Key Domains

The following questions and answers about primary keys indicate some examples of how domain integrity is enforced. The questions refer to the employee table defined in the previous section, Table Definition for Examples.

Question Answer Explanation
Is it valid to add Employee 12345?       Yes 12345 is a positive integer.
Is it valid to delete the row for Employee 5?       Yes Employee 5 is not referenced by any other row.
Is it valid to add Employee -23.67?       No -23.67 is a negative decimal number.
Is it valid to delete the row for Employee 2?       Yes Cascade rule.

Domain Rule for Foreign Keys

Foreign key values can be added only when they are drawn from the set of existing primary key values.

Questions and Answers for Foreign Key Domains

The following questions and answers about foreign keys indicate some examples of how domain integrity is enforced. The questions refer to the employee table defined in the previous section, Table Definition for Examples.

Question Answer Explanation
Is it valid to have Employee 6 report to Employee 4?       Yes Employee 4 is drawn from the set of existing employee numbers.
Is it valid to delete the row for Employee 7?       No Employee 7 has an assigned foreign key value.
Is it valid to have Employee 6 report to Employee 8?       No Employee 8 is not drawn from the set of existing employee numbers.