Last updated on July 31st, 2024 at 07:58 am
Many times you may need to create table in MySQL. Here’s how to create table using MySQL CREATE TABLE command.
How To Create Table in MySQL
Here are the steps to create table in MySQL.
MySQL CREATE TABLE allows you to create new table in database. Here’s the syntax.
CREATE TABLE [IF NOT EXISTS] table_name( column_1_definition, column_2_definition, ..., table_constraints ) ENGINE=storage_engine;
In the above create table query, table_name is the name of your new table. IF NOT EXISTS is an optional argument that checks if the table already exists and creates table only if it doesn’t exist.
column_1_definition, column_2_definition, … are different column definitions each consisting of column name and data type.
table_contraints are different types of optional constraints such as foreign key constraint, primary key constraint, etc.
You can optionally specify storage engine as ENGINE variable. MySQL supports InnoDB and MyISAM storage engines. If you don’t specify this variable, then MySQL will use InnoDB by default.
Bonus Read : MySQL Alter Table Column
MySQL CREATE TABLE examples
Here’s the SQL query to create table in MySQL. We will create an orders table to store the list of orders
CREATE TABLE IF NOT EXISTS orders (
id INT AUTO_INCREMENT PRIMARY KEY,
product_name VARCHAR(255) NOT NULL,
order_date DATE,
price INT NOT NULL,
description TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=INNODB;
mysql> describe orders;
+--------------+--------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| product_name | varchar(255) | NO | | NULL | |
| order_date | date | YES | | NULL | |
| price | int(11) | NO | | NULL | |
| description | text | YES | | NULL | |
| created_at | timestamp | NO | | CURRENT_TIMESTAMP | |
+--------------+--------------+------+-----+-------------------+----------------+
In the above query,
id is our table’s primary key with AUTO INCREMENT constraint. So when you add a new row to this table, MySQL will automatically increment and auto populate this column for the new row.
product_name is a VARCHAR (string) column to store product name
order_date is a date column. Since it doesn’t have any constraints, it can even have NULL values.
price is an integer column with a NOT NULL constraint. So it cannot have null values.
created_at is a timestamp column with CURRENT_TIMESTAMP as default value.
We have chosen to use INNODB storage engine for this table.
Bonus Read : MySQL ADD COLUMN
MySQL CREATE TABLE with FOREIGN KEY
Let’s create a new table in MySQL with FOREIGN KEY constraint. We will create a new table order_status with a foreign key order_id that references the primary key of orders table.
mysql> CREATE TABLE IF NOT EXISTS order_status (
status_id INT AUTO_INCREMENT,
order_id INT,
status VARCHAR(255) NOT NULL,
is_completed BOOLEAN NOT NULL DEFAULT FALSE,
PRIMARY KEY (status_id),
FOREIGN KEY (order_id)
REFERENCES orders (id)
ON UPDATE RESTRICT ON DELETE CASCADE
);
mysql> describe order_status;
+--------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+----------------+
| status_id | int(11) | NO | PRI | NULL | auto_increment |
| order_id | int(11) | YES | MUL | NULL | |
| status | varchar(255) | NO | | NULL | |
| is_completed | tinyint(1) | NO | | 0 | |
+--------------+--------------+------+-----+---------+----------------+
In the above query, we add a foreign key constraint using MySQL ADD FOREIGN KEY
Hopefully, now you can easily create table in MySQL.
Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!
Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.