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

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 185 186 187 188 189 190 < 191 > 192 193 194 195 196 197 .. 241 >> Next


SELECT pubname, BookCount FROM inventory WHERE (BookCount, pubnum) IN

(SELECT count(*), pubnum FROM book GROUP BY pubnum);

But if the book count value of a publisher in the inventory is zero, this publisher name will not be returned even when no books are found for this publisher.

This is because no row is returned by the subquery for this publisher. In the current release, the following query with correlated subquery will return the correct answer in this case.

SELECT pubname, BookCount FROM inventory WHERE BookCount IN (SELECT count(*) FROM book WHERE book.pubnum=library.pubnum)

Teradata RDBMS for UNIX SQL Reference

9-17
Advanced SQL

Referential Integrity

Introduction

Referencing (Child) Table

Referenced (Parent) Table

Referential Integrity

Referential integrity is defined as all the following:

• The concept of relationships between tables, based on the definition of a primary key and a foreign key.

• A mechanism that provides for specification of columns within a referencing table that are foreign keys for columns in some other referenced table. Referenced columns must be defined as one of the following:

• Primary key columns

• Unique columns

• A reliable mechanism which prevents accidental database corruptions when doing inserts, updates, and deletes.

Referential integrity 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.

The referencing table is referred to as the Child table, and the specified Child table columns are the referencing columns.

Note: Referencing columns must have the same numbers and types of columns, data types and sensitivity as the referenced table keys. COMPRESS is not allowed on either the referenced or referencing column(s) and column level constraints are not compared.

A Child table must have a parent table, and the referenced table is referred to as the Parent table. The parent key columns that exist in the Parent table are the referenced columns.

Since the referenced columns are defined as unique constraints, the referenced columns must be one of the following:

• A unique primary index (UPI), not null

• A unique secondary index (USI), not null

9-18

Teradata RDBMS for UNIX SQL Reference
Terms Related to Referential Integrity

Advanced SQL

Referential Integrity

The following terms are used to explain the Referential Integrity

concept:

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 column(s) in the Child table. A foreign key may consist of up to sixteen different columns
Referential Constraint The constraint defined on a column or a table to ensure Referential Integrity. For example: CREATE TABLE A (A1 Char(10) REFERENCES B (B1), A2 Integer) PRIMARY INDEX (A1) FOREIGN KEY (A1,A2) REFERENCES C; This CREATE TABLE statement specifies the following integrity constraints:

This constraint . . . Is this level . . .
1 column. Foreign Key Al references the Parent Key Bl in table B.
2 table. Composite Foreign Key (Al, A2) references the Parent’s UNIQUE PRIMARY index which must be two columns of type CHAR(10), type INTEGER, and not null.

Teradata RDBMS for UNIX SQL Reference

9-19
Advanced SQL

Referential Integrity

Why is Referential Integrity important?

Rules for Assigning Columns as FOREIGN KEYS

Consider the employee and payroll table.

With Referential Integrity, the two tables work together as one. When one table gets updated, the other table should get updated too.

The following depicts a new Referential Integrity scenario:

Looking for a better career, Mr. Clark Johnson leaves his company. Clark Johnson is deleted from the employee table.

The payroll table, however, does not get updated because the payroll clerk simply forgets to do so. Consequently, Mr. Clark Johnson keeps getting paid.

Referential integrity would link these two tables, and, depending on the constants defined, the delete from the employee table could not be done until accompanied by a delete from the payroll table.

Besides data integrity and data consistency, Referential Integrity has

these benefits:
Benefit Description
Increases development productivity It is not necessary to code SQL statements to enforce referential constraints. The Teradata RDBMS automatically enforces Referential Integrity.
Requires fewer programs to be written All update activities are programmed to ensure that referential constraints are not violated. The Teradata RDBMS enforces Referential Integrity in all environments, no additional programs are required.
Improves performance The Teradata RDBMS chooses the most efficient method to enforce the referential constraints.
Previous << 1 .. 185 186 187 188 189 190 < 191 > 192 193 194 195 196 197 .. 241 >> Next