Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Introduction to the Teradata® RDBMS for UNIX® Version 2 Release 2.1 - NCR

NCR Introduction to the Teradata® RDBMS for UNIX® Version 2 Release 2.1 - NCR, 1998. - 315 p.
Download (direct link): inntroduktionteradata1998.pdf
Previous << 1 .. 20 21 22 23 24 25 < 26 > 27 28 29 30 31 32 .. 76 >> Next


Referential constraints must meet the following criteria:

• The parent key must exist when the referential constraint is defined.

• The parent key columns must be either a unique primary index (UPI) or a unique secondary index (USI).

• The foreign and parent keys must have the same number of columns and their data types must match.

• The foreign and parent keys cannot exceed 16 columns.

• Duplicate referential constraints are not allowed.

• You cannot drop or alter either foreign or parent keys using an ALTER TABLE statement after a referential constraint has been defined.

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.

Introduction to the Teradata RDBMS for UNIX

3-11
The Relational Model

Referential Integrity

Referential Constraint Checks

• The foreign key must be equal to the parent key or it must be null.

• Self-reference is allowed, but the foreign and parent keys cannot consist of identical columns.

• You can define no more than 64 referential constraints per table.

A maximum of 64 tables can reference a single table.

Create referential constraints using the CREATE TABLE statement with the REFERENCES option.

Add or drop referential constraints using the ALTER TABLE statement with the FOREIGN KEY and REFERENCES options.

Rollforwards and rollbacks on either a parent or child table result in both tables becoming not valid. To resolve these inconsistent references, use the ALTER TABLE statement with the DROP FOREIGN KEY and REFERENCES options.

To resolve inconsistent references that occur because of a Restore or Copy operation on either the parent or the child table, use the ALTER TABLE statement with the DROP INCONSISTENT REFERENCES option.

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

• A referential constraint is added to a populated table

• A row is inserted, deleted, or updated

• A parent or foreign key is modified

The following table summarizes these actions.

Action on RDBMS Constraint check performed
INSERT into parent table None.
INSERT into child table Must have matching parent key value if the foreign key is not null.
DELETE from parent table Abort the request if the deleted parent key is referenced by any foreign key.
DELETE from child table None.
UPDATE parent table Abort the request if the parent key is referenced by any foreign key.
UPDATE child table New value must match the parent key when the foreign key is updated.

3-12

Introduction to the Teradata RDBMS for UNIX
The Relational Model

Teradata Macros

Introduction

What is a Teradata Macro?

Creating a Macro

Using a Macro

Teradata Macros

This topic describes Teradata SQL macros: what they are and how advantageous it is to use them in many situations.

Teradata macros are SQL code that is stored on the server. Macros act as a single transaction to perform complex tasks. They are typically used to reduce the number of characters that must be entered to specify an operation, saving the user time and decreasing the chance of making errors. Teradata macros are something of a cross between an interactive query and an application program using embedded SQL.

You create macros much the same way you create views, using the CREATE MACRO statement.

The following example statement defines a macro for adding new employees to the Employee table and incrementing the EmpCount field in the Department table.

CREATE MACRO NewEmp (name (VARCHAR(12)), number (INTEGER, NOT NULL), dept (INTEGER, DEFAULT 100))

AS (INSERT INTO Employee (Name, EmpNo, DeptNo)

VALUES (:name, :number, :dept);

UPDATE Department SET EmpCount=EmpCount + 1 WHERE DeptNo=:dept;);

Note the host variable-like parameters that begin with a colon character. The macro fills in the values for these with data you provide each time you execute it.

This example shows how you might use the NewEmp macro to add employee Goldsmith to the Manufacturing department.

EXECUTE NewEmp ('Goldsmith H', 10015, 600);

Introduction to the Teradata RDBMS for UNIX

3-13
The Relational Model

Teradata Macros

Modifying a Macro

Deleting a Macro

Use the REPLACE MACRO statement to modify a macro. The following statement replaces NewEmp with a macro that changes the default department number from 100 to 300.

REPLACE MACRO NewEmp (name (VARCHAR (12)), number (INTEGER, NOT NULL), dept (INTEGER, DEFAULT 3 00))

AS (INSERT INTO Employee (Name, EmpNo, DeptNo)

VALUES (:name, :number, :dept);

UPDATE Department SET EmpCount=EmpCount+1 WHERE DeptNo=:dept;);

Use the DROP MACRO statement to delete a macro. The following statement removes the NewEmp macro from the database.

DROP MACRO NewEmp;

3-14

Introduction to the Teradata RDBMS for UNIX
The Relational Model

For More Information

For More Information

For more information on the topics presented in this chapter, see the following Teradata RDBMS manuals.
Previous << 1 .. 20 21 22 23 24 25 < 26 > 27 28 29 30 31 32 .. 76 >> Next