How to Create Table in MySQL?

In this post, we will discuss how to create a table in MySQL using the command line and phpMyAdmin?

According to MySQL documentation, the general syntax to create a table in MySQL is

CREATE TABLE [IF NOT EXISTS] table_name(
   column_1_definition,
   column_2_definition,
   ...,
   table_constraints
);

where

• IF NOT EXISTS is optional. It ensures that the table will only be created when there is no table of same name is present in the database.
• column_definition is the syntax for individual column.
• table_constraints are the optional constraints for the table and columns such as PRIMARY_KEY, UNIQUE_KEY, CHECK, etc.

The general syntax for the column_definition is

column_name data_type(length) [NOT NULL] [DEFAULT value] [AUTO_INCREMENT] [column_constraint];

in which the mandatory part is the name and data type of the column.

So if we want to create a simple table without any constraints then our SQL syntax would be

CREATE TABLE table_name(
   column_name data_type(length),
   column_name data_type(length),
   ...,
   column_name data_type(length)
);

Let’s see some examples for better clarification.

Recommended: how to create a database in MySQL?

Examples

Example 1: Table without any constraints.

create table students (
roll_no int, 
name varchar(30)
);

Example 2: Table with a NOT NULL constraint.

create table students (
roll_no int NOT NULL,
name varchar(30)
);

Adding NOT NULL constraint to roll_no column means every record should have a valid roll_no value otherwise the record will not be inserted into the table.

Example 3: Table with column having default value.

create table math_marks (
roll_no int NOT NULL, 
name varchar(30) NOT NULL, 
marks int default 0
);

In the above table roll_no and name column is mandatory because of ‘NOT NULL’ constraint but marks field has default value of 0, means if we don’t specify marks of a student at the time of inserting a record then by default 0 will be stored into the respective marks column.

Recommended: Primary Key in MySQL

Example 4: Table with single column as primary key.

create table math_marks (
roll_no int PRIMARY KEY, 
name varchar(30) NOT NULL, 
marks int default 0
);

Specifying ‘roll_no’ as a primary key means ‘roll_no’ cannot be null and must be unique i.e each record in the table must have a unique roll_no.

Example 5: Table with multiple columns as primary key.

create table math_marks (
roll_no int, 
name varchar(30), 
marks int default 0, 
PRIMARY KEY (roll_no, name)
);

In the above table both roll_no and name together comprise a single primary key for math_marks.

Note: Each table can have a maximum of 1 primary key. The math_marks table has only 1 primary key which is constituted by multiple columns (i.e {roll_no, name}).

Example 6: Table with an AUTO INCREMENT column.

create table customers (
id int PRIMARY KEY AUTO_INCREMENT, 
name varchar(30) NOT NULL
);

In the above table, id is an AUTO_INCREMENT column means we don’t need to specify id value while inserting records to the table.

Note: An AUTO_INCREMENT column must be a PRIMARY KEY.

There are various constraints like CHECK, FOREIGN KEY, etc, which need separate discussion and we will discuss that in a separate post. For now, if you have any doubts or suggestions then comment below.