Enforce referential integrity when to use in relationship?

[Marco Simone]

Hi,

Is it good to use option Enforce referential integrity in relationship of
tables? There are also too options cascade update related fields and cascade
delete related fields? Can someone give advise what are pros and cons of
this option. I am newbee, sorry for this easy questions?

Thanks for your answer, Marco

 

[Howard Brody]

I always enforce Referential Integrity and always cascade
updates where I can. It means that any changes you make
to the 'One' field in the relationship are carried down to
the 'Many' side.

For example, you sell a product named "Scrub". It appears
once in your Master Item table (One) but many times in
your Sales table (Many). If you change the name
from "Scrub" to "Easy Scrub" in your master (one) table,
everywhere it appears in the Sales table, the name will
also change automatically.

I usually avoid cascading deletions. Instead of
cascadoing the updates, if you delete the 'One' record in
the relationship, it automatically deletes all the related
records on the 'Many' side.

Hope this helps!

Howard Brody

 

[John Vinson]

Hi,

Is it good to use option Enforce referential integrity in relationship of
tables? There are also too options cascade update related fields and cascade
delete related fields? Can someone give advise what are pros and cons of
this option. I am newbee, sorry for this easy questions?

Thanks for your answer, Marco

Enforcing relational integrity is *almost* always preferred. If you do
so, then you will be protected from the risk of creating an "orphan"
record in the many side table. If you have a one to many relationship
between (say) Orders and OrderDetails, it would make no sense to have
an OrderDetails record which does not have a corresponding Order;
unless you enforce RI, there's nothing to prevent you from entering
such an orphan. In addition, enforcing RI automatically creates
indexes on the joining fields, making queries run much more
efficiently.

Cascade Deletes can be used WITH CAUTION. If it isn't set, and you
attempt to delete a record in the "one" side of a table, you won't be
allowed to do so if there are related records in the "many" side. If
it is set, then deleting a "one" side record will delete all of the
matching records in the "many" side. This is often appropriate, but
bear in mind that what looks like a simple one-record deletion might
delete hundreds of records from several other tables, with only one
warning and no way to get them back!

Cascade Updates if of very limited value. All it does is propagate any
changes you make to the value of the linking field in the "one" side
table to the linking field in the "many" - for instance if you have a
Text primary key containing "Harvard", and edit that field to read
"Yale", it will change the value in all the related table records. But
since as a rule, primary keys should be stable, it will rarely happen
that you will need to edit the field. If the Primary Key field is an
autonumber, then you CAN'T edit it anyway, so there would be no need
to set Cascade Updates.