Документация
HTML CSS PHP PERL другое

Creating Tables

 
Previous
Table of Contents
Next

Creating Tables

Now, we have created a database and created any users we might wish to have access to this database. We now turn our attention to creating relations (tables) in that database. To create a table, we need to know three things:

  • The names of the columns we wish to create

  • The SQL types to which we wish to assign each of them

  • The name we plan to give the new table

SQL Data Types

Although we discussed general data types when talking about how you go about laying out you data, we must now discuss the SQL data types available for use. ANSI SQL defines a number of core types, and each server provides a number of additional types.

In general, our columns are declared by the name of the column, followed by the column's type and some attributes:

ColumnName ColumnType [Attributes]

For example:

fullName VARCHAR(200)
username VARCHAR(100) NOT NULL
user_id INTEGER AUTO_INCREMENT

Tables 9-1 to 9-4 list the key SQL types you will commonly work with, including a couple of non-ANSI SQL types that are seen in key servers, such as Oracle and Microsoft SQL Server.

Table 9-1. Common Numeric SQL Data Types

Type Name

Example Declaration

Description

SMALLINT

col1 SMALLINT

A 16-bit integer value. Some implementations allow an UNSIGNED attribute, but this is non-standard.

INT

col2 INT

An integer value using 4 bytes of storage with a maximum value of 2147483647 and a minimum value of -2147483648. Again, some implementations allow you to control whether they are signed.

BIGINT

col3 BIGINT

An integer value using 8 bytes of storage with a range of values from 9223372036854775808 to 9223372036854775807. Signing remains a non-standard extension.

FLOAT

col4 FLOAT

This is most often implemented as a 4-byte floating-point number similar to the float used in PHPthe number is an approximate value and is not always the exact value originally entered. Precision can be as low as six digits, and the range of values is often from 1x10-37 to 1x1037.

DOUBLE PRECISION

col5 DOUBLE PRECISION

This is most often implemented as an 8-byte floating-point number representing an approximate value instead of an exact one. Precision can be as low as 15 digits, and the valid range of values is 1x10307 to 1x10308.

REAL

col6 REAL

This is an alias for either the FLOAT or DOUBLE PRECISION data type, which is dependant on the system you are using. MySQL maps this to FLOAT, whereas PostgreSQL defaults to mapping this with DOUBLE PRECISION (although this can be controlled via a configuration option).

NUMERIC

col7 NUMERIC(p,d)

Represents a decimal number with specified precision p and number of decimal digits d (both of which are positive integers). Precision can be quite large and is not an approximate data type like the various floating-point types. However, implementation is quite slow.

DECIMAL

col8 DECIMAL(p,d)

Represents a decimal number with specified precision p and number of decimal digits d (both of which are positive integers). Precision can be quite large and is not an approximate data type like the various floating-point types. However, implementation is quite slow.


Table 9-4. Common Binary SQL Data Types

Type Name

Example Declaration

Server

Description

BLOB

colc BLOB

MySQL, Oracle

MySQL and Oracle use the BLOB data type to store binary data. In MySQL, these can be up to 216 bytes in length. For larger data file requirements, MEDIUMBLOB (224 bytes) and LONGBLOB (232 bytes) are available. Oracle BLOB fields can be 4GB in size.

image

cold image

Microsoft SQL Server

Microsoft's SQL Server uses the image data type to represent binary data. Fields of this type can be up to 2GB in length.

bytea

cole bytea

PostgreSQL

PostgreSQL includes the bytea field type, which is used to store strings in a binary form. This can also be used to store arbitrary binary data, with the caveat that a number of values have to be escaped by writing them out in three-digit octal notation and prefixing them with a backslash. These values include 0, 39 (single quote), 92 (backslash), and the values 031 and 127255. There is no expressed size limit on this data type.


Table 9-2. Common Date and Time SQL Data Types

Type Name

Example Declaration

Description

DATE

col9 DATE

Represents the date, typically on the Julian Calendar. In addition to ISO 8601 formatted dates ("yyyy-mm-dd"), the various engines support other date formats (such as "mm/dd/yy" and "dd/mm/yy") through configuration. Programmers are encouraged to be careful when entering and showing dates since it can be difficult for users to understand what the date "02/04/03" means.

TIME

cola TIME

Represents a time of day value (24-hour days). The ability to handle 12-hour versus 24-hour time is usually provided, although various implementations differ on how well they handle localized values of the strings "AM" and "PM." We often see values represented as "08:15:32.44 PM" or "20:15:32.44."

TIMESTAMP

colb TIMESTAMP

Represents a date and a time together. It is usually entered as a date followed by a time. The internal representation of this varies from system to system.


Table 9-3. Common Character SQL Data Types

Type Name

Example Declaration

Description

CHAR

colc CHAR(n)

This type represents a fixed-length string, with any unused "slots" in the string filled in with spaces. The value n represents how many characters are actually stored and typically has a maximum permissible value of 255.

VARCHAR

cold VARCHAR(n)

This type represents a variable-length string, meaning the database will accept and store any string up to a length of n characters. The exact permissible size of n varies from system to system, but 255 is not an uncommon value.

TEXT

cole TEXT

This type is actually not in the ANSI SQL standard, but most servers support it (although it is called CLOB in Oracle). It represents the ability to store large, sometimes unlimited amounts of text data. For those servers that do not support it, the BLOB or other binary types work, too. Please note that some servers do not consider the entire string when using it in sorting operationsmany choose to use only the first x characters, where x could be as small as 1000.

NCHAR

colf NCHAR(n)

This is a variation on the CHAR field. For servers where the "default character set" cannot be specified for an entire database or table, they typically provide this column type to enter Unicode characters, usually in UTF-8 format.

NVARCHAR

colg NVARCHAR(n)

This is a variation on the VARCHAR field. For servers where the "default character set" cannot be specified for an entire database or table, they typically provide this column type to enter Unicode characters, usually in UTF-8 format.


There are a few attributes that can be placed after the column type to support things such as keys and restrictions on values that are not standardized across servers. The ones we use most frequently throughout this book with MySQL (we show other server equivalents in Appendix B) are:

  • NOT NULL This specifies that the given column can never be empty or unspecified; there must be a value whenever a new row is created.

  • DEFAULT value This lets us specify a default value for the column when inserting new data. For example, we could create a column:

    user_country VARCHAR(150) DEFAULT "USA"
    

  • AUTO_INCREMENT For numeric column types, this tells the server to automatically increment the value of the column whenever new data is inserted.

  • PRIMARY KEY This tells the server that this is the unique, non-null column that will serve as the primary key for our table.

  • UNIQUE This tells the server that duplicate values are not permitted in this column.

We will now look at creating tables.

The Database Server Client Lifecycle

To create a table, you must first be connected to the database server as a user with permissions to do so. Once this is done, you then go and tell MySQL which database you wish to work with. This is done via the USE query, as follows. (We have created a database called MessageBoard as an example.)

mysql> USE MessageBoard;
Database changed
mysql>

We see that there is a common set of actions we always perform when connecting to a database server and working with our data:

  1. Connect to the server with a client program.

  2. Authenticate the database user.

  3. Select an appropriate database for use with the USE query.

  4. Set up the character set to use for the connection (if necessarysee the next two chapters for more detail).

  5. Perform any actions and queries.

  6. Disconnect from the database server and exit.

Creating the Table

Given both the names and types of the columns that you would like to create, you are ready to execute the CREATE TABLE query, which has the following syntax:

CREATE TABLE TableName(
  ColumnName ColumnType [attributes],
  ...
) [attributes or directives];

With the CREATE TABLE query, you specify (in parentheses) a list of the columns to be included in the table, including both their names and types along with any necessary attributes. The attributes we use most frequently are the NOT NULL, AUTO_INCREMENT, and PRIMARY KEY attributes. The first has the database verify that no critical field contains empty values at any point (although we also verify the input and values from within PHP in our web applications). AUTO_INCREMENT has MySQL manage the assignment and incrementing of column values, while PRIMARY KEY designates the given column as the primary key for the table being created. (PRIMARY KEY also implies that the field is NOT NULL.)

For example, for our Messages table, we would execute the following statement given the final design we developed through normalization:

mysql> CREATE TABLE Messages
    -> (
    ->   message_id INTEGER AUTO_INCREMENT PRIMARY KEY,
    ->   author_id INTEGER NOT NULL,
    ->   date_posted DATETIME,
    ->   title VARCHAR(150),
    ->   body TEXT,
    ->   forum_id INTEGER NOT NULL
    -> );
Query OK, 0 rows affected (0.23 sec)

We can also create the tables for the users, replies, and forums:

mysql> CREATE TABLE Users
    -> (
    ->   user_id INTEGER AUTO_INCREMENT PRIMARY KEY,
    ->   user_name VARCHAR(50) NOT NULL,
    ->   full_name VARCHAR(150),
    ->   user_email VARCHAR(200) NOT NULL,
    ->   birthdate DATE
    -> );
Query OK, 0 rows affected (0.18 sec)

mysql> CREATE TABLE Replies
    -> (
    ->   reply_id INTEGER AUTO_INCREMENT PRIMARY KEY,
    ->   author_id INTEGER NOT NULL,
    ->   message_id INTEGER NOT NULL,
    ->   date_posted DATETIME,
    ->   title VARCHAR(150),
    ->   body TEXT
    -> );
Query OK, 0 rows affected (0.14 sec)

mysql> CREATE TABLE Forums
    -> (
    ->   forum_id INTEGER AUTO_INCREMENT PRIMARY KEY,
    ->   name VARCHAR(200) NOT NULL,
    ->   description TEXT,
    ->   owner_id INTEGER
    -> );
Query OK, 0 rows affected (0.36 sec)

To see a list of all the tables in our database, we can execute the following query (provided we have the permissions):

mysql> SHOW TABLES;
+------------------------+
| Tables_in_messageboard |
+------------------------+
| Forums                 |
| Messages               |
| Replies                |
| Users                  |
+------------------------+
3 rows in set (0.00 sec)

If we have forgotten some of the details about our table, we can execute the following query to see a list of the columns, their types, and other attributes.

mysql> DESCRIBE Messages;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| message_id  | int(11)      |      | PRI | 0       |       |
| author_id   | int(11)      |      |     | 0       |       |
| date_posted | datetime     | YES  |     | NULL    |       |
| title       | varchar(150) | YES  |     | NULL    |       |
| body        | text         | YES  |     | NULL    |       |
| forum_id    | int(11)      |      |     | 0       |       |
+-------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

Table Storage Engines

One feature that you will encounter when using the MySQL database server is its support of the ability to store tables in a number of different formats. Each of these formats is managed by a code library called a storage engine. These engines are called from the core MySQL processes and support a number of different features.

The two most common engines you will see and that you will work with are MyISAM (formerly simply called ISAM) and InnoDB. Both of these ship with the freely downloadable MySQL binary releases and support all of the features we will need throughout this book. The key difference between them is that InnoDB supports more robust table and record locking needed for SQL transactions, while the MyISAM engine, not burdened with all of the extra code to support these features, is faster and requires less space for table storage (but cannot be used for transactions and other advanced features).

There are a few other storage engines you might encounter, including BDB (based on the Berkeley Database code libraries managed by Sleepycat Software) and the NDB Cluster engine, which allows for storage of tables across multiple computers.

MySQL works with a "default" storage engine. This is typically the MyISAM engine, but there are installations (notably on Windows) where InnoDB is made the default instead. You can change this value by passing the --default-storage-engine=type option to the server when launching it.

You can specify the storage engine to use when using the CREATE TABLE statement in SQL, as follows:

CREATE TABLE Products
(
  pid INTEGER AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price NUMERIC(10,2) NOT NULL,
  left_in_stock INTEGER NOT NULL
) ENGINE = InnoDB;

The previous table can now be used with transactions in our SQL code (which we will see in the next chapter).

Creating Indexes

For the Users table we created, we will find that we frequently want the ability to efficiently search through this table to look for records given only the user's name. As mentioned in the earlier section "Indexes for Faster Searching," we can use an index to help. We can do this in MySQL by adding an index to the CREATE TABLE statement, as follows:

INDEX (column1, column2, ..., columnn)

For our Users table, we will create an index on the user_name field as follows:

CREATE TABLE Users
(
  user_id INTEGER AUTO_INCREMENT PRIMARY KEY,
  user_name VARCHAR(50) NOT NULL,
  full_name VARCHAR(150),
  user_email VARCHAR(200) NOT NULL,
  birthdate DATE,
  INDEX (user_name)
);

If we change the way our application works and later find that we are frequently performing searches on the user_email field, we can create an index after table creation by using the CREATE INDEX statement, which looks as follows:

CREATE INDEX index_name ON TableName (columns)

Thus, we would run the following for our Users table:

CREATE INDEX user_email ON Users (user_email);

Foreign Keys and Cascading Deletes

Taking another look at the Messages and Forums tables we created previously with the CREATE TABLE statement, we see that our table for messages has a foreign key reference to the Forums table, as follows:

forum_id INTEGER NOT NULL

This suggests that we want the values in this column to only be valid identifiers from the forum_id field of the Forums tables. While we could add some code so that whenever a user adds a message, we verified the forum_id was valid, we will instead have the database server do this work for us by having it enforce the foreign keys. This is done by adding a new entry to our CREATE TABLE statement with the following structure:

FOREIGN KEY (my_field) REFERENCES parent_tbl (field)

This statement takes the name of the field in the current table that is to act as a foreign key reference and then takes the name of the parent table and the field that are to identify the referenced values.

All involved tables must be declared as using the InnoDB engine to have MySQL enforce these constraints; otherwise, they are silently ignored.

In the following, we show how to create our Messages table so that the forum_id and the user_id field are both validated and enforced by the database server. Also, we want entries in the Forums table to have their owner_id value validated by the database engine:

CREATE TABLE Users
(
  user_id INTEGER AUTO_INCREMENT PRIMARY KEY,
  user_name VARCHAR(50) NOT NULL,
  full_name VARCHAR(150),
  user_email VARCHAR(200) NOT NULL,
  birthdate DATE,
) ENGINE = InnoDB;

CREATE TABLE Forums
(
  forum_id INTEGER AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(200) NOT NULL,
  description TEXT,
  owner_id INTEGER,
  FOREIGN KEY (owner_id) REFERENCES Users (user_id)
) ENGINE = InnoDB;
CREATE TABLE Messages
(
  message_id INTEGER AUTO_INCREMENT PRIMARY KEY,
  author_id INTEGER NOT NULL,
  date_posted DATETIME,
  title VARCHAR(150),
  body TEXT,
  forum_id INTEGER NOT NULL,
  FOREIGN KEY (author_id) REFERENCES Users (user_id),
  FOREIGN KEY (forum_id) REFERENCES Forums (forum_id)
);

CREATE TABLE Replies
(
  reply_id INTEGER AUTO_INCREMENT PRIMARY KEY,
  author_id INTEGER NOT NULL,
  message_id INTEGER NOT NULL,
  date_posted DATETIME,
  title VARCHAR(150),
  body TEXT,
  FOREIGN KEY (author_id) REFERENCES Users (user_id),
  FOREIGN KEY (message_id) REFERENCES Messages (message_id)
);

Attempting to add a record to the Messages table with a user_id or forum_id field that does not represent a valid identifier from the appropriate table results in an error:

ERROR 1216 (23000): Cannot add or update a child row:
  a foreign key constraint fails

However, when we delete a forum, we now have a situation where there are a number of rows in the Messages table that point to a forum that no longer exists. If we are designing a web application where we want the database to automatically delete all of the messages that belong to that forum, we can further modify the FOREIGN KEY constraint we made by having it perform a cascading delete. When a record in the parent (for instance, Forums) table is deleted, any records in the child table where the foreign key reference is set to the ID of the recently deleted parent record are also deleted by the database engine.

We tell the database server we want this to happen by adding ON DELETE CASCADE to the foreign key declaration:

FOREIGN KEY (forum_id) REFERENCES Forums (forum_id)
    ON DELETE CASCADE


Previous
Table of Contents
Next
© 2000- NIV