Home > Articles > Data > SQL

  • Print
  • + Share This
From the author of

Breaks In Chains

Because views can be defined in terms of other views and tables, it's possible to wind up with a chain of objects, each dependent on another. Just as an actual chain can break at any link, so can a chain of objects. Any one of the views or tables in the chain might be redefined so that its dependent views no longer make sense.

As an example, here are three generations of views derived on the authors table.

create view number1
as 
select au_lname, phone
from authors
where zip like '94%'

select * 
from number1

au_lname                                 phone    
---------------------------------------- ------------ 
Bennet                                   415 658-9932
Green                                    415 986-7020
Carson                                   415 548-7723
Stringer                                 415 843-2991
Straight                                 415 834-2919
Karsen                                   415 534-9219
MacFeather                               415 354-7128
Dull                                     415 836-7128
Yokomoto                                 415 935-4228
White                                    408 496-7223
Hunter                                   415 836-7128
Locksley                                 415 585-4620
(12 row(s) affected)

create view number2
as 
select au_lname, phone
from number1
where au_lname > 'M'
 
select * 
from number2

au_lname                                 phone    
---------------------------------------- ------------ 
MacFeather                               415 354-7128
Straight                                 415 834-2919
Stringer                                 415 843-2991
White                                    408 496-7223
Yokomoto                                 415 935-4228
(5 row(s) affected)

create view number3
as 
select au_lname, phone
from number2
where au_lname = 'MacFeather' 

select * 
from number3

au_lname                                 phone    
---------------------------------------- ------------ 
MacFeather                               415 354-7128
(1 row(s) affected)

What happens to number3 if you redefine number2 with different selection criteria?

  • A condition on a column available in number2's underlying table number1 (either au_fname or phone) works fine. The WHERE clause determines what, if any, data is seen through number3.

  • A condition such as a zip code matching "947nn" causes failure in creating number2 or displaying data through it because the zip column exists in neither number1 nor number2. With number2 in trouble, there's not much hope for retrieving data through number3.

Whatever you do to view number2, view number3 still exists, and becomes usable again by dropping and re-creating view number2.

  • + Share This
  • 🔖 Save To Your Account