MySQL - Defining the Database

A number of commands in SQL are part of the Data Definition Language (DDL). These commands allow you to create, modify or delete a database, create, modify and delete tables and views, define constraints to ensure database integrity, and create, modify or delete indexes (search keys). The main elements of the Data Definition Language are discussed below.

The CREATE DATABASE command

The CREATE DATABASE command is used to create a new database. We will use this command to create a simple database to hold contact details for family and friends called "contacts". The command syntax is as follows:

CREATE DATABASE <database_name>;

To connect to the MySQL server, open a command window and navigate to the directory where MySQL stores its executable files (on my computer, this would be X:\xampp\mysql\bin), and enter the command: mysql -u root. Logging into the database as the root user in this way automatically gives you authorisation to run MySQL commands as the root user. You should see a screen out put something like the illustration below.

Enter the following command to create the "contacts" database:

create database contacts;

If the command has been executed successfully, you should see a screen that looks like the one shown below (don't expect a fanfare - there will only be a message telling you "Query OK, 1 row affected (0.06 sec)", or an error message if the operation fails).

Creating tables

Tables can be added to the database using the CREATE TABLE command. The general syntax for the CREATE TABLE command is shown below.

CREATE TABLE <table_name>
(
  <column_name_1> <data_type>,
  <column_name_2> <data_type>,
  <column_name_3> <data_type>,
  . . . . .
  <column_name_n> <data_type>
);

The data type determines what kind of information can be stored in the column (or field). We will be looking at the various data types allowed in MySQL in another page. For now, we will only really need to use integers or character arrays (text strings) to store the information required for the "contacts" database. We will create a table called "person" that has five columns (contactID, lastName, firstName, address, and city). Before we can actually create a table, however, we need to tell MySQL which database we want to use. We will do this using the following command:

use contacts;

You should see the message "Database changed", as shown below.

We can now create the "person" table using the following command (note that you can actually type the entire command on one line - the form used below simply adds some clarity):

create table person
(
  contactID int,
  lastName varchar(20),
  firstName varchar(20,
  address varchar(60),
  city varchar(20)
);

If the table has been created successfully, you should see the message "Query OK, 0 rows affected (0.13 sec)". The time reported will vary, depending on the speed of your computer. Your screen should now look something like the illustration below.

Once you have created the database and any required tables, you can either make changes to the database structure or (if you are satisfied that the database meets current requirements) exit the MySQL command shell. To exit the command shell, use the quit command (don't forget to add a semicolon after the command).

SQL Constraints

Constraints are used to limit the type of data that can go into a table. They can either be specified when the table is created (using the CREATE TABLE command), or at some later time (using the ALTER TABLE statement). Two of the most commonly used constraints are described below.

• PRIMARY KEY - the PRIMARY KEY constraint identifies one or more columns that will be used to uniquely identify each record in a table. Primary key fields must contain unique values (or combined values, if more than one column is used to define the primary key), and cannot contain NULL values. Every table should have one (and only one) primary key.
• NOT NULL - by default, a column can be empty (i.e. its is said to hold a null value). The NOT NULL constraint, when applied to a column, means that the column must always contain a value, and you can't insert a new record or update a record without providing a value for the column. Using the NOT NULL constraint is useful for ensuring that required data (for example the column lastName in our "person" table, which should always contain a value) is always present in a database record.

Looking at the "person" table (which we admittedly created without giving too much thought to the detailed requirements of our database application) it will become apparent that the table as it stands is somewhat lacking. It does not, for example, have a primary key, Furthermore, all of the fields in the table are currently (by default) able to accept null values. The following SQL command will modify the "person" table so that the "contactID" field becomes the table's primary key:

alter table person add primary key(contactID);

In addition to creating the primary key (which by definition must be both unique and not null), it would probably be a good idea to require the "lastName" field to always have a value (i.e. not be null). The following SQL command adds the constraint to the "person" table that the "lastName" field cannot accept null values:

alter table person modify lastname varchar(20) not null;

In order to confirm that the changes made to the structure of the database or to individual tables have indeed been implemented as expected, it would be nice to be able to view a summary of the information that defines the various database objects (i.e. tables, views, and the database itself). We can determine what tables currently exist within the "contacts" database quite easily using the following SQL command:

show tables;

The output from this command looks something like the illustration below.

To look at the structure of the "person" table, use this command:

desc person;

This command will show the fields in the "person" table, together with their data type, default value, and any constraints that apply to them. A screen shot of the output from using this command is shown below. Note that the "contactID" field is now the primary key for the table, and that the "lastName" field may not be null.

Creating an auto-increment field

When the primary key of a table is an integer, it would be nice if we could have the numeric value used for each new record created automatically. This would make life a lot easier, as we would not have to worry about duplicated key values (which would cause database errors). The auto-increment facility allows a unique numeric value to be generated for the designated field each time a new record is inserted into a table, and is particularly useful for generating unique primary key values. The SQL command shown below defines the "contactID" column (the primary key) in the "person" table as an auto-increment field. Note that by default, the value of this field, for the first record created, will be 1. The value of the field for each record subsequently created will be one greater than that of the last record created.

alter table person modify contactID int auto_increment;

If you now display the "person" table once more, you will see output something like that shown below. Note that the "Extra" column now displays the words "auto_increment" opposite the "contactID" field name.

Note that, had we wished, we could have created the table in the first instance with its current characteristics using the following command:

create table person
(
  contactID int not null auto_increment,
  lastName varchar(20) not null,
  firstName varchar(20),
  address varchar(60),
  city varchar(20),
  primary key (contactID)
);

Some other useful SQL commands inlude:

• DROP DATABASE - used to delete a database
• DROP TABLE - used to delete a table
• TRUNCATE TABLE - deletes all data in a table but not the table itself