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 du­pli­ca­tion 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 re­la­tion­al database man­age­ment system (DBMS), MariaDB uses these tables as the basis for all storage op­er­a­tions. During table creation, in­di­vid­ual 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 place­hold­er “Name_of_table”. All ASCII code char­ac­ters are permitted. Then the in­di­vid­ual 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 over­writ­ten, 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 du­pli­ca­tions or the sub­se­quent 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 no­ti­fi­ca­tion and no table will be over­writ­ten. If there is no table with this name, a new table is created. The cor­re­spond­ing 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 demon­strat­ed 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 in­di­vid­ual columns:

• project_number: An in­di­vid­ual number is assigned to the project in this column. It is treated as a primary key and is therefore used to clearly assign each in­di­vid­ual line. With AUTO_INCREMENT we instruct the program to au­to­mat­i­cal­ly 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 char­ac­ters long. The NOT NULL con­straint 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 con­tin­u­ous project. The ac­cept­able values are a date in the specified date format or a null value.
• end: “end” describes the deadline or the actual com­ple­tion 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 de­scrip­tion of the services that were carried out for the project. The column offers space for up to 255 char­ac­ters and must not be left blank.
• created_at: The date of the re­spec­tive project creation is stored in the last column. This is based on the current time and date of the system.