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

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

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

Let’s see some examples for better clarification.

Recommended: how to create a database in MySQL?

Examples

Example 1: Table without any constraints.

MySQL table

Example 2: Table with a NOT NULL constraint.

MySQL table with not null column

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.

MySQL table with column having default value

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.

MySQL table with single column as primary key

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.

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.

mysql table with auto increment column

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.

This Post Has 4 Comments

Leave a Reply