Creating and Dropping Tables (MySQL & mSQL)

6.2. Creating and Dropping Tables

With MySQL or mSQL successfully installed, you should now be ready to create your first table. The table, a structured container of data, is the basic concept in a relational database. Before you can begin adding data to a table, you must define the table's structure. Consider the following layout:

+---------------------------------+
|             people              |
+-------------+-------------------+
| name        | char(10) not null |
| address     | text(100)         |
| id          | int               |
+-------------+-------------------+

Not only does the table contain the names of the columns, but it also contains the types of each field as well as any additional information the fields may have. A field's datatype specified what kind of data the field can hold. SQL datatypes are similar to datatypes in other programming languages. The full SQL standard allows for a large range of datatypes. MySQL implements most of them, while mSQL contains only a few of the most useful types.

The general syntax for table creation is:

CREATE TABLE table_name (column_name1 type [modifiers]
                      [, column_name2 type [modifiers]]
)

NOTE

What constitutes a valid identifier -- a name for a table or column -- varies from DBMS to DBMS. mSQL provides close to the bare minimum support for names. It accepts any sequence of International Standards Organization (ISO) 8859-1 (Latin 1) letters, numbers, or `_' up to 20 characters as a valid identifier. An identifier must begin with a letter. Good database design only encounters problems with the ISO 8859-1 restriction. In other words, for good portable SQL, you do not want to have names that start with anything other than a valid letter. MySQL lets you go further. It allows up to 64 characters in an identifier, supports the character `$' in identifiers, and lets identifiers start with a valid number. More important, however, MySQL considers any valid letter for your local character set to be a valid letter for identifiers.

A column is the individual unit of data within a table. A table may have any number of columns, but large tables may be inefficient. This is where good database design, discussed in Chapter 2, "Database Design", becomes an important skill. By creating properly normalized tables, you can "join" tables to perform a single search from data housed in more than one table. We discuss the mechanics of a join later in the chapter.

Like most things in life, destruction is much easier than creation. The command to drop a table from the database is:

DROP TABLE table_name

This command will completely remove all traces of that table from the database. MySQL will remove all data within the destroyed table from existence. If you have no backups of the table, you absolutely cannot recover from this action. The moral of this story is to always keep backups and be very careful about dropping tables. You will thank yourself for it some day.

With MySQL, you can specify more than one table to delete by separating the table names with commas. For example, DROP TABLEpeople,animals,plants would delete the three named tables. You can also use the IF EXISTS modifier under MySQL to avoid an error should the table not exist when you try to drop it. This modifier is useful for huge scripts designed to create a database and all its tables. Before the create, you do a DROP TABLEtable_nameIF EXISTS.