MySQL Create Table

A table in a database represents one entity of application. It stores data in form of rows of attributes or fields. The columns represent fields to store data. A row is the set of values assigning against the columns. A table can store data about students, clients, cases, orders, items etc. In this tutorial we will learn how use MySQL create table statement.

Create Table with MySQL Create Table Statement

Col1 Datatype (length) [INDEX| CONSTRAINT],
Col2 Datatype (length) [INDEX| CONSTRAINT],
Col3 Datatype (length) [INDEX| CONSTRAINT],  … … 

All the words written in uppercase are the keywords.  MySQL is not CASE SENSITIVE for the keywords.

Datatype– it is type of data to be stored against the column. It can be string, numeric, date etc. The basic and very commonly used data types are discussed here.

Table-name and ColN are the names you to be given to data table and associated columns. The names must follow these rules

  • Names can begin only with a alphabet
  • Alphabets, numeric (0-9), _(underscore), $ and # characters can be used.
  • Names of all objects must be unique. No two tables or two columns of a table can have same names

IF NOT EXISTS when used in MySQL Create Table statement prevents error if the table already exists in your database.

CONSTRAINTS are also called modifiers that control the value of data to be added in a field.  Column definition in MySQL Create Table statement may use one or all of these constraints.

  • NOT NULL it restricts null value in a column.
  • PRIMARY KEY it defines the column as row identifier value for a table. It must be unique and not null.
  • AUTO_INCREMENT is applied only for numeric field and when a new record is entered in the table, the field value will be automatically increments. You don’t need to give the field value for a column defined as AUTO_INCREMENT. The next value will be one incremented from the last value.