MySQL CREATE TABLE with Examples


How to Create Tables in MySQL Using the CREATE TABLE Statement

In this tutorial, you'll learn how to create a table in MySQL using the MySQL CREATE statement.

CREATE TABLE Syntax

The syntax of the MySQL CREATE TABLE statement is as follows:

CREATE TABLE [IF NOT EXISTS] table_name(
    column_name(s)
) ENGINE=storage_engine;

To define a column for the table in the CREATE TABLE statement, you use the following syntax:

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

Remarks

  • Column_name: The name of the column that you want to create. Each column has a specific data type and maximum length.
  • NOT NULL: The column doesn't allow NULL if specified. Each record must contain a value for that column.
  • DEFAULT value: The default value of the column to add if not specified.
  • AUTO_INCREMENT: The column value is generated automatically by the system at the time MySQL adds a new record to a table. There can be only one AUTO_INCREMENT column per table.

Creating a table with primary key columns

To create a table with primary key columns, use the following syntax:

CREATE TABLE [IF NOT EXISTS] table_name(
    column_name(s),

    PRIMARY KEY (column_name1, column_name2, ...)
) ENGINE=storage_engine;

Creating a table with a column referenced to another table

To create a table with a column referenced to another table, use the following syntax:

CREATE TABLE [IF NOT EXISTS] table_name(
    column_name(s),

    PRIMARY KEY (column_name1, column_name2, ...),

    FOREIGN KEY (column_name) REFERENCES referenced_table_name (referenced_column_name)
) ENGINE=storage_engine;

Examples

Example 1: Creating a table without a primary key column

Let's say you want to create a table named customers, which consists of two columns, customer_id and cusotmer_name, without a primary key column.

  • The customers table doesn't have a primary key column.
  • The customer_id column is an INT data type and can't contain NULL values.
  • The customer_name column is a VARCHAR data type and can store up to 50 characters, and can also contain NULL values.

CREATE TABLE IF NOT EXISTS customers (

    customer_id INT NOT NULL,

    customer_name VARCHAR(50) NULL

) ENGINE = innodb;

MySQL allows creating a table without a primary key.

Example 2: Creating a table with a primary key

In this example, let's create a table that consists of a primary key column.

We're going to create a new table named customers that consists of three columns, customer_idcustomer_name, and customer_group.

  • The customer table consists of one primary key column.
  • The customer_id column is the primary key and set as the AUTO_INCREMENT column of the table. The value is created automatically at the time a new record added to the table, if not specified.
  • The customer_name column is a VARCHAR data type and can store up to 50 characters, and can also contain NULL values.
  • The customer_group column is a VARCHAR data type and can store up to 40 characters, and can also contain NULL values. It defaults to Retail Customers, if not specified.
  • The active column is a TINYINT data type and can contain NULL values. It defaults to 1, if not specified.

The SQL statement is as follows:

CREATE TABLE IF NOT EXISTS customers (

    customer_id INT NOT NULL AUTO_INCREMENT,

    customer_name VARCHAR(50) NULL,

    customer_group VARCHAR(40) NULL DEFAULT "Retail Customers",

    active TINYINT NULL DEFAULT 1,

    PRIMARY KEY (customer_id)

) ENGINE = innodb;

Example 3: Creating tables with primary and foreign keys

In this example, let's create two tables, named customers and groups. 

The customers table consists of three columns, customer_idcustomer_name, and group_id. The customer_id is a primary key.

The groups table consists of two columns group_id and group_name. The group_id column is a primary key. 

The group_id column of the customers table referenced the group_id of the groups table.

Creating a groups table:

CREATE TABLE IF NOT EXISTS groups (

    group_id SMALLINT NOT NULL AUTO_INCREMENT,

    group_name VARCHAR(150) NULL,

PRIMARY KEY (group_id)

) ENGINE = innodb;

Creating a customers table:

CREATE TABLE IF NOT EXISTS customers (

    customer_id INT NOT NULL AUTO_INCREMENT,

    customer_name VARCHAR(50) NULL,

    group_id SMALLINT NULL,

    PRIMARY KEY (customer_id),

    FOREIGN KEY (group_id) REFERENCES groups (group_id)

) ENGINE = innodb;

As you can see, the customer table consists of both primary key and foreign key.

Example 4: Creating tables with composite primary keys

In this example, I show you how to create composite primary keys by creating four tables, named customersproductsorders, and order_items

  • The products table consists of three columns, product_id,  product_name, and price
  • The customers table consists of two columns customer_id and customer_name
  • The orders table consists of three columns, order_idcustomer_id, and order_number. The customer_id column referenced to the customer_id of the customers table.
  • The order_item consists of three columns, order_idproduct_id, and unit_price. The product_id column referenced the product_id of the products table, and the order_id column referenced the order_id of the orders table. And, the product_id and order_id are the primary keys.

Creating the products table:

CREATE TABLE IF NOT EXISTS products (

    product_id INT NOT NULL AUTO_INCREMENT,

    product_name VARCHAR(50) NULL,

    price DECIMAL(2) NOT NULL,

    PRIMARY KEY (product_id)

) ENGINE = innodb;

Creating the customers table:

CREATE TABLE IF NOT EXISTS customers (

    customer_id INT NOT NULL AUTO_INCREMENT,

    customer_name VARCHAR(50) NULL,

   PRIMARY KEY (customer_id),

) ENGINE = innodb;

Creating the orders table:

CREATE TABLE IF NOT EXISTS orders (

    order_id INT NOT NULL AUTO_INCREMENT,

    customer_id INT NOT NULL,

    order_number VARCHAR(20) NOT NULL,

    PRIMARY KEY (order_id),

    FOREIGN KEY (customer_id) REFERENCES customers (customer_id),

) ENGINE = innodb;

Creating the order_items table:

CREATE TABLE IF NOT EXISTS order_items (

    order_id INT NOT NULL,

    product_id INT NOT NULL,

    unit_price VARCHAR(255) NOT NULL,

    PRIMARY KEY (order_id, product_id),

    FOREIGN KEY (product_id) REFERENCES products (product_id),

    FOREIGN KEY (order_id) REFERENCES orders (order_id),

) ENGINE = innodb;

As you can see, the order_items table consists of two primary keys (order_id and product_id).

In this tutorial, you've learned how to create tables in MySQL using the CREATE TABLE statement. Also, you learned to create tables with primary key and foreign key columns. 

Note that a database must exist before you can create tables.

Leave a Reply

Your email address will not be published. Required fields are marked *