[thelist] Access Tip

[rudy limeback]

> ... you run into some real problems when you rely on RI to take
> care of your joins, etc.... it's good practice to do your joins in your
> SQL queries anyway, the more you do the better you will get the
> hang of it..

well, i can't argue with that, but the point of relational integrity is
hardly to help you with your join syntax

are you talking about the difference between

   select foo
     from table1, table2
      where table1.pk = table2.fk

and

   select foo
     from table1 inner join table2
       on table1.pk = table2.fk

these are equivalent

the real joy of relational integrity is that it frees up the need for a
great whack of coding that people traditionally (e.g. before RI) used to do
in the application

relational integrity means the database takes care of checking that there
actually is a row in the parent table with the pk that you're trying to
insert into the fk of the child table

(did i say that okay?)

naturally, you still have to handle errors gracefully (e.g. "you cannot add
an order for a non-existing item") but it's a lot easier -- to say nothing
of being twice as efficient -- to trap an RI error than to test the damn
condition in a separate query yourself

relational integrity is also known for the nice stuff it can do on certain
actions -- probably the best known is the "cascading delete" where when you
delete a parent row, all the child rows get deleted too, which again saves
you a whack of extra coding

more obscure are the RI actions for update and insertion, which most
databases require be implemented as triggers -- for example, insert a row
into a child table that has a foreign key to parent table, and if there is
no parent row for the particular fk value you're inserting, *insert the
parent row first* and then allow the insert of the child to proceed

a real life example?  add an article to a content database, assign it a
bunch of keywords, and if you happen to assign a keyword that didn't exist
before, RI will insert the keyword into the keyword table automatically
(like i said, it usually requires a trigger)

if you have ever found "orphan" records in a database, child records
pointing to parents that don't exist, you will understand the real benefit
of relational integrity

you see, if you leave it to the application developers to test for all the
conditions they should be testing for, the project will take a lot longer
to finish, and/or they will forget some   ;o)


rudy