Creating Tables | MySQL Tutorial

To create the tables in the employee database, we use the CREATE TABLE SQL statement. The usual form of this statement is

 create table  tablename  (  table definition  ) [type=  table_type  ];

That is, we begin with the words create table , followed by the name we would like the table to have, followed by a set of column definitions. At the end of the statement, we can optionally specify the storage engine type we would like to use.

Table Creation Example

We will look at an example of table creation to illustrate this point. Listing 4.1 shows a set of SQL statements that can be used to create the employee database. You can type these by hand or download the file (along with some sample data for the database) from this book's Web site.

Listing 4.1 SQL to Create the Employee Database

 drop database if exists employee; create database employee; use employee; create table department (   departmentID int not null auto_increment primary key,   name varchar(30) ) type=InnoDB; create table employee (   employeeID int not null auto_increment primary key,   name varchar(80),   job varchar(30),   departmentID int not null references department(departmentID) ) type=InnoDB; create table employeeSkills (   employeeID int not null references employee(employeeID),   skill varchar(15) not null,   primary key (employeeID, skill) ) type=InnoDB; create table client (   clientID int not null auto_increment primary key,   name varchar(40),   address varchar(100),   contactPerson varchar(80),   contactNumber char(12) ) type=InnoDB; create table assignment (   clientID int not null references client(clientID),   employeeID int not null references employee(employeeID),   workdate date not null,   hours float,   primary key (clientID, employeeID, workdate) ) type=InnoDB;

Let's go though the SQL statements in this file one by one.

We begin with

 drop database if exists employee;

This statement checks whether an employee database already exists and deletes it if it does, cleaning the slate if you like. This is not strictly necessary and could even be dangerous, but we do it here to make sure that this database creation script should work, even if you have already been experimenting with an employee database.

Note that if you use MySQL in a hosted environment, your hosting company can disable the drop database command. In this case, simply eliminate that line of the script (but make sure that there isn't a database called employee).

We then create the database and select it for use, as we have seen already:

 create database employee; use employee;

Now, we begin creating tables inside this database. We begin by creating the department table, as follows :

 create table department (   departmentID int not null auto_increment primary key,   name varchar(20) ) type=InnoDB;

This table has two columns, departmentID, which is the primary key, and the department name. To declare the columns in the table, we give a comma-separated list of the column declarations enclosed in parentheses. Note that attributes of a column do not need to be comma separated ”only the columns themselves do.

This is our first multiline SQL statement. Whitespace is not important in SQL, so we can lay out our queries in any way we like. Typically, with CREATE statements, you tend to put one item on each line to increase readability. The SQL interpreter will not try to interpret your statement until you have typed the final semicolon (;) and pressed Enter. (You can also choose to end your statements with \g , but the semicolon is far more commonly used.)

In this table, we are declaring two columns. Each column declaration begins with the name of the column, which is followed by information about the type of that column. Look at the second column first in this example because it's a little easier to understand. The declaration

 name varchar(20)

tells us that the column is called name and that its type is varchar(20) . The varchar type is a variable-length string, in this case up to 20 characters . We could also have used char , which is a fixed-length string. Choosing varchar or char does not make a difference in terms of using the data, just in how the data is stored in memory. A varchar(20) takes up only as much room as the number of characters stored in it, whereas a char(20) is always 20 characters wide, regardless of what is stored in it. We will talk about the relative advantages and disadvantages of this later in this chapter in the section "Column Types."

Now, look back at the first column definition. It looks like this:

 departmentID int not null auto_increment primary key,

The name of this column is departmentID , and it is of type int (integer). This is a unique number that we will use to identify each department in the company.

After the type, there is some further information about the column.

First, we have specified that this column is not null ”in other words, for every row in this table, this column must have a value in it.

Secondly, we have specified that this column is an auto_increment column. This is a nice feature in MySQL. When we insert data into this table, if we do not specify a department number, MySQL will allocate a unique number that will be the next number in the auto_increment sequence. This makes life easy for us.

Finally, we have specified that this column is to be the primary key for this table. If the primary key consists of a single column, we can specify it like this. For multicolumn primary keys, we must use a different approach, which we will look at in a moment.

That's the table definition. Now, look at the very end of the SQL statement. After the end parenthesis, you will see the following line:

 type=InnoDB

This specifies that this table should use the InnoDB storage engine. If you look through the table definitions, you will see that in this case, we have declared all the tables as InnoDB tables.

What does this mean? MySQL supports various storage engines, and we will discuss them all in detail in Chapter 9, "Understanding MySQL's Table Types." The default type is MyISAM. If we want to use MyISAM tables, we don't need to add the type clause at the end of the create database statement.

In this case, we are using InnoDB because we are going to work through some examples using foreign keys. The InnoDB storage engine supports foreign keys and transactions, whereas the MyISAM table type does not. The MyISAM table type is often faster than the InnoDB table type. We need to decide what type is best for each table.

We could make the tables of different types having, for example, some InnoDB tables and some MyISAM tables (and perhaps some of the other types, if needed), but we are keeping it simple in this example and using InnoDB for all our tables.

Look now at the second create table statement:

 create table employee (   employeeID int not null auto_increment primary key,   name varchar(80),   job varchar(15),   departmentID int not null references department(departmentID) ) type=InnoDB;

There is only one new piece of syntax in this statement. The last column in the employee table is the id of the department for which the employees work. This is a foreign key. We declare this in the table definition by adding the references clause as follows:

 departmentID int not null references department(departmentID)

This tells us that the departmentID in the employee table should be referenced back to the departmentID column in the department table.

Note that we can use this foreign key syntax because the employee table is an InnoDB table. When we use MyISAM tables, we cannot use foreign keys. Foreign keys in MyISAM tables are planned for a future version of MySQL, probably version 5.1 according to the development schedule.

Now, look at the third create table statement:

 create table employeeSkills (   employeeID int not null references employee(employeeID),   skill varchar(15) not null,   primary key (employeeID, skill) ) type=InnoDB;

Again, in this table, we have a foreign key, in this case the employeeID . The interesting thing about this table definition is that this table has a two-column primary key. You can see that we declare the two columns in the table, employeeID and skill , and then declare the primary key separately with the following line:

 primary key (employeeID, skill)

The other table definitions don't contain any new syntax, so we won't go through them in detail. You will note that we have used a couple of other data types: in the assignment table, the number of hours is a float , or floating-point number, and the workdate is of type date . We will revisit the column types in more detail later in this chapter.

You can check whether the tables in your database have been set up correctly using the command

 show tables;

You should get the following output:

 +--------------------+  Tables_in_employee  +--------------------+  assignment           client               department           employee             employeeSkills      +--------------------+

You can get more information about the structure of each table by using the describe command, for example,

 describe department;

This should give you something like the following output:

 +---------------+-------------+-------------------+------+-----+---------+----------------+  Field          Type         Collation          Null  Key  Default  Extra           +---------------+-------------+-------------------+------+-----+---------+----------------+  departmentID   int(11)      binary                   PRI  NULL     auto_increment   name           varchar(20)  latin1_swedish_ci  YES        NULL          ______     +---------------+-------------+-------------------+------+-----+---------+----------------+

You might want to check the other tables at this point.

CREATE TABLE Statement

Now that we've looked at an example, let's go over the complete syntax for the CREATE TABLE statement. The MySQL manual tells us that the general form of this statement is as follows:

 CREATE [TEMPORARY] TABLE [IF NOT EXISTS]  tbl_name  [(  create_definition  ,...)] [  table_options  ] [  select_statement  ] or CREATE [TEMPORARY] TABLE [IF NOT EXISTS]  tbl_name  LIKE  old_table_name  ; create_definition:  col_name type  [NOT NULL  NULL] [DEFAULT  default_value  ] [AUTO_INCREMENT]             [PRIMARY KEY] [  reference_definition  ]   or    PRIMARY KEY (  index_col_name  ,...)   or    KEY [  index_name  ] (  index_col_name  ,...)   or    INDEX [  index_name  ] (  index_col_name  ,...)   or    UNIQUE [INDEX] [  index_name  ] (  index_col_name  ,...)   or    FULLTEXT [INDEX] [  index_name  ] (  index_col_name  ,...)   or    [CONSTRAINT  symbol  ] FOREIGN KEY [  index_name  ] (  index_col_name  ,...)             [  reference_definition  ]   or    CHECK (  expr  )

Let's go through the various options we can see in this general form.

The TEMPORARY keyword is used to create a table that will be visible only in your current database session, and that will be automatically deleted when your connection is closed.

We can use the IF NOT EXISTS clause, logically enough, to create a table only if there is not already a table with this table name.

We can use the LIKE old_table_name clause to create a new table with the same schema as old_table_name .

Inside the parentheses of the CREATE TABLE statement, we declare the columns that we want, their types, and any other information about the structure of the table. The simplest column definition is a column name followed by a column type. In the next section of this chapter, we will look at valid column types.

Other options we can add to each column declaration include the following:

We can declare each column as NOT NULL or NULL , meaning either that the column cannot contain a NULL ( NOT NULL ) or that it may contain NULL s ( NULL ). The default is that columns may contain NULL s.
We can declare a default value for a column using the DEFAULT keyword followed by the default value we want.
We can use the AUTO_INCREMENT keyword, as we did in the previous example, to generate a sequence number. The value automatically generated will be one greater than the current largest value in the table. The first row inserted will have the sequence number 1. You can have only one AUTO_INCREMENT column per table, and it must be indexed. You will note that in the previous examples, we did not manually create any indexes; however, some were automatically created for us. Indexes are automatically created for columns that are declared as PRIMARY KEY , which all our AUTO_INCREMENT columns were in this example.
We can declare that this particular column is the PRIMARY KEY for the table.
We can specify that this particular column is a foreign key using the REFERENCES clause, as we did in the example.

As well as declaring column names and types, we can declare some other column information in this part of the CREATE TABLE statement:

We can specify a multicolumn PRIMARY KEY , as we did in the example, by specifying PRIMARY KEY followed by the names of the columns that make up the key. We can actually also declare a single column primary key this way. A PRIMARY KEY column is a unique, indexed column that cannot contain nulls.
INDEX and KEY are synonyms which mean that the specified column(s) will be indexed. Note that these columns do not have to contain unique values in MySQL.
UNIQUE can be used to specify that a particular column must contain unique values. UNIQUE columns will also be indexed.
FULLTEXT is used to create full-text indexes on a TEXT , CHAR , or VARCHAR column type. You can use full-text indexes only with MyISAM tables. There is an example in Chapter 9.
The FOREIGN KEY clause allows us to declare foreign keys in the same two ways that we can declare primary keys.

After the closing parenthesis, we can specify some table options for this table. The one we have looked at so far is the table type. We will discuss the table types in detail in Chapter 9. If you do not specify a type, the tables will default to being MyISAM tables. Just briefly , these are the possible values for the table type:

MyISAM, the default, is very fast and supports full-text indexing. It is a replacement for the previous standard ISAM type.
ISAM is an older table type. It is similar to MyISAM but with fewer features, so you should always use MyISAM instead.
InnoDB is the ACID-compliant storage engine that supports transactions, foreign keys, and row-level locking.
BDB (Berkeley DB) is a storage engine that supports transactions and page-level locking.
HEAP tables are stored completely in memory and are never written to disk, so they are very fast, but limited in size and are unrecoverable in the event of failure.
MERGE tables allow you to combine a set of MyISAM tables with the same structure so that they can be queried as if they were one table. This can be used to get around operating-system restrictions on the maximum file ”and therefore table ”size.

We can also specify some other options for the table. These are not required and are mostly for optimization. We will discuss the use of some of these options in Chapter 18, "Optimizing Your Database." The options are as listed here:

AUTO_INCREMENT = # This option allows you to set the initial auto_increment value to something other than 1 .
AVG_ROW_LENGTH = # This option allows you to estimate what you think will be the average row length in order to aid the storage engine.
CHECKSUM = 1 This option allows you to turn on checksum calculation for the rows in the table that may help you find the problem if the table becomes corrupt. Set it to 1 to turn it on. Off is the default, and this option works only with MyISAM tables.
COMMENT = "string" This option stores a comment about this table.
MAX_ROWS = # This option sets the maximum number of rows that will be stored in this table.
MIN_ROWS = # This option sets the minimum number of rows that will be stored in this table.
PACK_KEYS = {0 1 DEFAULT} By default, MySQL packs (compresses) strings in keys ”that is CHAR s, VARCHAR s, and TEXT . If you set this value to 1 , all keys will be packed; if you set this value to , then none will be packed.
PASSWORD = "string" This option does nothing in the standard version of MySQL.
DELAY_KEY_WRITE = {0 1} This option allows you to delay key updates until after the table is closed. This option works only on MyISAM tables.
ROW_FORMAT= {default dynamic fixed compressed } This option allows you to specify the storage format for rows. This option works only with MyISAM tables.
RAID_TYPE= {1 STRIPED RAID0 } RAID_CHUNKS=# RAID_CHUNKSIZE=# This option lets you specify your RAID configuration for optimization purposes.
UNION = (table_name,[table_name...]) This option is only for MERGE tables, and it allows you to specify which tables should be part of the MERGE.
INSERT_METHOD= {NO FIRST LAST } This option is only for MERGE tables and is used to specify which table to insert data into.
DATA DIRECTORY="absolute path to directory" You can use this option to specify where you would like the data in this table to be stored.
INDEX DIRECTORY="absolute path to directory" You can use this option to specify where you would like the indexes for this table to be stored.

Finally, if you look back at the CREATE TABLE general form, you can see that you can end a CREATE TABLE with a SELECT statement. SELECT is the SQL statement we use to retrieve rows from one or more tables. (It is covered in Chapters 6 through 8 of this book.) We can use this clause to fill the new table with the data that is returned by the SELECT statement.