ON DELETE CASCADE ) ENGINE = InnoDB;

code CHAR(3) NOT NULL, name CHAR(52) NOT NULL, PRIMARY KEY (code) ) ENGINE = InnoDB;

CREATE TABLE city_child (

id INT NOT NULL AUTO_INCREMENT, name CHAR(35) NOT NULL,

country_code CHAR(3) NOT NULL, PRIMARY KEY (ID),

INDEX (country_code), FOREIGN KEY (country_code)

REFERENCES country_parent (code) ON UPDATE CASCADE

ON DELETE CASCADE ) ENGINE = InnoDB;

In these two tables, the column and PRIMARY KEY definitions are the same as in the original Country and City tables. The parts of the syntax that differ from the original tables are the ENGINE table option, which specifies the InnoDB storage engine, and the INDEX and FOREIGN KEY definitions for the CountryCode column in the city_child table.

Why InnoDB?

It's necessary to use InnoDB because that is the only storage engine that supports foreign keys. (You can specify a FOREIGN KEY clause for other table types, but it would simply be ignored.)

96

Foreign Key Clause

The FOREIGN KEY clause has several parts:

• It names the column in the referring table (country_code).

• It names the code column in the country_parent table as the referenced column. This column is the “foreign” key.

• It specifies what actions to take if records are modified in the referenced table. The foreign key definition shown specifies the CASCADE action for both UPDATE and DELETE operations. This means that changes in the parent table are cascaded down to the child table. If a code value is changed in the country_parent table, InnoDB changes any corresponding city_child records with that value in the country_code column to match. If a country_parent record is deleted, InnoDB also deletes any city_child records with the same country code. (InnoDB supports actions other than CASCADE, but they are not covered here.

Instead of CASCADE a SET NULL or RESTRICT can be specified.

In a foreign key relationship, the referring column and the referenced column should have the same data type, and both must be indexed. (If the referring column has no index, InnoDB creates an index on it automatically.)

The ON UPDATE and ON DELETE parts are optional. If they are ommitted, InnoDB simply disallows attempts to update or delete code values in the country_parent table if there are city_child records that refer to them.

97

Lab 6-B

In this exercise you will interact with the world database and utilize some of the commands that were presented in this chapter.

ACTION (You Do) COMPUTER RESPONSE / Comment

1. Log in to the mysql client (if not already). It is easier and more advantageous to design the database components in the MySQL client versus trying to complete the tasks through the PHP interface.

2. In the mysql client, type the following to use the world database:

USE world;

If the world database is not available (or not present), please refer to Lab 6-A to create and load the database with the components that will be needed to complete the remainder of this lab.

3. Show the create statement for the City table in the world database using the vertical output format option by typing the following in the mysql client:

SHOW CREATE TABLE City\G

A representation of the City table creation statement is shown.

4. Switch to the test database by typing the following command in the mysql client:

USE test;

Sets the current database to use the test database.

5. Create a table using the mysql client called 'db' which will use the storage engine InnoDB and add a comment of 'Create Table lab':

CREATE TABLE db (name char (10)) ENGINE InnoDB COMMENT 'Create Table lab';

Returns an OK message if the table is created successfully.

6. Show the create statement for the db table in the test database using the vertical output format option by typing the following in the mysql client:

SHOW CREATE TABLE db\G

A representation of the db table creation statement is shown. Confirm that the table property settings are correct.

ACTION (You Do) COMPUTER RESPONSE / Comment 7. In the test database, create a new MyISAM table

called altering with one column called foo which is data type text by typing the following in the mysql client:

CREATE TABLE altering (foo text) ENGINE=MyISAM;

Returns an OK message if the table is created successfully.

8. Use the DESCRIBE command to show the current structure of the altering table by typing the following in the mysql client:

DESCRIBE altering;

Confirm that the foo column has been created with the text data type.

9. Add a column to the altering table called id, with a data type of tinyint at a width of 4 digits that will not accept null values by typing the following in the mysql client:

ALTER TABLE altering ADD id TINYINT(4) NOT NULL;

Returns an OK message if the table was altered successfully.

10. Use the DESCRIBE command to show the structure of the altering table now by typing the following into the mysql client:

DESCRIBE altering;

Confirm that the id column has been added with the proper attributes.

11. Remove the id column from the altering table by typing the following in the mysql client:

ALTER TABLE altering DROP id;

Returns an OK message if the table was altered successfully.

12. Use the DESCRIBE command to show the current structure of the altering table by typing the following in the mysql client:

DESCRIBE altering;

Confirm that the id column has been removed.

13. Modify the foo column from the altering table;

change the data type to char with a width of 8 and no longer allow it to accept null values by typing the following in the mysql client:

ALTER TABLE altering MODIFY foo char(8) NOT NULL;

Returns an OK message if the table was altered successfully.

14. Use the DESCRIBE command to show the current structure of the altering table by typing the following in the mysql client:

DESCRIBE altering;

Confirm that the Id column has been modified with the proper values.

15. Rename the altering table to foo typing the following in the mysql client:

RENAME TABLE altering TO foo;

Returns OK if the table was renamed successfully

ACTION (You Do) COMPUTER RESPONSE / Comment 16. Verify the altering table was changed to foo by

typing the following in the mysql client:

SHOW TABLES;

Confirm that the altering table was renamed to the foo table.

17. Remove the db table and show the list of tables again by typing the following commands in the mysql client:

DROP TABLE db;

SHOW TABLES;

The first SQL statement will return an OK message if the table was deleted. The second SQL statement will list the tables currently in the test database. Verify that the db table has been removed.

18. Remove the foo table and show the list of tables again by typing the following commands in the mysql client:

DROP TABLE foo;

SHOW TABLES;

The first SQL statement will return an OK message if the table was deleted. The second SQL statement will list the tables currently in the test database. Verify that the foo table has been removed.

19. Change the database to world by typing the following in the mysql client:

USE world;

Sets the current database to use the world database.

20. Create the two tables, using the mysql client, listed below (country_parent and city_child):

mysql> CREATE TABLE country_parent -> (code CHAR(3) NOT NULL, -> name CHAR(52) NOT NULL, -> PRIMARY KEY (code) -> ) ENGINE = InnoDB;

…and…

mysql> CREATE TABLE city_child -> (id INT NOT NULL