referential integrity

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

in relationship options there is the choice of referential integrity

can any one tell me what difference does it make? explain it to me please.

I understand the other two options

Thank you
 
R,

In a nutshell... With Referential Integrity enforced, you won't be
allowed to have a record in the table on the "many" side of a
one-to-many relationship without a corresponding record existing in the
table on the "one" side.
 
To add to Steve's answer, I still remember a definition that an instructor
gave to us in an Oracle course I took way back in 1994 or so:

Referential Integrity <---> No Orphans

The classic example given is a table of Customers and a table of Orders.
With RI enforced, you are not allowed to enter a CustomerID into the Orders
table unless that same CustomerID value can be found in the Customers table.
This helps protect against order records being entered into a database with
no known customer.

Access will still allow one to have a null CustomerID field in the Orders
table with RI enforced, until you set the required value (or validation rule:
is not null with appropriate validation text).


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
a follow up question regarding ref integ:
can u explain (like you would to a child) the Cascade option and the delete
option?
 
I'll certainly make an attempt to do so!

Cascade Update is only needed *if* one needs to change the value for a given
record in the primary key (PK). Take a look at the sample Northwind database
as an example. You will notice that the Customers table uses a text-based PK
that is based on the name of the company. For example, Alfreds Futterkiste
has a PK value = ALFKI. Now, if Alfred sells his business to Sally, she may
wish to rename it to Sally's Deli. At this point, the PK value in the
database would not exactly match any recognizable form of the business name.
The Cascade Update option allows a database administrator (DBA) to change
ALFKI, to something else, such as SADLI. The change will automagically
"cascade" to all related records in the Orders table. Without Cascade Update
enabled, the DBA would not be allowed to make this change, because doing so
would result in unmatched order records for ALFKI that did not have a
matching parent record.

Cascade delete will cause all related records in a child table to be
automatically deleted, if the parent record is deleted. In the Northwind
example, if you delete Alfred's Futterkiste from the Customers table, and
Cascade Delete is enabled, all of Alfred's Orders will be removed as well.
This would, of course, require that cascade delete was also enabled for the
relationship between the Orders table and the Order Details table, since
enforced RI would not allow for unmatched Order Detail records to exist.

Whether one uses text-based (natural) PK or surrogate (autonumber) PK is up
to the individual developer. There are people strongly aligned on both sides
of the fence. I am personally on the surrogate autonumber side of the fence.
I'll certainly respect the opinion of others who are firmly convinced that
using natural PK is always better; I'm simply not in that camp. One advantage
of using surrogate keys is that you should never need to change the value,
since should be considered a meaningless number. Thus, no need for Cascade
Update when one uses an autonumber PK.

Here is some background information on using autonumber PK:

http://www.access.qbuilt.com/html/articles.html
(See article # 4: The Case for the Surrogate Key)

Database Normalization Tips
by Luke Chung
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html
(See the section titled "Use Meaningless Field for the Key Field")

I have an intense dislike for cascade deletes in Microsoft Access. And I
don't like cascade updates.
by Access MVP Tony Toews
http://www.granite.ab.ca/access/cascadeupdatedelete.htm


Okay, so perhaps that's a bit more advanced than how I might attempt to
present such a concept to a child--actually, I probably wouldn't even
try--but have I left you with a clear understanding of the concept?


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
perfect ! answered all my q's...

ps: how was DC? I had an exclusive tour prepared for you, but you didnt
call (probably got busy)...
 
Hi Vinny,

I'm sorry. I have to admit that things got so busy for Cindy and me, as we
were preparing for our trip, that I forgot to take your contact information
with me. I had no way of contacting you.

I plan on returning in sometime in the August/September time frame. Is there
any way I can take a rain check on that nice offer?


Tom Wickerath, Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
Back
Top