Database Reference
In-Depth Information
Creating Tables
The next step forstructuring a database is to create tables. Although this can be complic-
ated, we'll keep it simple to start. We'll initially create one main table and two smaller
tables for reference information. The main table will have a bunch of columns, but the ref-
erence tables will have only a few columns.
For our fictitious bird-watchers site, the key interest is birds. So we want to create a table
that will hold basic data on birds. For learning purposes, we won't make this anelaborate
table. Enter the following SQL statement into
mysql
on your computer:
CREATE TABLE
birds
(
bird_id
INT
AUTO_INCREMENT
PRIMARY KEY
,
scientific_name
VARCHAR
(
255
)
UNIQUE
,
common_name
VARCHAR
(
50
),
family_id
INT
,
description
TEXT
);
This SQL statement creates the table
birds
with five fields, or columns, with commas
separating the information about each column. Note that all the columns together are con-
tained in a pair of parentheses. For each colum, we specify the name, the type, and optional
settings. For instance, the information we give about the first column is:
▪ The name,
bird_id
▪ The type,
INT
(meaning it has to contain integers)
▪ The settings,
AUTO_INCREMENT
and
PRIMARY KEY
The names of the columns can be anything other than words that are reserved for SQL
statements, clauses, and functions. Actually, you can use a reserve word, but it must always
be given within quotes to distinguish it. You can find a list of data types from which to
choose on the websites of MySQL and MariaDB, or in my book,
MySQL in a Nutshell
.
We created this table with only five columns. You can have plenty of columns (up to 255),
but you shouldn't have too many. If a table has too many columns, it can be cumbersome to
use and the table will be sluggish when it's accessed. It's better to break data into multiple
tables.
The first column in the
birds
table is a simple identification number,
bird_id
. It will
be theprimary key column on which data will be indexed — hence the keywords,
PRIMARY KEY
. We'll discuss the importance of the primary key later.
The
AUTO_INCREMENT
option tells MySQLto automatically increment the value of this
field. It will start with the number 1, unless we specify a different number.
