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 DatabaseStep 2: Then click on Create new -> Table.
Enter table nameStep 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 columnsAnother 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:
TablesExplanation: 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 KeyExplanation: 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 PropertyExplanation: 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 IndexExplanation: 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 KeyExplanation: 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.
Similar Reads
Alter Table in MariaDB
MariaDB is an open-source RDBMS, that offers an extensive collection of features for handling database structures effectively. One important part of database management is changing tables to meet needs or improve performance. The ALTER TABLE command in MariaDB assists in these changes, allowing user
5 min read
Drop Table in MariaDB
MariaDB is one of the most widely used open-source relational database management systems, it offers many useful commands for manipulating database structures. It supports the same features that MySQL does but with additional features. Some of the most significant features are new storage engines, J
4 min read
MariaDB Create View
Views in MariaDB are powerful tools that allow us to create virtual tables based on the result set of a SELECT query. They provide a way to simplify complex queries, improve security by limiting access to certain columns,, and enhance performance by precomputing expensive operations. In this article
5 min read
MariaDB Create Triggers
Triggers are a very useful and powerful feature of MariaDB. It is a database object associated with a table that activates if an INSERT, UPDATE or DELETE operations are performed. The name itself reflects their action as they run immediately without any human intervention when the respective operati
6 min read
Show Tables in MariaDB
MariaDB is an open-source relational database management system (RDBMS). MariaDB is a very successful RDBMS that is known for its performance, scalability, and ease of use. When dealing with databases, understanding the structure and organization of their table type becomes important. MariaDB provid
4 min read
WHERE Clause in MariaDB
MariaDB uses SQL (Structured Query Language) and it is an open-source relational database management system (RDBMS) for managing and manipulating data. The WHERE clause in SQL queries is used to filter and obtain specific data. The ability to remove and retrieve specific data using the WHERE clause
3 min read
SET Variable in MariaDB
In MariaDB, the SET statement is a main tool in variable handling. Users can assign values to variables, operate with them, and control database operations in various respects. This article includes a look at the SET variable usage in MariaDB and its syntax together with some examples. SET Variable
4 min read
Declaring Variable in MariaDB
Variables serve as essential components in database management systems like MariaDB facilitating the storage and manipulation of data within the database environment. Variable declaration in MariaDB is a fundamental concept, allowing users to store values temporarily, perform calculations, and strea
4 min read
MySQL CREATE TABLE
Creating tables in MySQL is a fundamental task for organizing and managing data within a database. Tables act as structured containers, similar to spreadsheets, where data is stored in rows and columns. In this article, we will explore the process of creating tables in MySQL using both the Command L
4 min read
Unique Constraint in MariaDB
MariaDB uses relational databases similar to SQL. SQL is the precedence of MariaDB similar to the SQL mariaDB also uses some of the querying language to process through the databases. The main advantage of the MariaDB is that it runs on various operating systems and supports many programming languag
7 min read