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.
A database table is a place where we store our data in rows and columns format.
To create a table we use the
CREATE TABLE table_name command and pass some column names and required data types.
Points to note!
DEFAULT NULL. This means if we don't provide any value for the column then NULL will be saved.
DEFAULT 'VALUE1'. This menas if we don't provide any value for the column then 'VALUE1' will be saved.
AUTO_INCREMENTattribute. 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.
In the following example the
employee table has 6 columns.
employeeidID of the employee and it is the primary key.
firstnameThe first name of the employee.
lastnameThe last name of the employee.
scoreTotal points the employee has scored.
lastmodifiedThe date time when the employee details were last modified.
createdThe 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
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.
|employeeid||VARCHAR(20)||This column can save variable length string and max 20 characters.|
The column has
|firstname||VARCHAR(100)||This column can save variable length string of max length 100 characters.|
This is also set as
|lastname||VARCHAR(100)||This column can save variable length string of max length 100 characters similar to the firstname column.|
And we have to set some value for this column as it is set as
|score||INT||This column can hold integer value.|
It has an attribute
|lastmodified||DATETIME||This column will save date time value in the format |
It has an attribute
|created||DATETIME||Similar to lastmodified column this will save date time value in the format |
It has the attribute
Lets try creating another table and this time we will use the auto increment attribute for the primary key of the table.
The comments table has the following 4 columns.
commentidID of the comment and it is the primary key.
commentbodyThis is the body of the comment.
lastmodifiedThe date time when the comment was last modified.
createdThe 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
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.