# 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](https://www.ionos.com/digitalguide/hosting/technical-matters/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:

```sql
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](https://www.ionos.com/digitalguide/server/know-how/ascii-codes-overview-of-all-characters-on-the-ascii-table/) 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:

```sql
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:

```sql
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:

```sql
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:

```sql
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.

Tip In our Digital Guide you will learn everything you need to know about MariaDB. For example, we explain how to use the [MariaDB CREATE USER](https://www.ionos.com/digitalguide/hosting/technical-matters/mariadb-create-user/) command. You will also find a detailed [comparison of MariaDB and MySQL](https://www.ionos.com/digitalguide/hosting/technical-matters/mariadb-vs-mysql/) as well as everything you need to know about [installing MariaDB](https://www.ionos.com/digitalguide/hosting/technical-matters/install-mysql-mariadb/).


This is a markdown version of: [https://www.ionos.com/digitalguide/hosting/technical-matters/mariadb-create-table/](https://www.ionos.com/digitalguide/hosting/technical-matters/mariadb-create-table/) for AI/LLM consumption.