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_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
- 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 |
|
Reassign |
|
Nullify |
|
Cascade |
|
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. |