Managing Tables

Now let's look at how database tables are defined, altered, and dropped.

Creating a New Table

Use the CREATE TABLE command to create a new database table. A table definition consists of a number of columns and a set of table options. Each column in the table definition is given a data type and can also be given a constraint.

A column name can be up to 64 characters long and can contain any character. You must enclose the column name in quotes (") or backticks (`) if it contains a space. A column name cannot be one of the keywords listed in Appendix F.

The data type of a column determines what values it can hold and the maximum size of a stored value. For instance, a column defined as VARCHAR(6) can contain text data up to 6 characters in length. You will learn more about the MySQL data types in Lesson 16, "Designing Your Database.".

A column constraint is used to impose a restriction on the values stored in that column. For example, a column defined as UNIQUE may not contain the same value more than once. If a constraint is violated when you attempt an INSERT or UPDATE on the table, MySQL will return an error and your SQL statement will fail.

Let's look at an example from the sample database. The following statement creates the products table:

 CREATE TABLE products (    product_code  VARCHAR(10)    PRIMARY KEY,    name          VARCHAR(40)    NOT NULL UNIQUE,    weight        DECIMAL(6,2)   NOT NULL,    price         DECIMAL(6,2)   NOT NULL ); 

Although the formatting shown in this example is not necessary, it helps to show the different elements of the column specification. You give the column a name, then its data type, and then any constraints that are required.

CREATE TABLE

The CREATE TABLE statement has many different options, and there is not space in this chapter to cover them all. Please refer to the online MySQL manual at http://dev.mysql.com/doc/refman/5.0/en/create-table.html for the full CREATE TABLE syntax.

In this table, the product_code column is defined as a 10-character string and is the PRIMARY KEY for the table. Each table may have only one PRIMARY KEY because this is a unique identifier for each row of the table. You will learn more about keys in Lesson 17, "Keys and Indexes.

Primary Keys

A PRIMARY KEY field also has the UNIQUE and NOT NULL constraints, even if you do not specify them in the CREATE TABLE statement.

The other three columns are given the NOT NULL constraint. With this constraint, any attempt to insert data rows where any of these columns are left empty will fail.

NULLs

NULL means no value. It is not the same as 0 or an empty string. Therefore, it is possible for the name column to contain '', and for the weight and price columns to contain 0.00.

The name column is a text field with a maximum length of 40 characters; it is specified as UNIQUE. This is done to ensure that two products in the database will never have exactly the same name.

The weight and price columns are defined as DECIMAL(6,2). This is a 6-digit number with 2 of those digits appearing after the decimal point. Therefore, the maximum value is 9999.99.

Data Types

Refer to Appendix D, "MySQL Data Type Reference," which can be found on the book's website at www.samspublishing.com for a complete list of data types that can be used for a column definition.

Specifying Default Values

You can give each column a default value using the DEFAULT keyword. Then whenever a row is inserted without explicitly giving a value for that column, the default value will be used.

This statement creates a simple table in which the value of the sex column will be Female unless you specify otherwise.

 mysql> CREATE TABLE people (     ->   name VARCHAR(40),     ->   sex VARCHAR(6) DEFAULT 'Female'     -> ); Query OK, 0 rows affected (0.00 sec) 

Then insert a row that gives a value for only the name column, as follows. You will learn how to form an INSERT statement in Lesson 15, "Working with Data."

 mysql> INSERT INTO people (name)     -> VALUES ('Jane Doe'); Query OK, 1 row affected (0.00 sec) 

Retrieving the data from this table shows that the default value was used:

 mysql> SELECT * FROM people; +----------+--------+ | name     | sex    | +----------+--------+ | Jane Doe | Female | +----------+--------+ 1 row in set (0.00 sec) 

Default Values

If there is a default value for a column, it will be displayed in the Default column of the output from the DESCRIBE command.

Autoincrementing Columns

It is possible to specify a column in a table as an autoincrementing column. Such a column will contain a number that is assigned automatically each time a new row is inserted. To do this, use the AUTO_INCREMENT attribute on a numeric column.

The orders table in the sample database uses AUTO_INCREMENT on the order_id column to assign a new order number each time an order is placed. The table definition is as follows:

 CREATE TABLE orders (   order_id      INT            PRIMARY KEY AUTO_INCREMENT,   customer_code VARCHAR(10)    NOT NULL,   order_date    DATE           NOT NULL ); 

If you insert a new record into this table without giving a value for the order_id column, its value will be assigned as the next unused number in sequence.

 mysql> INSERT INTO orders (customer_code, order_date)     -> VALUES ('SCICORP','2006-03-20'); Query OK, 1 row affected (0.00 sec) 

The following query selects the two most recently added records from the orders table. As you can see, the newly inserted record has an order_id that is one higher than the previous record.

 mysql> SELECT * FROM orders     -> ORDER BY order_id DESC     -> LIMIT 2; +----------+---------------+------------+ | order_id | customer_code | order_date | +----------+---------------+------------+ |        8 | SCICORP       | 2006-03-20 | |        7 | MUSGRP        | 2006-02-02 | +----------+---------------+------------+ 2 rows in set (0.00 sec) 

Autoincrement Columns

If a column has the AUTO_INCREMENT property, this will be shown in the ExTRas column of the output from the DESCRIBE command.