MySQL Create Table Tutorial With Examples

In this tutorial, we will explore the use of the MySQL CREATE TABLE command with syntax and programming examples:

CREATE TABLE statement is a part of DDL (Data Definition Language) of SQL.

We will discuss the ways in which you can CREATE a table in a given database, mention column names, and DB engine while creating the table, along with the rules around naming conventions for SQL tables.

=> Click here for the complete MySQL tutorial series

MYSQL CREATE table

Pre Requisites

The prerequisite for running any of the SQL commands would be to download the MySQL server. The free community edition can be downloaded here.

We will also be using the MySQL Workbench SQL Client for all the examples and discussions in this tutorial. The free community edition of MySQL Workbench can be downloaded here (you can choose the version depending on the OS that you are working on).

In case you don’t want to use MySQL Workbench – you can also use the MySQL Command line client that comes with the default installation of MySQL Server.

MySQL CREATE TABLE Command

CREATE TABLE command is a part of the DDL (Data Definition Language) set of commands in MySQL and it allows a user to create a new table for the given database.

Note: The CREATE TABLE command exists for almost all the relational databases – like MySQL, Postgres, SQL Server, etc.

MySQL CREATE TABLE Syntax

In the simplest form, you can use the CREATE TABLE command with just the basic options  i.e. the table name and the column definitions.

CREATE TABLE [IF NOT EXISTS]  tableName (
	column1 datatype,
	column2 datatype,
	....
);

Let’s understand the syntax arguments in detail:

  • tableName: This should be the name of the table that you are trying to create. It should be a fully qualified name (in case you don’t have a default database set). For example, databaseName.tableName
    The table name can be specified without quotes or with backtick symbol like `tableName` and `databaseName`.`tableName`
  • Column definition: A table in SQL must consist of at least one column. All the column definitions must consist of column_name as well as the column data type. You can also optionally include the other column properties like primary key, null, not null, etc.

Let’s see an example of using the above syntax to create a table.

We will create a table named EMPLOYEE_DETAILS (in the database – SAMPLE_DB) with columns

  1. name: varchar(100)
  2. age: int
  3. address: varchar(100)
CREATE TABLE SAMPLE_DB.employee_details (
	name varchar(100),
    age int,
    address varchar(100)
);

Given below will be the output of the Table creation:

Output - Table Creation

Note:

#1) Notice the use of [IF NOT EXISTS] the optional command in the above syntax.

It’s generally recommended to use this command as it would avoid generating an error if the table we are trying to create is already present in the database.

Here’s an example of using with and without IF NOT EXISTS.

  • Without IF NOT EXISTS would generate an error if the table is already existing.

without-if-not-exists

  • With IF NOT EXISTS will not generate an error. However, it will show a warning that the table already exists.

with-if-not-exists

#2) The ‘tableName’ while using the CREATE TABLE command should be fully qualified with database name i.e. the way we have used it is SAMPLE_DB.employee_details

The other ways of specifying the table name are setting up the current database using the ‘USE’ command. E.g. USE SAMPLE_DB; and then running/using just the table name instead of the fully qualified table name.

CREATE TABLE With Table Options

The table options are used in order to optimize the behavior of the MySQL tables. These can be applied while creating a table using the MySQL CREATE TABLE command (or later through ALTER TABLE command).

The syntax remains the same with additional table options that can be specified.

CREATE TABLE [IF NOT EXISTS]  tableName (
	column1 datatype,
	column2 datatype,
	....
)
[table “” not found /]



;

We will discuss the most widely used options below (A complete list of table options can be found here).

#1) ENGINE

It is used to specify the storage engine for the table i.e. The default value is InnoDB. This is not required to be changed unless there are needs of any specific storage engine. The other valid values for storage engines are MEMORY, CSV, HEAP, etc.

The syntax for specifying ENGINE as a part of MySQL CREATE TABLE is given below.

CREATE TABLE IF NOT EXISTS SAMPLE_DB.employee_details (
	name varchar(100),
    age int,
    address varchar(100)
)ENGINE='MEMORY';

#2) AUTO_INCREMENT

This option is used to set the initial AUTO_INCREMENT value of the table i.e. The default value is 1, but you can override to any other positive integer value.

Note: AUTO_INCREMENT can be specified for just one column on the table and it should be the primary key. Let’s see an example of specifying auto-increment as 10 and inserting a record to validate if auto-increment is set correctly.

We are using the same table employee_details (ensure to drop the existing table before running this command) with an additional id field marked as the primary key.

CREATE TABLE IF NOT EXISTS SAMPLE_DB.employee_details(
	id int not null AUTO_INCREMENT primary key,
	name varchar(100),
    age int,
    address varchar(100)
)AUTO_INCREMENT=10;

Let’s insert a row without any value for ID and ensure that the values are inserting from the value starting 10.

INSERT INTO SAMPLE_DB.employee_details(name,age,address) values
('aman kumar',20,'mumbai');
select * from SAMPLE_DB.employee_details;

auto_increment_verify

#3) CHECKSUM

This should be set to 1 if you want to have a checksum for the entire table stored. It is generally used to ensure that there are no corrupted tables.

CREATE TABLE IF NOT EXISTS SAMPLE_DB.employee_details (
	name varchar(100),
    age int,
    address varchar(100)
)CHECKSUM=1;

CHECKSUM keeps a live checksum of the entire table during any inserts or updates

CREATE TABLE With Partitioning Details

We can also mention the User-defined partitioning if required using the partitioning options.

Partitioning as a concept is broadly used to distribute the contents of the tables across the file system in order to ensure faster access times and optimized queries. Partitioning splits a large table into smaller tables depending upon the strategies or partitioning keys specified.

Let’s see how we can specify Partitioning details with the MySQL CREATE TABLE Command.

We will use the sample table employee_details and add a new integer column named department_id that would be used as a partition hash key to have an even distribution of data.

Also specifying the count of partitions would indicate how many actual partitions would be created (in this case 4). If not specified then by default there would just be 1 partition.

CREATE TABLE IF NOT EXISTS SAMPLE_DB.employee_details (
	name varchar(100),
    age int,
    address varchar(100),
    department_id int
)PARTITION BY HASH (department_id)
PARTITIONS 4;

Note: Generally the key which would be used to create partitions would depend on anticipated access patterns that would be used for the table. In this case, suppose we would be querying the table based on department ID most of the time, then it makes sense to have department_id as a part of the hash key.

MySQL Table Cloning & Copying

At times, you might want to create a clone of an existing table or copy the contents from one table to another table. MySQL supports 2 ways to achieve this as shown below.

  1. Using LIKE command
  2. Using SELECT command

Table Cloning Using LIKE COMMAND

With the LIKE command, you can create a new table with exactly the same column names and properties as the existing tables.

Here’s the syntax using LIKE Command.

CREATE TABLE tableName1 LIKE tableName2

With the above command, a new table i.e. tableName1 would be created with the same structure and properties of tableName2.

Please note that with this approach – only the column names and properties get cloned and not the actual table data.

Let’s try creating a table named employee_details and use this table to create a new table named student_details using the LIKE option.

CREATE TABLE IF NOT EXISTS SAMPLE_DB.employee_details (
name varchar(100),
age int,
address varchar(100),
department_id int
);

CREATE TABLE SAMPLE_DB.student_details LIKE SAMPLE_DB.employee_details;

Given below is the output of the above command.

table_clone_LIKE

Table Cloning Using SELECT COMMAND

This approach uses the SELECT command to create a copy of the existing table into a new table.

With this approach, the data from the table also gets copied over to the new table.

CREATE TABLE tableName1 AS SELECT * FROM tableName2;

Let’s try creating a table named employee_details and use this table to create a new table named student_details using the SELECT option.

CREATE TABLE SAMPLE_DB.student_details AS SELECT * FROM SAMPLE_DB.employee_details;

MySQL Table Naming Conventions

In the previous sections, we learned about creating tables MySQL. Now let’s see some rules that should be kept in mind while naming the tables along with the restrictions that apply with respect to MySQL.

These conventions/rules apply to both SQL tables as well as databases.

#1) Legal Characters In Names

a) Unquoted names can consist of any characters in the SQL Server default character set with an exception that not all the characters can be digits. For example, ‘23test’ is a valid table name but not ‘2345’.

Given below is the list of characters that could be used for unquoted names:

ASCII: [0-9,a-z,A-Z$_] (basic Latin letters, digits 0-9, dollar, underscore)
Extended: U+0080 .. U+FFFF

Learn more about ASCII codes here

b) Table and database names can be quoted with a backtick character (`) and can contain any letter/character except the backtick character itself. With the quoted names you can even have table/database names having all digits.

Please note that for such tables you would need to use the backticks around the table and/or database name for accessing data inside such tables.

c) Table and Database names cannot contain period ‘.’ as that’s used as a database and table separator.

d) The Database and Table names can contain – ‘$’ and ‘_’ special characters.

#2) Name Length

Max allowed length for a database or table name in MySQL is 64 characters.

#3) Name Qualifiers 

As discussed in the previous sections, in MySQL a table is always in context with the database of which it is a part of. Hence in order to access a table, you can use a fully qualified table name – which is nothing but the combination of database name separated by a period and then the column name.

For example, to select all the elements from the table ‘EMPLOYEE_DETAILS’ from the database SAMPLE_DB, you can use a fully qualified name as below.

SELECT * from SAMPLE_DB.EMPLOYEE_DETAILS;

The DB and table names can also be specified as quoted with backticks separately as shown below.

SELECT * from `SAMPLE_DB`.`EMPLOYEE_DETAILS`;

The industry recommended conventions & best practices around the Naming tables and Databases are given below.

  1. Name tables and databases in lowercase – This generally speeds up typing and querying the DB especially for those involved in day to day querying DB.
  2. Use underscores (‘_’) instead of spaces in table and db names – This makes the names more readable.
  3. Use self-explanatory names for the tables and databases – For example, a table containing details of the employee can be named in different ways like employee_info, employee_data, employee, employee_details, employee_name_and_address. It would make sense to choose a name that’s the most self-explanatory. For example, we can choose the table name to be employee_details or employee_info. Though this is a subjective discussion, it should be agreed upon by multiple members of the team who are going to use and create these entities.
  4. Avoid using numbers in database and table names – Like sample_db_2, test_table_1 etc.

Frequently Asked Questions And Answers

Q #1) How to create a table in MySQL using Index?

Answer: You can add an INDEX against any column (or combination of columns) during the table creation itself.

Let’s see an example of adding an Index against the department_id column for table employee_details.

CREATE TABLE IF NOT EXISTS SAMPLE_DB.employee_details (
	name varchar(100),
    	id int,
    	age int,
    	address varchar(100),
    	department_id int,
    	index(department_id)
);

Q #2) How to create a table with date in MySQL?

Answer: Date is a data type that should be associated with any column while creating a table.

Refer to the below sample CREATE TABLE command with the joining_date column having DATETIME as the datatype for a sample table employee_details.

CREATE TABLE IF NOT EXISTS SAMPLE_DB.employee_details (
    name varchar(100),
    id int primary key,
    age int,
    address varchar(100),
    joining_date datetime,
    department_id int
);

Q #3) How can I see the structure of the table in MySQL?

Answer: After creating a table, if you want to refer to the structure of the table like columns, indexes, etc, you can use the DESCRIBE command to fetch the details.

Syntax:

DESCRIBE tableName;

Let’s create a table and see the output for the DESCRIBE command.

CREATE TABLE IF NOT EXISTS SAMPLE_DB.employee_details (
	name varchar(100),
    	id int primary key,
    	age int,
    	address varchar(100),
    	department_id int
);

DESCRIBE sample_db.employee_details;

Given below is the output of the DESCRIBE command.

Describe Command - output

Q #4) How do I add a FOREIGN KEY to a table in MySQL?

Answer: Foreign Key is used to link 2 tables together in MySQL. In order to use the Foreign Key constraint, you should have already created the table that you are referring to.

>>Learn more about MySQL Foreign Key Constraint

Let’s try understanding this with an example. Let’s say that we have 2 tables i.e. Department(which has details about the various departments that the college has) and Student details (all details pertaining to students).

Department has columns – id (primary key) and name.
Student Details – id (primary key), age, address & department_id (foreign key referenced from Department table).

Given below is the syntax for the CREATE TABLE command for both these tables.

CREATE TABLE department (
    name varchar(100),
    id INT NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE student_details (
    name varchar(100),
    id int not null,
    age int,
    address varchar(100),
    department_id int,
    PRIMARY KEY (id),
    FOREIGN KEY (department_id) 
    references 
    department(id)
    ON DELETE CASCADE
);

Refer to the syntax of FOREIGN KEY reference in the student_details table where we have mentioned the relationship between the columns and department_id should be referenced by column id from the department table.

Conclusion

CREATE TABLE command in MySQL that belongs to commands in the category of Data Definition Language was explained in detail here.

We learned about the different table options like ENGINE, CHECKSUM, etc that could be mentioned along with the CREATE TABLE command to have additional properties being set for the table.

We went through the ways to create a clone of the existing table in MySQL. Finally, we talked about the naming conventions for the table names along with the industry-recommended best practices.

Happy Reading!!

Recommended Reading