Creating Databases and Tables Using SQL Commands

In the previous chapter (Creating New Databases and Tables with MySQL Administrator) we created a new database and table using a graphical tool that did not rquire us to use any SQL commands. In this chapter we will learn how to create new databases and tables by issuing SQL commands using the mysql client.

This chapter assumes that the mysql tool is running and connected to the the MySQL database server. If this is not the case and you are unsure as to how to achieve this refer to The mysql Command-Line Tool chapter. Alternatively, the SQL commands outlined in that chapter may be executed in the MySQL Query Browser.

Contents

Creating a New MySQL Database

A new database is created using the CREATE DATABASE SQL statement followed by the name of the database to be created. The CREATE SCHEMA statement may also be used for this purpose. For example, to create a new database called MySampleDB the following statement would be entered at the mysql> prompt:

CREATE DATABASE MySampleDB;

If successful, the command will generate output similar to the following:

Query OK, 1 row affected (0.00 sec)

If the database name specified conflicts with an existing database MySQL will display and error message reporting this fact:

ERROR 1007 (HY000): Can't create database 'MySampleDB'; database exists

In this situation, a different database name should be selected, or the IF NOT EXISTS option, which only creates the database if it does not already exists, and does not report an error if it does:

CREATE DATABASE IF NOT EXISTS MySampleDB;

Creating Tables with SQL

New tables are added to an existing database using the SQL CREATE TABLE statement. The CREATE TABLE statement is followed by the name of the table to be created followed by a comma separated list of the names and definitions of each table column:

CREATE TABLE table_name ( column_name definitions, table_name definitions ..., PRIMARY KEY=(column_name) ) ENGINE=engine_type;

The definitions field for each column define information such as the data type of the column, whether the column can be NULL and whether the column value auto increments. The CREATE TABLE statemnt also allows a column (or group of columns) to be specified as the primary key (see Database Basics for a description of primary keys).

Before a table can be created a database must first be selected so that MySQL knows where to create the table. This is achieved using the USE SQL statement:

USE MySampleDB;

Having selected a database, the following example creates a table consisting of three columns named customer_id, customer_name and customer_address. The customer_id and customer_name columns must contain values (i.e NOT NULL). The customer_id holds an integer value whichj will auto increment as new rows are added and the others hold character strings up to 20 characters in length. The primary key is defined to be the customer_id

CREATE TABLE customer 
( 
customer_id int NOT NULL AUTO_INCREMENT, 
customer_name char(20) NOT NULL, 
customer_address char(20) NULL, 
PRIMARY KEY (customer_id) 
) ENGINE=InnoDB;

Understanding NULL and NOT NULL Values

When a column is specified to be NULL then a row can be added to a database when there is no value assigned to that column. Conversely, if a column is defined as NOT NULL then it must have a value assigned to it before the row can be added to table.

Primary Keys

As covered in Database Basics a primary key is a column used to identify individual records in a table. The value of a primary key column must be unique within the context of the table in which it exists, or if multiple columns are combined to constitute a primary key, the combination of key values must be unique to each row.

The primary key is defined using the PRIMARY KEY statement during table creation. If multiple columns are being used they are comma separated:

PRIMARY KEY (column_name, column_name ... )

In the following example, a table is created using two columns as the primary key:

CREATE TABLE product 
( 
  prod_code INT NOT NULL AUTO_INCREMENT, 
  prod_name char(30) NOT NULL, 
  prod_desc char(60) NULL, 
  PRIMARY KEY (prod_code, prod_name) 
) ENGINE=InnoDB;

AUTO_INCREMENT

AUTO_INCREMENT is one of the simplest, yet most useful column definitions in the SQL language. Essentially, when a column is defined using AUTO_INCREMENT the value of the column is increased automatically each time a new row is added to a table. This is especially useful when using a column as a primary key. By using AUTO_INCREMENT it is not necessary to write SQL statements to calculate a new unique id for each row. This is all handled by the MySQL server when the row is added.

There are two rules that must be obeyed when using AUTO_INCREMENT. Firstly, only one column per table may be assigned AUTO_INCREMENT status. Secondly, the AUTO_INCREMENT column must be indexed (e.g. by declaring it as the primary key).

It is possible to override the AUTO_INCREMENT value of a column simply by specifying a value when executing an INSERT statement. As long as the specified value to unique the value provided will be used in the new row and subsequent increments will start at the newly inserted value.

MySQL may be interrogated to retrieve the most recent increment value using the last_insert_id() function as follows:

SELECT last_insert_value();