Some Systems Support Cascading Options

All these restraints might seem to tie your hands a bit. No rule is absolute, and there may be times when you need to add, delete, and change without interference. Some RDBMSs offer cascading options to help with these situations.

Warning 

We strongly recommend that you not permanently enable cascading options. Keep these options disabled until they are specifically needed. Changing and especially deleting data is a serious undertaking, and you definitely don't want to give this level of decision-making to users who are unfamiliar with all the repercussions of such serious actions. Deleting data can have far-reaching consequences and requires careful scrutiny and discretion.

If you attempt to change a primary key value when foreign key values exist, you'll receive a referential integrity error message. Systems that support cascading options will allow such a change and even help you fully implement it by making the same change to all the matching foreign key values. For instance, if you change a particular publisher's primary key value in the publishers table with the cascading update option enabled, your system automatically updates any matching foreign key values in the books table.

Note 

Your RDBMS may or may not allow you to edit its system-generated, auto-numbering values used as surrogate key values. If you can't edit these values, cascading updates are a moot point. The option will still exist; you just won't be able to use it.

The other cascading option allows you to delete primary key values when there are matching foreign key values in a related table. Enabling a cascading delete option allows your system to delete the matching foreign key values. The system will delete the primary key, but it will also delete any related records (foreign key values). This result may or may not be what you intended, so be careful when using this option.

Applying cascading options to our book database is a limited venture because we've used surrogate keys. If your RDBMS won't allow you to change a surrogate value that's generated by the system itself, you can't change your primary key value. That means any cascading update option is moot. But let's assume you used the ISBN as your primary key instead of a surrogate key. Furthermore, let's suppose you've found an incorrectly entered ISBN. With the cascading update feature enabled, you can change an ISBN number, and your RDBMS will update any foreign key values (in Books- Categoriesmmlink, BooksTypesmmlink, BooksAuthorsmmlink, and BooksContributorsmmlink) accordingly.

You can delete surrogate primary keys, so you might need a cascading delete option. Earlier, we discussed the possibility of using referential integrity to prevent you from deleting a publisher from the publishers table if a record in the books table refers to that publisher. (Remember, without referential integrity you can delete any record at any time.) Using a cascading delete option, you could delete a publisher if a related book record exists, but your RDBMS will also delete the book record. That may or may not be what you want, so be careful when using this option.