CIS 336 STUDY Career Path Begins/cis336study.com CIS 336 STUDY Career Path Begins/cis336study.com | Page 69

The reason for this is that you are attempting to alter data in
one column that has a defined PK:FK relationship to a field
in another table. Referential Integrity rules prevent this. So,
how do you resolve such a problem?
One approach to solving this dilemma is to turn off the
foreign key checks that implement referential integrity rules.
However, the danger here is that other users and processes
operating on the database while these constraints are
suspended could create or modify data in a way that
compromises integrity. We can solve this second problem by
preventing other users and processes from altering the data
in the table in which we are working until we have turned
the foreign key checks back on. We therefore need to
construct a script that does the following.
a) Locks the customer table – lock table customers write;
b) Turns off FK checks – set foreign_key_checks = 0;
c) Alters the table to add the auto_increment feature to the
PK field
d) Turns FK checks back on – set foreign_key_checks = 1;
e) Unlocks the customer table – unlock tables;
It is VERY important to consider that altering tables can
require a bit of time for very large tables, and that while the
table is locked, other users and processes cannot operate.
Consequently, this kind of modification should not be done