Working with Database Structures - Learning MySQL - page 212

+---------+
| counter |
+---------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
+---------+
6 rows in set (0.00 sec)
Instead of relying on MySQL to handle incrementing fields as you hope, you can man-
age the process in program code that you write to interact with the database. We don't
use an auto-incrementing field in the final music database specification, described fully
in the next section. However, we do use one in our wedding gift registry in Chapter 15.
The Sample Music Database
We've used the music database extensively in this and the previous chapter, so you're
already familiar with its structure. This section explains the steps we took to express
our sample music database as SQL statements for loading into MySQL. It also lists the
complete SQL statements used to create the structures, which you'll find a useful ref-
erence for discussions in later chapters.
Let's begin by discussing how we structured the file that contains the SQL statements.
You can download the file music.sql from the topic's web site. We created the table
using the monitor, and created the file from the output of one of MySQL's commands
for dumping SQL, and then edited it for readability. You'll find more about how to
dump SQL statements to a file in Chapter 10.
The music.sql file is structured as follows:
1. Drop the database if it exists, and then create it.
2. Use the database.
3. Create the tables.
4. Insert the data.
This structure allows you to reload the database—using the SOURCE command discussed
in Chapter 3—at any time without having to worry about whether the database, tables,
or data exist. Loading the file just wipes the database and starts again. Of course, in a
production environment, always ensure your backups are reasonably up-to-date before
commencing a restore operation that involves dropping tables or deleting existing data.
The first three lines of the file carry out the first two steps:
DROP DATABASE IF EXISTS music;
CREATE DATABASE music;
USE music;