Create Table in MariaDB

MariaDB is an open-source RDBMS that has become famous for its speed, and scalability. MariaDB Stores data in tables with structured relationships between them. In terms of working with databases, one crucial element involves the construction of tables for organizing and storing data effectively. In this article, we will discuss creating tables in MariaDB and the available choices and factors that need to be considered.

Create a Table in MariaDB

Creating a table in MariaDB involves using the CREATE TABLE statement. The basic syntax is as follows:

Syntax:

CREATE TABLE table_name 
(
    column1 datatype,
    column2 datatype,
    ...
);

Explanation of Syntax:

• CREATE TABLE: This statement is used to create a new table.
• table_name: The name you give to the table.
• column1 datatype, column2 datatype: This is a column of the table, each with its name and data type.

Ways to Create Tables

Let’s create a table in a stepwise manner.

Step 1: Go to the selected database and right-click.

Select Database

Step 2: Then click on Create new -> Table.

Enter table name

Step 3: Give a name to our table and we can add columns also by clicking on plus icon before add button and also you can add other options like indexes, foreign keys and many more.

Add columns

Another Ways to Create Table

Let’s create a simple table to store information about workers.

Query:

CREATE TABLE workers 
(
    worker_id INT,
    first_name VARCHAR(50) not null,
    last_name VARCHAR(50),
    birth_date DATE,
    salary DOUBLE not null
);

Explanation: The workers table has five columns:

• The worker_id is an integer column.
• The first_name is a variable-length with a maximum size of 50 characters. Because it has a not NULL constraint, we cannot insert null values into this column.
• The last_name is also a variable-length with a maximum size of 50 characters but it accept NULL values.
• The birth_date is the date column. It accept dates and null values only.
• The salary is the double column that also accept NULL values.

Now let’s verify whether the table has created or not using below command:

Query:

SHOW TABLES;

Output:

Tables

Explanation: As we can see in the output image the workers table has been created.

Add Primary Key:

A primary key uniquely identifies each record in a table. It ensures that each row has a unique identifier. You can specify a primary key during table creation:

Query:

CREATE TABLE workers 
(
    worker_id INT PRIMARY KEY,
    first_name VARCHAR(50) not null,
    last_name VARCHAR(50),
    birth_date DATE,
    salary DOUBLE not null
);

Output:

Adding Primary Key

Explanation: Now we had modified the table by specifying worker_id as the primary key. It means that a value in the worker_id column will uniquely identify a row in the table.

Add Auto-Incrementing Primary Key:

To automatically generate unique values for the primary key, we can use the AUTO_INCREMENT attribute.

Query:

CREATE TABLE workers 
(
    worker_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birth_date DATE,
    salary DOUBLE
);

Output:

Adding Auto-Increment Property

Explanation: Now the worker_id column has the auto_increment property, therefore, MariaDB will automatically generate a sequential number when you insert a row into the table. The remaining columns are same.

Add Indexes:

Indexes improve the speed of data retrieval operations on a table. We can create indexes on one or more columns using the INDEX keyword:

Query:

CREATE TABLE employees 
(
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    birth_date DATE,
    salary DOUBLE,
    INDEX last_name_index (last_name)
);

Output:

Add Index

Explanation: The last line INDEX last_name_index (last_name) is creating an index on the last_name column of the employees table.

• INDEX: This indicates the type of the index that you are creating a regular index on the specified column.
• last_name_index: This is the name you want to give to the index. This is optional.
• (last_name): This is the column name on which index is being created.

Add Foreign key:

Foreign key ensures data integrity and consistency across related tables. They are the powerful tool for maintaining relationships between tables.

Query:

CREATE TABLE employees 
(
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    worker_id INT,
    emp_name VARCHAR(50),
    birth_date DATE,
    department_id INT,
    FOREIGN KEY (worker_id) REFERENCES workers(worker_id)
);

Output:

Adding Foreign Key

Explanation: In the employees table, the primary key consists of two columns employee_id and worker_id. It means that a employees will not exist without a worker. The worker_id column is the foreign key column that references the worker_id column of the workers table.

Conclusion

MariaDB offers a simple syntax for the creation of tables, which is considered an integral part of designing a relational database. Knowing the different alternatives, including the primary keys, data types, and indexes, help create tables catering to the unique needs of your application. As you advance your skills in database design and management, understanding how the tables are created becomes critical in building databases efficacious and scalable with MariaDB.