How to use MariaDB CREATE TABLE

In MariaDB, the CREATE TABLE command is used to create new tables. To ensure that each table is unique within the database, the options OR REPLACE and IF NOT EXISTS can be used to avoid duplication and error messages.

What is CREATE TABLE for MariaDB?

The CREATE TABLE statement in MariaDB is used to create a new table that can later be filled with data. As a relational database management system (DBMS), MariaDB uses these tables as the basis for all storage operations. During table creation, individual columns are defined, and the data types for each column are specified. Tables are unique within a newly created database – MariaDB CREATE DATABASE – so if a table with the same name already exists, an error message will be generated. In the upcoming sections, we will describe how to use the CREATE TABLE command in MariaDB and explore the available options you can use.

Syntax and operating mode

The general syntax of CREATE TABLE in MariaDB always follows this principle:

CREATE TABLE Name_of_table(
	Name_of_first_column Data_type_of_first_column,
	Name_of_second_column Data_type_of_second_column
	…
);

To do this, first create a new table and give it its own name instead of the placeholder “Name_of_table”. All ASCII code characters are permitted. Then the individual columns need to be specified. Each of these columns is given its own name and data type that may be stored within this column. All columns are separated from each other by commas.

OR REPLACE and IF NOT EXISTS

Since tables must be unique, you will receive an error message if a table with the same name already exists. To avoid this problem, you have two options: The OR REPLACE option checks whether a table with the same name already exists in the database. If this is the case, the old table is replaced by the new one. Otherwise, the new table is simply created. The syntax of this statement looks like this:

CREATE OR REPLACE TABLE Name_of_table(
	Name_of_first_column Data_type_of_first_column,
	Name_of_second_column Data_type_of_second_column,
	…
);

Please note, however, that the old table will be overwritten, and its content will be lost. The option works as a short form of this code:

DROP TABLE IF EXISTS Name_of_table;
CREATE TABLE Name_of_table (
	Name_of_first_column Data_type_of_first_column,
	Name_of_second_column Data_type_of_second_column
	…
);

Another way to avoid duplications or the subsequent error messages is the option IF NOT EXISTS. This checks whether a table with the same name already exists in the database. If this is the case, you will only receive a notification and no table will be overwritten. If there is no table with this name, a new table is created. The corresponding syntax looks like this:

CREATE TABLE IF NOT EXISTS Name_of_table (
	Name_of_first_column Data_type_of_first_column,
	Name_of_second_column Data_type_of_second_column
	…
);

Example of CREATE TABLE in MariaDB

The CREATE TABLE function in MariaDB can be best demonstrated with a simple example. We’ll create a table for a fictional project list that contains eight columns. The structure is as follows:

CREATE TABLE Projects(
	Project_number INT AUTO_INCREMENT,
	surname VARCHAR(50) NOT NULL,
	first_name VARCHAR(50),
	start DATE,
	end DATE,
	costs DOUBLE,
	tasks VARCHAR(255) NOT NULL,
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (Project_number)
);

In the first step, we create a new table and name it “Projects”. In the following rows, we specify the individual columns:

• project_number: An individual number is assigned to the project in this column. It is treated as a primary key and is therefore used to clearly assign each individual line. With AUTO_INCREMENT we instruct the program to automatically continue the entries in “project number” in order to ensure a uniform sequence.
• surname: This is where the customer’s last name is recorded. The entry can be up to 50 characters long. The NOT NULL constraint ensures that this column cannot be left empty.
• first_name: The “first_name” column operates in a similar way to the previous column. However, since the first name isn’t required for billing, this column can be left blank.
• start: This section records the start of a continuous project. The acceptable values are a date in the specified date format or a null value.
• end: “end” describes the deadline or the actual completion of a project. These values may also be in the format DATE or NULL.
• costs: The invoice amount is listed in this column. It is stored in the format DOUBLE.
• tasks: Under “tasks” there is space for a short description of the services that were carried out for the project. The column offers space for up to 255 characters and must not be left blank.
• created_at: The date of the respective project creation is stored in the last column. This is based on the current time and date of the system.