Constraints in Teradata Teradata

One of our best Teradata training customers has a data warehouse with over 40,000 users. Even though their enterprise data warehouse may span continents, the data warehouse Return On Investment game is played on a field that is 5 inches wide. The space between the users ears! It is the users that make the data warehouse great. When users gain experience and use intuition, imagination, and experience they can find the company big dollars. When Users can ask any question, at any time, on any data, the boundaries are unlimited.

But sometimes boundaries are necessary! At times it is advisable to add restrictions to the table and columns within a table. This is done to provide data integrity, availability and ease of use to the table and its data. Users can still use their imagination and ask any question, but on all inserts, updates, and deletes Teradata will be watching.

Relational theory describes columns and the attributes a column can have as part of a domain. The domain includes the data type and valid value boundaries based on business requirements or restrictions. Because ANSI does not support a rigorous atomic definition of a domain, it is up to the database designer to define the domains for a table and their legal values by creating constraints on one or more columns. Constraints keep the table in check to enforce certain rules.

Think of domains as standards. If everyone in the company tracking information about employees sets up the employee number as an integer ranging from 1 – 100,000,000 then when there is cross functional analysis across business units each will see employee number as an integer. Comparisons will be easy. Make columns with the same functions the exact same data types (with the same range) and your columns are said to be from the same domain. Teradata has some fundamental rules about constraints:

  • Always name table level constraints
  • Constraint names can be up to 30 characters
  • Constraint names must be unique among all other constraint names defined for a table
  • Constraints can be specified at the column or table level
  • The system does not assign names to constraints you do not name

You also have the ability to define constraints on column values during the CREATE Table process. This can also be done using the ALTER command once the table has been created. Constraints generally fall into three areas:

  • Unique
  • Check
  • Referential Integrity

Constraints are defined at the column or table level

Constraints are defined at the column or table level

Constraints are defined at the column or table level

UNIQUE Constraint

The great writer Mark Twain was quoted as saying, "Whenever you find you are on the side of the majority, it is time to pause and reflect." Sometimes it is good to be on the side of the majority, but at other times it is best to be UNIQUE. The UNIQUE constraint is used to enforce uniqueness of values stored within the column(s). This means that no two rows in the table can have the same value for the column or columns utilizing the UNIQUE constraint.
An employee number is an excellent example. Each employee must have an employee number, and no two employees can ever have the same employee number. At the same time, we must ensure that no employee has NULL data for their employee number. The Teradata system enforces the uniqueness by making a column with the UNIQUE constraint a Unique Secondary Index (USI). Teradata makes the column a Unique Primary Index (UPI) only if during the TABLE CREATE statement no Primary Index or PRIMARY KEY is explicitly stated.

When a table is created using this constraint, rows containing duplicate values cannot be stored in the table. If the table is not created using a UNIQUE constraint and it is later altered to add the constraint, if the data is not unique the ALTER statement fails because the data violates the constraint.

CHECK Constraint

Even Wayne Gretzky, the greatest hockey player ever missed a check on occasion, but Teradata will not! The CHECK constraint allows for a range of values to be checked or for specific value limits to be placed on a column. For example you can check that a column value falls within a certain range such as EMP BETWEEN 1 AND 99. You can also check to see that a column value is greater than another column or value. As an example: CHECK EMP > 0. You can also CHECK EMP > DEPT. You can even utilize a compound check like EMP BETWEEN 1 AND 99 AND EMP > DEPT.

When a table is created using this constraint, rows containing invalid values cannot be entered into the table. If the table is altered later on with a CHECK, and the data is not valid for the constraint, the ALTER statement fails since the data violates the constraint.

Referential Integrity (RI) Constraint

Referential Integrity (RI) insists that a row cannot be inserted unless the value in the column has a corresponding value existing in another table. This also means a row cannot be deleted if a corresponding value in another table still exists. For example, imagine getting fired and your employer deletes you from the employee table, but forgets to delete you from the payroll table. A RI check can be used to enforce data integrity and prevent this scenario. Referential Integrity does not allow anyone to be deleted from the employee table unless they were already deleted from the payroll table. Darn, RI can hurt your Bahamas retirement.

When RI is established on a new table, invalid data values cannot be entered into a column. However, if a table is altered to begin enforcing RI, the data might already be incorrect. When this happens, the ALTER creates a copy of the original table and stores rows in it that violate the RI constraint. It is up to you to look for the table copy and correct any errors in the actual table. RI is the only constraint that can be added with data that violates the constraint.

Defining Constraints at the Column level

It is possible to establish the constraint directly on the column definition. It makes for an easy definition process. However, it does spread the varying constraints throughout the DDL statement. Sometimes the CREATE TABLE can be very large! This makes the constraints a bit more difficult for people to find. We have seen constraints placed at the column level on DDL so large that Magellan couldn't find his way through it.

The other issue for a column level definition is that only one column can be involved. Since some constraints may involve multiple columns, these definitions must be defined at the table level and not the column level. Both levels are covered in this section.

Here is an example of creating a table with column level constraints:

In the above table, the emp column must have a value because it cannot be null. It also has a primary key named EmpPK and therefore, becomes the UPI of this table because no Primary Index was explicitly defined.
The dept column has an RI constraint named Ref_1 on the column called dept in the Department table. This means that a dept cannot be entered into the employee table unless that dept exists in the department table. This is referential integrity as its best! There is a CHECK constraint called SalCheck on the salary column and it requires the salary to be at least $10,000.00 up to a maximum of $99,999.99.

The last two constraints are on the soc_sec column. First, there is the NOT NULL that requires a value to be stored there. Then, the NameUniq constraint requires that the value be different from any other value in other rows because it must be unique.

Defining Constraints at the Table Level

Besides using column level constraints, table level constraints can also be used. This is the only way to implement multi-column constraints. A multi-column constraint involves more than one column. All table level constraints should always be named. Table level constraints are established after the column definitions. Here is an example:

We feel this type of definition is easier to read and understand than looking for constraints throughout the DDL. These constraints are all the same as in the first example, with one exception. Here, the name constraint called NameUniq uses a combination of both the fname and the lname columns to create a USI. Notice too that NOT NULL must still be at the column level.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Teradata Topics