CIS 336 STUDY Future Starts Here/cis336study.com CIS 336 STUDY Future Starts Here/cis336study.com | Page 69
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 during peak operating hours in a production operation( as a student in a lab exercise, working on your own database, you may do this at any time) but ideally in hours during which the business does not normally operate. In cases where
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 during peak operating hours in a production operation( as a student in a lab exercise, working on your own database, you may do this at any time) but ideally in hours during which the business does not normally operate. In cases where