CIS 336 STUDY Extraordinary Success /cis336study.com CIS 336 STUDY Extraordinary Success /cis336study.c | Página 67

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 round-the-clock, high availability
of a database is required, other approaches may be
required. Addressing this problem in a high-availability,