CIS 336 All Assignments CIS 336 All Assignments | Page 53

• Lab Report (Answer Sheet) containing both the student-created SQL command(s) for each
exercise, and the output showing the results obtained. Be sure your name is on the file.
LAB STEPS: Complete each of the exercises below.
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;