Getting started with MariaDB – Create Table

Pre requirement: Before continuing to MariaDB – Create Table, make sure to have the MariaDB Server installed.

Let’s login to Mariadb Server first:

bluegrid-edu:~# mysql

CREATE command takes a set of arguments defining table name, columns, and their definition and optional database engine (InnoDB and MyISAM):

CREATE TABLE [IF NOT EXISTS] tablename (column1_definition, column2_definition,...) engine=storage_engine;

Note that [IF NOT EXISTS] is optional and it can be used in order to execute create command only if there is no table with the same name. If this condition is not set the command will end with an error like ERROR 1050 (42S01): Table 'test' already exists, otherwise, the command will end with success but with the warning like this Query OK, 0 rows affected, 1 warning (0.00 sec).

There is no too important preference on whether you should use this condition or not, it’s more of a personal preference and use case.

Example of creating table “users” in the database “testdb” with columns:

• id with auto-increment option
• Name (char field type consisting of 255 characters)
• Email (char field type consisting of 100 characters)
• Date and time of entry creation (timestamp field type)

Important notes:

The field types are to be defined per personal or use case conditioned scenarios. 

Every column is defined by data type and optional size (ex: char(255)). 

Autoincrement columns must be defined as a key (foreign or primary). If it happens that you don’t define this column as a key this is the error you’d get:

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

If the column must not contain the NULL value you can add an additional property like “name char(255) not null”. This will prevent the engine to store NULL values in the column “name”.

Now that we have learned all that 😁 here is the command format for Mariadb – Create Table:

MariaDB [testdb]> create table users (
id int auto_increment primary key, 
name char(255), 
email char(100), 
date timestamp default current_timestamp) 
engine=innodb;