5.8 Multiple Table Access Structures

5.8 Multiple Table Access Structures

Declarative referential integrity (DRI) in SQL-92 gives vendors the opportunity to add structures that in effect ?prejoin? the tables involved in a foreign key relationship. This might be easier to see with a small example. Let?s create two tables and note where they physically store their rows:

CREATE TABLE Foo
(fookey INTEGER NOT NULL PRIMARY KEY,
description CHAR(25) NOT NULL);
INSERT INTO Foo
VALUES (1, 'This is one'), ? goes to address 101
(2, 'This is two'); ? goes to address 102

CREATE TABLE Bar
(barkey CHAR(2) NOT NULL PRIMARY KEY,
foo_stuff INTEGER NOT NULL
REFERENCES Foo(fookey),
. . . );

INSERT INTO Bar
VALUES _('a1', 1), ? goes to address 4001
('a2', 1), ? goes to address 4007
('b1', 2), ? goes to address 4011
('b2', 2); ? goes to address 4003

In this case, Foo is the referenced table and bar is the referencing table. Those words are easy to confuse when you just hear them, so I stressed the relationship.

The table Foo needs a primary index to enforce the uniqueness of its primary key, fookey. Likewise, the table Bar needs a primary index to enforce the uniqueness of its primary key, barkey.

However, I can also construct something like this, with pointers to the rows in both tables:

value

referenced row address

referencing row address

101

4001

101

4007

102

4003

102

4011

The join between Foo and Bar is already done!

< Previous Excerpt Next Excerpt >

Purchase This Book

Joe Celko's Data and Databases: Concepts in Practice

TABLE OF CONTENTS

5.8 Multiple Table Access Structures

Contact Preferences

This is embarrasing...

Customize Your GlobalSpec Experience

Select Your Free Newsletters

Industry Newsletters

Select Your Free Product Alerts

This is embarrasing...