CIS 336 help Making Decisions/uophelp.com CIS 336 help Making Decisions/uophelp.com | Page 55

1. Use an ALTER TABLE statement to update the customers table so that the Primary
Key field is an auto-increment field, then create TWO insert statements to test proper
operation, using your own first and last name for one (and a name of your choice for the
second one), and any data you care to imagine for the remaining fields.
IMPORTANT NOTE: When using a LOCAL copy of MySQL, if you attempt to simply
issue the ALTER TABLE command you have composed by itself, you should receive an
error similar to the following (try it for yourself!).
ERROR 1833: Cannot change column ‗customer_id‘: used in a foreign key constraint
‗orders_fk_customers‘ of table ‗om.orders‘
(Note – EDUPE will not give this error message, however you should still follow the
CORRECT procedure as discussed here to complete this problem).
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 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-