MySQL - CREATE Table

In this tutorial we will learn to create tables in MySQL.

We have already learned how to create database in the previous tutorial. So, open the terminal, login to MySQL server and run the USE command to start using a database.

What is a database table?

A database table is a place where we store our data in rows and columns format.

Creating a table

To create a table we use the CREATE TABLE table_name command and pass some column names and required data types.

Points to note!

• Table contains a primary key column denoted by PRIMARY KEY.
• To ensure that a column stores unique values we use UNIQUE KEY constraint.
• If we want to set default value for a column to null we write DEFAULT NULL. This means if we don't provide any value for the column then NULL will be saved.
• If we want to set default value we write DEFAULT 'VALUE1'. This menas if we don't provide any value for the column then 'VALUE1' will be saved.
• If it is mandatory to set a value for a given column then we set it to NOT NULL.
• For integer data type column we can also add AUTO_INCREMENT attribute. This will help in increasing the value in the column by 1 everytime we insert a new row in the table.

PRIMARY KEY helps to uniquely identify a row in a table. No two rows in a table can have the same primary key value.

Creating Employee Table

In the following example the employee table has 6 columns.

• employeeid ID of the employee and it is the primary key.
• firstname The first name of the employee.
• lastname The last name of the employee.
• score Total points the employee has scored.
• lastmodified The date time when the employee details were last modified.
• created The date time when the employee account was created.

Lets go ahead and create the employee table.

CREATE TABLE `employee` (
    `employeeid` VARCHAR(20) NOT NULL,
    `firstname` VARCHAR(100) NOT NULL,
    `lastname` VARCHAR(100) NOT NULL,
    `score` INT DEFAULT 0,
    `lastmodified` DATETIME DEFAULT NULL,
    `created` DATETIME NOT NULL,
    PRIMARY KEY (`employeeid`),
    UNIQUE KEY `employeeid_employee_UNIQUE` (`employeeid`)
)  ENGINE=INNODB DEFAULT CHARSET=UTF8;

If we run the above SQL query we will get the following output.

Query OK, 0 rows affected (0.03 sec)

In the above SQL query the table employee is using InnoDB storage engine. The default character set used is utf8.

Syntax for the UNIQUE KEY contraint is as follows.

UNIQUE KEY symbol (`column_name`)

In the above SQL query to create the employee table we are using the following UNIQUE KEY constraint.

UNIQUE KEY `employeeid_employee_UNIQUE` (`employeeid`)

The symbol format used in the above example is columnName_tableName_UNIQUE. You can use any other format that you wish to follow for the UNIQUE KEY symbol.

Let us now explore each column of the employee table.

Lets try creating another table and this time we will use the auto increment attribute for the primary key of the table.

Creating Comments Table

The comments table has the following 4 columns.

• commentid ID of the comment and it is the primary key.
• commentbody This is the body of the comment.
• lastmodified The date time when the comment was last modified.
• created The date time when the comment was created.

CREATE TABLE `comments` (
    `commentid` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `commentbody` VARCHAR(200) NOT NULL,
    `lastmodified` DATETIME DEFAULT NULL,
    `created` DATETIME NOT NULL,
    PRIMARY KEY (`commentid`),
    UNIQUE KEY `commentid_comments_UNIQUE` (`commentid`)
)  ENGINE=INNODB DEFAULT CHARSET=UTF8;

The primary key for the comments table is commentid.

The data type of this column is BIGINT and it is UNSIGNED meaning it can take only positive integer value.

The commentid column is set to NOT NULL meaning a value is required.

We are also using the AUTO_INCREMENT attribute for the commentid column which means we don't have to specify integer value for this column when inserting data into the comments table.

Because of the AUTO_INCREMENT attribute an integer value will be inserted automatically and everytime the value will be increased by 1.