MySQL Create Tables

MySQL Create table

MySQL CREATE TABLE is used to create a table within a database.The table creation command requires:

  • Name of the table
  • Names of fields
  • Definitions for each field

MySQL storage engine represents each table by following files

File Purpose
table_name.frm Table format (definition) file.
table_name.MYD Data file.
table_name.MYI Index file.

The CREATE TABLE statement Syntax.


Here few items need explanation:

  • Constraint NOT NULL is used because we do not want this field to be NULL. if we try to create a entry with NULL value then MySQL Engine will through an error.
  • AUTO_INCREMENT tells to MySQL Insert new record with next available incremented value.
  • Constraint PRIMARY KEY is used to define a column as primary key. Multiple columns need to be separated by comma to define a primary key.
  • The UNIQUE constraint used to uniquely identify each record in a table.The UNIQUE and PRIMARY KEY constraints both assures for uniqueness for a field or set of fields. A PRIMARY KEY constraint by default has a UNIQUE constraint defined for it.We can have many UNIQUE constraints for a table, but only one PRIMARY KEY per table.

The statement above will create ’emp_info’ table which have columns like following

Column Name Data Type Size
empID Int
empName Varchar 50
empCode Varchar 50
empSalary Varchar 50
empDept Varchar 50