CREATE TABLE SQL Statement in MySQL

Implementing Database in MySQL – Part 5

Foreword: In this part of the series, we look at important points in the MySQL CREATE TABLE Statement.

By: Chrysanthus Date Published: 14 Apr 2015

Introduction

This is part 5 of my series, Implementing Database in MySQL. You should have read the previous parts of the series before reaching here; this is a continuation. In this part of the series, we look at important points in the MySQL CREATE TABLE Statement. We shall also create the tables of the example (wholesale database).

MySQL has two kinds of tables, which are called, MyISAM tables and InnoDB tables.

CREATE TABLE SQL Statement Syntax
I will first give you the complete syntax. When you read it you will probably not understand. I will explain the important points below.

The syntax begins with:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

create_definition:
    col_name column_definition
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
      [index_option] ...
  | {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
      [index_name] [index_type] (index_col_name,...)
      [index_option] ...
  | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (index_col_name,...) reference_definition
  | CHECK (expr)

The syntax for the column_definition is:

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
      [COMMENT 'string']
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
      [STORAGE {DISK|MEMORY|DEFAULT}]
      [reference_definition]
The syntax for the data type is:

data_type:
    BIT[(length)]
  | TINYINT[(length)] [UNSIGNED] [ZEROFILL]
  | SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
  | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
  | INT[(length)] [UNSIGNED] [ZEROFILL]
  | INTEGER[(length)] [UNSIGNED] [ZEROFILL]
  | BIGINT[(length)] [UNSIGNED] [ZEROFILL]
  | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
  | DECIMAL[(length[,decimals])] [UNSIGNED] [ZEROFILL]
  | NUMERIC[(length[,decimals])] [UNSIGNED] [ZEROFILL]
  | DATE
  | TIME
  | TIMESTAMP
  | DATETIME
  | YEAR
  | CHAR[(length)]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | VARCHAR(length)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | BINARY[(length)]
  | VARBINARY(length)
  | TINYBLOB
  | BLOB
  | MEDIUMBLOB
  | LONGBLOB
  | TINYTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | TEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | MEDIUMTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | LONGTEXT [BINARY]
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | ENUM(value1,value2,value3,...)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | SET(value1,value2,value3,...)
      [CHARACTER SET charset_name] [COLLATE collation_name]
  | spatial_type

The syntax for the index_col_name is:

index_col_name:
    col_name [(length)] [ASC | DESC]

The syntax for the index_type is:

index_type:
    USING {BTREE | HASH}

The syntax for the indexc_option is:

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name

The syntax for the reference_definition is:

reference_definition:
    REFERENCES tbl_name (index_col_name,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]

The syntax for the reference_option is:

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

The syntax for the table_option is:

table_options:
    table_option [[,] table_option] ...

table_option:
    ENGINE [=] engine_name
  | AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | CONNECTION [=] 'connect_string'
  | DATA DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | INDEX DIRECTORY [=] 'absolute path to directory'
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | KEY_BLOCK_SIZE [=] value
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
  | TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
  | UNION [=] (tbl_name[,tbl_name]...)

The syntax for the partition_options is:

partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY(column_list)
        | RANGE(expr)
        | LIST(expr) }
    [PARTITIONS num]
    [SUBPARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY(column_list) }
     [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]

The syntax for the partition_definition option is:

partition_definition:
    PARTITION partition_name
        [VALUES
            {LESS THAN {(expr) | MAXVALUE}
            |
            IN (value_list)}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [NODEGROUP [=] node_group_id]
        [(subpartition_definition [, subpartition_definition] ...)]

The syntax for the subpartition_definition option is:

subpartition_definition:
    SUBPARTITION logical_name
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [NODEGROUP [=] node_group_id]

We have just seen the complete syntax. You probably have not understood it. I will explain only the important points. Consult some other document for the other points.

The Check Constraint
The CHECK constraint whose syntax is:

    CHECK (expr)

is used to restrict values in a column within a particular set. For example, you can use it to insist that no price input in the price column should be less than zero. The CHECK constraint is coded inside the SQL statement. In the next part of the series we shall learn how to write the expression (exp) in the syntax, “CHECK (expr)”.

Comment
The syntax to add a comment in a SQL statement is:

    COMMENT 'string'

You begin with the reserved word, COMMENT. This is followed by a space and then the actual comment in single quotes. In programming, you need to be commenting your work otherwise you will forget why you typed certain code segments.

Indexes
The guidelines for creating indexes in a table are:

- Define a unique index on a primary key.
- Index all foreign keys.
- Want to insist on unique values for a column, index that column with unique index.
- Index frequently searched columns.
- Index columns that frequently determine the sort order.
- The longer the table, the likelihood that it should have indexes.

Only the first point is satisfied automatically for us by MySQL. You have to code the other points yourself.

With MySQL, if you want a column to be unique, you have to use the UNIQUE constraint. You can then index the column if you think it will be frequently searched.

I have explained the rest of the important points in the previous part of the series.

Creating Tables of the Example
We continue with the wholesale example. Carry out the following instructions:

- Open the command prompt window.
- Type the following and press Enter to go to the root directory:

                cd c:\

- Start the Mysqlcom.pl tool (in the c:\perl\lib directory) and type the following command and press Enter. When it asks you to type the password, type, sql.

    connect "root", "localhost", 3306;

- We had already created the database, wholesale. In order to use it, type the following command:

USE wholesale;

At this point, you should be seeing the Mysql> prompt again.

- Type the statements for all the 8 tables in the previous part of the series (any SQL statement ends with a semicolon); and at the end of each statement, press the Enter key (each of the statements has many lines, so type the semicolon only for the last line).

You should have a positive feedback after typing the semicolon and pressing the Enter Key for each creation table statement. If you do not have a positive feedback, then you made a mistake in the typing; in that case, retype and press the Enter Key again.

- To drop the connection, by typing the following and pressing Enter:

    CLOSE

Quit the tool (PurePerl MySQL command line tool) by typing the following and pressing Enter:

    QUIT

You should see Bye. Both CLOSE and QUIT can be in lowercase. And with that, we come to the end of this tutorial. Rendezvous in the next part.

Chrys

Broad Network

Related Articles

CREATE TABLE SQL Statement in MySQL

Implementing Database in MySQL – Part 5

Introduction

Related Links

Comments