How to Create Table in MariaDB

In this MariaDB tutorial, we will discuss how to create a table in MariaDB. Let us see different examples of MariaDB create table.

We will create a new table in the MariaDB database and cover the following topics.

• MariaDB create table example
• How to create table with foreign key in MariaDB
• MariaDB create table auto_increment
• MariaDB create table from another table
• MariaDB create table with primary key
• How to create table with index in MariaDB
• MariaDB create table date format
• How to create table multiple primary key in MariaDB

How to create a table in MariaDB

The statement that is used to create a new table in MariaDB is ‘CREATE TABLE tbl_name’, but there are some options that are used with this statement is given below.

CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...) [table_options    ]... [partition_options]

CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)] [table_options   ]... [partition_options]
    select_statement
CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
   { LIKE old_table_name | (LIKE old_table_name) }

The CREATE TABLE statement or command gives the table name followed by the list of columns, indexes, and constraints.

The options with the CREATE TABLE statement is OR REPLACE, TEMPORARY, IF NOT EXISTS.

• OR REPLACE: This options drop the existing table and create a new table.
• IF NOT EXISTS: This option is used to suppress the error 1050, which is generated when the table is created of the same name that already exists.
• TEMPORARY: This option is used to create the temporary table that exists till the current session, when the current session ends then the table is dropped automatically.

Let’s create a new table named maria_table using the below command.

CREATE TABLE maria_db(data_id int,data_name varchar(20));
show tables;

In the above code, the maria_db table is created using the CREATE TABLE statement, this table contains two columns named data_id of int data type and data_name of varchar data type.

Use the show tables command to list all the tables in the current database in MariaDB.

This is how to create a table in MariaDB.

Read How to Create Database in MariaDB

Create table with foreign key in MariaDB

The foreign key is made up of a column or set of columns in the MariaDB table that denotes a column or a set of columns in other tables.

A table that contains the foreign keys is called a child table and the table to which the foreign key denotes is called the parent table.

Create the table named product_table.

CREATE TABLE product_table(product_id int auto_increment,product_name varchar(100),primary key(product_id));

Here, we are creating the table as product_table using the CREATE TABLE statement with two columns named product_id and product_name of data type int and varchar respectively. Where product_id is the primary key.

Now again, create the table named prouduct_type with a foreign key.

CREATE TABLE product_type(product_type_id int auto_increment,product_id int,product_typ_name varchar(50),
foreign key(product_id) references product_table(product_id),primary key(product_type_id));

In the above code, we are creating a table named product_type with three columns named product_type_id, proudcut_id, product_typ_name. Where product_id is the foreign key that references to column prouduct_id of product_table.

This is how to create a table with a foreign key in MariaDB.

Read How to Grant User Access to a MariaDB Database

MariaDB create table auto_increment

The auto_increment is an attribute that is used to generate the unique identity for new rows automatically in MariaDB. It should be defined as a key and each table can contain only one auto_increment.

When we insert the new record in the table, the unique identity column or the column which is defined as auto_increment increments automatically from 1 by default.

Let’s check with the example.

CREATE TABLE auto_country(id int auto_increment,country_name varchar(100),primary key(id));

Here, we are creating the table as auto_country with columns id, country_name, and where id is defined as the auto_increment and primary key of the table using the CREATE TABLE statement.

Let’s insert the records in the table.

INSERT INTO auto_country(country_name)VALUES('United Kindom'),('Cannada'),('New Zealand'),('United States'),('Austrailia');

As we can see in the above output and the code, In the code we haven’t provided any id values but in the output, it has been generated automatically due to the auto_increment attribute.

This is an example of Mariadb create table auto_increment.

Read How to create a user in MariaDB

Create table from another table in MariaDB

The CREATE TABLE statement in MariaDB is used to create the new table from the existing MariaDB table, the full syntax is given below.

CREATE TABLE [ IF NOT EXISTS ] new_tble_name [ AS ] 
  SELECT expressions
  FROM name_existing_tables
  [WHERE conditions];

• new_tble_name: It is the name of the new table that we want to create.
• expressions: It is the columns name that is copied from the existing table.
• name_existing_tables: It is the name of the old or existing table from where the columns will be copied.

In the above sub-topic, we have created the table name auto_country that contains the name of some countries. Now we will copy or create the new table named countries from the existing table auto_country.

CREATE TABLE countries SELECT id,country_name FROM auto_country;

Here, we are creating the new table as countries from an existing table auto_country with the columns id, country_name using the CREATE TABLE statement.

To see the table data use the below query.

SELECT * FROM countries;

The above output shows every information or data from the table auto_increment in the new table countries.

This is how to create a table from another table in MariaDB.

Read How To Check MariaDB Version

MariaDB create table with primary key

Here, we are going to create a table with the primary key, the primary key is the unique key for each record in the table of MariaDB.

The primary key is like a vehicle identification number or telephone number. Every relational database should have only one and one primary key.

The syntax to create a primary key is given below.

CREATE TABLE table_name(col_1 int,col_2 varchar, PRIMARY KEY(col_1));

In the above syntax, we are creating the table as table_name using the CREATE TABLE statement with two columns col_1, col_2 of data type int and varchar respectively.

After defining columns name and their data type, the primary key is defined using the keyword PRIMARY KEY (name of the column) like here we make col_1 as the primary key of the table table_name.

Let’s create a table with a primary key using the below query.

CREATE TABLE prim_key(id int,data_name varchar,PRIMARY KEY(id));

Here in the above output, we have created the table as prim_key with two columns id, data_name of data type int and varchar respectively.

And the primary key is column id which is defined as PRIMARY KEY(id). So whenever a new record will be inserted each record is going to have a unique id.

This is how to create a table with a primary key in MariaDB.

Read MariaDB Vs SQL Server

MariaDB create table with index example

The index is used in MariaDB to get information or data very quickly or in an optimized way. Without the index, the accessed or searched information time will be different from the query that uses the index.

The index is created using the CREATE INDEX command in the MariaDB, the full syntax is given below

CREATE INDEX name_of_index
ON name_of_table(name_of_columns);

Where name_of_index is the new index name,name_of_table is the table name where columns exist and name_of_columns is the column name that we want to make as an index.

Now, create the new index on id columns of table prim_key that we have created in the previous sub-topic.

CREATE INDEX id_index ON prim_key(id);

Here, we are creating the index named id_index on column id of table prim_key using the CREATE INDEX statement.

To list the indexes of the table run the below query.

show indexes from prim_key;

In the above output, Look at the column Key_name after running the ‘show indexes from prim_key’, it shows the name of the index (id_index) that we have created.

This is how to create a table with an index in MariaDB.

Read How to Add Column in MariaDB

MariaDB create table date format

The MariaDB allows us to input the date in the different formats while inserting the information, but it stores in an internal format or ISO standard format like YYYY-MM-DD.

Let’s create a table name maria_date using the below query.

CREATE TABLE maria_date(date_data date);

Now, insert the date in a different format using the below code.

INSERT INTO maria_date VALUES 
("2022-01-12"), 
("2015-2-28"), 
('120317'),
('13*05*21');

In the above query or code, we are inserting date in different format like (“2022-01-12”), (“2015-2-28”), (‘120317′), (’13*05*21’).

Here in the above output, all the dates in different formats is stored in the same format or internal format.

Read How to Drop Column from MariaDB Table

MariaDB create table multiple primary key

In this section, we will create multiple primary keys using the keyword PRIMARY KEY in MariaDB. This kind of key is also called composite key because it is based on multiple fields.

Remember a table can have only one primary key, so here we will create only one primary key, but it will be based on multiple columns because a table can not have two primary keys.

Let’s create a table with multiple primary keys.

CREATE TABLE product_data(id int,product_type varchar(10),product_name varchar(10), PRIMARY KEY(id,product_type));

In the above code, we are creating the table as product_data with three columns id, product_type, product_name of data type int, varchar, and varchar accordingly. The columns id and product_type is the primary key of the table.

This is how to create a table with multiple primary keys in MariaDB.

You may like the following MariaDB tutorials.

• Replace Function in MariaDB
• How to Create View in MariaDB
• MariaDB query examples
• MariaDB Full Outer Join
• MariaDB Date Function

So in this MariaDB tutorial, we have learned about “MariaDB create a table” and covered the following topics.

• MariaDB create table example
• MariaDB create table foreign key
• How to create table auto_increment in MariaDB
• MariaDB create table from another table
• How to create table with primary key in MariaDB
• MariaDB create table with index example
• How to create table date format in MariaDB
• MariaDB create table multiple primary key