MariaDB Create Table

Summary: in this tutorial, you will learn how to use the MariaDB create table statement to create a new table in a database.

Introduction to MariaDB create table statement

The create table statement allows you to create a new table in a database.

Here is the basic syntax of the create table statement:

create table [if not exists] table_name(
    column_1_definition,
    column_2_definition,
    ...,
    table_constraints
) engine=storage_engine;
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the table that you want to create after the create table keywords. The table name must be unique within a database.
• Second, use the if not exists option to conditionally create the new table only if it does not exist.
• Third, specify a list of columns for the table within the parentheses, the columns are separated by commas (,).
• Fourth, specify table constraints like primary key, foreign key, check, and unique after the column list.
• Finally, optionally specify a storage engine for the table in the engine clause. If you skip the storage engine, MariaDB will use the InnoDB by default.

MariaDB has made InnoDB as the default storage engine since version 10.2. The InnoDB is a good transaction storage engine that supports ACID, referential integrity, and crash recovery.

column definition

To define a column for a table, you use the following syntax:

column_name data_type(length) [not null] [default value] [auto_increment] column_constraint;
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the column.
• Next, specify the data type and maximum length of the column if the data type requires it.
• Then, use the not null to enforce non-null values in the column. Besides the not null constraint, you can use the check and primary key column constraints for the column.
• After that, use the default value clause to specify a default value for the column when the insert and update statements do not explicitly specify it.
• Finally, use the auto_increment property to instruct MariaDB to implicitly generate sequential integers for the column. A table has one and only one column with the auto_increment property.

or replace option

The create table statement has an or replace option:

create [or replace] table_name (
    ...
);
Code language: SQL (Structured Query Language) (sql)

The or replace option drops the table if it exists and creates a new one. It is a shortcut for the following sequence of statements:

drop table if exists table_name;

create table table_name(
    ...
);
Code language: SQL (Structured Query Language) (sql)

Note that you cannot use the or replace and if not exists option at the same time.

MariaDB create table statement examples

Let’s take some examples of using the create table statement.

A) Using MariaDB create table statement to create a simple table example

The following example uses the create table statement to create a new table called projects:

create table projects(
    project_id int auto_increment,
    project_name varchar(255) not null,
    begin_date date,
    end_date date,
    cost decimal(15,2) not null,
    created_at timestamp default current_timestamp,
    primary key(project_id)
);
Code language: SQL (Structured Query Language) (sql)

The projects table has six columns:

• The project_id is an integer column. It has the auto_increment property, therefore, MariaDB will automatically generate a sequential number when you insert a row into the table. In addition, the project_id column is the primary key specified by the primary key constraint at the end of the table. It means that a value in the project_id column will uniquely identify a row in the table.
• The project_name is a variable-length character with a maximum size of 255 characters. Because it has a not null constraint, you cannot insert null values into this column.
• The begin_date and end_date are the date columns. They accept dates and null values only.
• The cost is a decimal column that also does not accept null values.
• The created_at column is a timestamp column that accepts date and time values. In addition, its default value is the current timestamp of the operating system on which the MariaDB server runs.

Because we don’t explicitly specify the storage engine for the projects table, it takes InnoDB as the storage engine.

B) Using MariaDB create table statement to create a table with a foreign key constraint

The following example uses the create table statement to create a new table called milestones:

create table milestones(
    milestone_id int auto_increment,
    project_id int,
    milestone varchar(255) not null,
    start_date date not null,
    end_date date not null,
    completed bool default false,
    primary key(milestone_id, project_id),
    foreign key(project_id)
        references projects(project_id)
);
Code language: SQL (Structured Query Language) (sql)

In the milestones table, the primary key consists of two columns milestone_id and project_id specified by the following table constraint:

primary key(milestone_id, project_id)
Code language: SQL (Structured Query Language) (sql)

It means that a milestone will not exist without a project.

The project_id column is the foreign key column that references the project_id column of the projects table:

foreign key(project_id)
    references projects(project_id)
Code language: SQL (Structured Query Language) (sql)

The following diagram illustrates the relationship between projects and milestones tables:

In this relationship, a project may have one or more milestones while each milestone belongs to only one project. This relationship is called the one-to-many.

In this tutorial, you have learned how to use MariaDB create table statement to create a new table in the database.