MySQL Create Table

This tutorial will see how to create tables using MySQL in both the command line and the MySQL Workbench.

MySQL Create table syntax

To create tables in MySQL, we use the CREATE TABLE statement.

The basic syntax of Create Table as per MySQL documentation is as follows:

CREATE TABLE [IF NOT EXISTS] <table_name>(
<column names> <datatype> <conditions>
<table constraints>
)

The default storage engine is InnoDB.
The "IF NOT EXISTS" clause is optional. In case we try to create a table that is already present in the database, then MySQL throws an error.
After mentioning the table name, we need to specify the column name followed by the column's datatype and any specific conditions.

The conditions we can mention are as follows:

NULL | Not Null: these conditions specify if the Null values are allowed for the column or not.
Unique: the condition says that no duplicates are allowed. By default, all columns are "Not Unique."
Visible or Invisible: MySQL requires that at least one column is Visible in the table. By default, all columns are Visible.
Auto-increment: We use auto-increment to enter a series of values in rows.
Default: A value accompanies the default condition. MySQL sets this value to the row if we do not specify the value.
Constraints: We can mention constraints on the table like the Primary Key, Foreign Key, etc. after the columns are defined.
MySQL gives many other options, such as Indexes, Partitions, storage engines, etc., that we can mention while creating the table. We will not look at all of them. For more details, please refer to the official MySQL documentation.

Example for table creation

Pre-requisites for Create Table:

The database should exist.
The user should have CREATE Privileges.

Creating a table with no conditions in MySQL using the command line

Log in with the user (who has CREATE privileges) credentials.
Change to the database that you are going to use.
Create the Table

USE somedb;

CREATE TABLE EMPLOYEES(
    EMP_ID INT AUTO_INCREMENT PRIMARY KEY,
    FIRST_NAME VARCHAR(20) NOT NULL,
    LAST_NAME VARCHAR(20),
    BIRTH_DATE DATE DEFAULT '1900-00-00',
    HIRE_DATE DATE DEFAULT (CURRENT_DATE())
);

The command above creates a table with only the Primary Key constraint and InnoDB as the storage engine and all visible columns.

To see if the table got created, we type

show tables;

We can also see the columns and datatypes by-

desc employees;

Creating a table with Foreign key constraints in MySQL using the command line

We can add the following conditions to Foreign key constraints

On UPDATE RESTRICT/DELETE CASCADE
On DELETE RESTRICT/UPDATE CASCADE

By default, if we do not mention any conditions, both the CASCADE options are set to "Restrict." In such a case, we cannot update or delete records from the main table unless all the tables that reference the foreign key are updated or deleted.

Pre-requisites:

The table that we reference in the foreign key constraint should exist. If this condition is not satisfied, then MySQL throws an error.

Example:

CREATE TABLE DEPT_LOCATIONS(
   LOCATION_ID INT AUTO_INCREMENT PRIMARY KEY,
   DEPT_ID INT,
   LOCATION_NAME VARCHAR(40),
   FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENTS(DEPT_ID) 
);

The table DEPARTMENTS does not exist yet.

After creating the Departments table, we can trigger the creation script for the DEPT_LOCATIONS table.

Creating a table with both Foreign key constraints using MySQL Workbench

Login to MySQL workbench.
Change to "somedb" database.
Trigger the query:

CREATE TABLE EMPLOYEE_DEPT(
   RECORD_ID INT AUTO_INCREMENT PRIMARY KEY,
   EMP_ID INT,
   DEPT_ID INT,
   FROM_DATE DATE,
   TO_DATE DATE,
   FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEES(EMP_ID) On UPDATE cascade ON DELETE CASCADE,
   FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENTS(DEPT_ID) On UPDATE restrict ON DELETE CASCADE
);

Create a table in MySQL using the LIKE clause in Workbench

We can also create a table that is a replica of another table by using the Like clause. For example, we will create a new Employees2 table exactly like the Employees table using LIKE

CREATE TABLE EMPLOYEES2 LIKE EMPLOYEES;

MySQL Create Table