Referential Integrity

Ruihan Wang

Introduction

Often, in relational database, we wish to ensure that a value that appears in one relation for a given set of attributes also appears for a certain set of attributes in another relation. This condition is called Referential integrity (RI). It is the concept of relationships between tables, based on the definition of a primary key and a foreign key.

The following table includes the important elements of Referential Integrity:

Term Definition

Child Table A table, where the referential constraints are defined. A child table is synonymous with the referencing table.

Parent Table The table being referenced by a Child table. A Parent table is synonymous with the referenced table.

Primary Key A primary key uniquely identifies a row of a table.

Foreign Key A foreign key refers to columns in the Child table. A foreign key may consist of up to 16 columns.

Referencing columns A referencing columns are within a referencing table that are foreign keys for columns in some other referenced table.

Referenced columns A referenced columns are defined as either primary key columns or unique columns in a referenced table.

In the Teradata RDBMS, there are three choices to implement RI:

Use the referential constraint checks supplied by the database software.
Write site-specific macros.
Enforce constraints through application code.

Teradata RDBMS provides a reliable mechanism to prevent accidental erasure or corruption of data in a database and ensuring data integrity and data consistency.

Importance of Referential Integrity

By providing specification of columns within a referencing table that are foreign keys for columns in some other referenced table, referential integrity is a reliable mechanism which prevents accidental database corruptions when doing inserts, updates, and deletes. It states that a row cannot exist in a table with a non-null value for a referencing column if an equal value does not exist in a referenced column.

For example, let's suppose in customer_service database, there is a tuple t1 in the employee_phone relation with t1[employee_number] = '1018', but there is no tuple in the employee relation for the 1018. This situation would be undesirable. We expect the employee relation to list all employee_number. Therefore, tuple t1 would refer to an employee that does not exist. Clearly, we would like to have an integrity constraint that prohibits this sort of situation. We could define employee_number in employee relation as a foreign key in employee_phone relation. Under this situation, employee is the referenced table, employee_name of employee table is the referenced column; employee_phone is the referencing table, employee_name of employee_phone is the referencing column.

Once we define the employee_number as a foreign key in employee_phone relation, if we try to insert a row with a primary key value that does not exit in employee table, the system will not allow this insertion. This is the way the Teradata RDBMS maintains referential integrity. The following summarize the benefits of referential integrity:

Ensure data integrity and consistency base on primary key and foreign key
Increases development productivity, because it is not necessary to code SQL statements to enforce referential constraints, the Teradata RDBMS automatically enforces referential integrity.

For large database system like the Teradata RDBMS, it is critique to ensure referential integrity.

Defining Referential Constraints

Referential constraint provide a means of ensuring that changes made to the database by authorized users do not result in a loss of data consistency. The constraints are in the following forms:

Key declarations The stipulation that certain attributes form a candidate key for a given entity set. The set of legal insertions and updates is constrained to those that do not create two entities with the same value on a candidate key.
Form of a relationship Many to many, one to many, one to one. A one-to-one or one-to-many relationship restricts the set of legal relationships among entities of a collection of entity sets.

So in general, the referential constraint is the combination of the foreign key, the parent key, and the relationship between them.

Proper definition of referential constraints not only allows us to test values inserted in the database, but also permits us to test queries to ensure that the comparisons made make sense. The check clause in the Teradata RDBMS permit data to be restricted in powerful way that most programming language type systems do not permit. Specifically, the check clause permits the schema designer to specify a predicate that must be satisfied by any value assigned to a variable. The referential constraint can be defined by CREATE TABLE statement.

Referential constraints must meet the following criteria:

The Parent key must exist when the referential constraint is defined, it must be either a unique primary index or a unique secondary index.
The foreign and parent keys must have the same number of columns and their data types must match, they cannot exceed 16 columns.
Duplicate referential constraints are not allowed.
self-reference is allowed, but the foreign and parent keys cannot consist of identical columns.
There can be no more than 64 referential constraints per table.

Examples of SQL data definition for key declarations

Referential constraints in SQL

Primary and foreign keys can be specified as part of the SQL CREATE TABLE statement:

Key word Function

Primary key The primary key clause of the CREATE TABLE statement includes a list of the attributes that constitute the primary key.

Unique The unique clause of the CREATE TABLE statement includes a list of the attributes that constitute a candidate key.

Foreign key The foreign key clause of CREATE TABLE statement includes both a list of the attributes that constitute the foreign key and the name of the relation referenced by the foreign key.

Using the partial SQL DDL definition of our customer_service database as an example to illustrate the syntax of primary- and foreign-key declarations.

CREATE TABLE employee_phone

(employee_number INTEGER NOT NULL,

area_code SMALLINT NOT NULL,

phone INTEGER NOT NULL,

extension INTEGER,

comment_line CHAR(72)

primary key(employee_number)

foreign key(employee_number) references employee)

PRIMARY INDEX (employee_number);

This CREATE TABLE statement is used for creating the table employee-phone with the following referential constraints.

Restricts the employee_number, area_code, phone domains do not contain null value.
Defines the employee_number domain as a primary key
Defines the employee_number domain in employee table as a foreign key

After a referential constraint has been defined, it can be dropped or altered by an ALTER TABLE statement. To drop a foreign or parent key after a referential constraint has been defined, you must first drop the constraint and then alter the table.

Examples of SQL data definition for check constraints

Referential constraint checks

The Teradata RDBMS performs referential constraints checks whenever any of the following occur:

A referential constraint is added to a populated table The table will be check by the new referential constraint, if the referential integrity is violated, error message appears and the request is aborted.
A row is inserted, deleted or updated

RDBMS statement	Constraint check performed
INSERT	For parent table: None; for child table: Must have matching parent key value if the foreign key is not null
DELETE	For parent table: Abort the request if the deleted parent key is referenced by any foreign key; for child table: None
UPDATE	For parent table: Abort the request if the parent key is referenced by any foreign key; for child table: New value must match the parent key when the foreign key is updated.

A parent or foreign key is modified Check if there are any violations of the referential constraints, if there are, error message appears and the request is aborted.

Examples of SQL statements involving referential constraint check

Term	Definition
Child Table	A table, where the referential constraints are defined. A child table is synonymous with the referencing table.
Parent Table	The table being referenced by a Child table. A Parent table is synonymous with the referenced table.
Primary Key	A primary key uniquely identifies a row of a table.
Foreign Key	A foreign key refers to columns in the Child table. A foreign key may consist of up to 16 columns.
Referencing columns	A referencing columns are within a referencing table that are foreign keys for columns in some other referenced table.
Referenced columns	A referenced columns are defined as either primary key columns or unique columns in a referenced table.

Key word	Function
Primary key	The primary key clause of the CREATE TABLE statement includes a list of the attributes that constitute the primary key.
Unique	The unique clause of the CREATE TABLE statement includes a list of the attributes that constitute a candidate key.
Foreign key	The foreign key clause of CREATE TABLE statement includes both a list of the attributes that constitute the foreign key and the name of the relation referenced by the foreign key.