Introduction to MariaDB create table.
MariaDB provides a create table statement to the user. in which we are able to create a table with the assigned name of the table. Normally in creating a table statement, create a table statement followed by table name with column name and data type. We have different options to create a table that means where we want to store the table, user defined database or default database that depends on the user. Id we need to specify the database then we can use (database name. Specified table name). When we specify the table name between a single quote, we must quote the database name in a single quote; this especially useful when the table contains data from another database; for that purpose, we create table…… and select statement. If the table name is already present in the database then it shows an error message.
Syntax
create table table_name (colm name 1 data type(size), colm name 2 data type(size), colm name N data type(size));
Explanation:
In the above syntax, we create table statement to create table, in which that table_name means user-specified table name that needs to be created then followed by column name with data type and size as shown in the above syntax.
How to create a table in MariaDB?
Let’s see how to create table statements that work in MariaDB as follows. Some things we must know at the time of table creation or replace as mentioned below:
- If we need to create with the same name, if the table name is excited, then we must need to drop that table first; then, we can create a table with the required name.
- If we use, IF NOT EXISTS clause at the time of table creation, then only indexes will be created. If the index is already exited then it shows a warning
- If we need to create a temporary table for a particular time period, then we use a temporary keyword at the time of table creation; after the end of the session table automatically dropped. The name of the temporary table is specified to a particular. This functionality is compatible only with the current version of MariaDB.
- Another way to create tables is by using LIKE clause, if we need to create the same table from another definition of the table at that time, we use the LIKE
- We can use NOT NULL option and it is used to specify the value of a particular column may or may not be
- Default option is also available or we can say the default option we can use at the time of table creation. When we assign default property to specified column that means we can specify default value to that column.
- MariaDB also provides an auto_increment option to users. When we use the auto_increment option, MariaDB automatically generates sequential integer numbers for each insertion operation.
- primary key option user can use primary key option at the time of table creation. It used to define a unique index on a specific column unless the specified column is not null.
- MariaDB also provides a unique key option. The use of this is that to specify all values of specified columns must be distinct from each other. Unless the specified column name is not null.
Example
Let’s try to understand how we can use create table statements in MariaDB with the help of examples as follows.
create table product(
product_id int auto_increment,
product_name varchar(255) not null,
product_cost decimal(15,2) not null,
primary key (product_id));
Explanation
In the above example, we created a table name as a product with different attributes. The product table has a 3 column as shown in the above statement.
The product_id is an integer column with auto_increment property, so MariaDB will automatically increment a sequential number when we insert a new row into the table. In addition, the product_id column is a primary key specified by using the primary key constraint as shown at the end of the statement, and the primary key constraint is useful to identify unique rows in the table.
product_name is a variable-length character with a maximum size of the character. The product_name has a not-null constraint that means we cannot insert null values into this column.
product_cost is a decimal column it also has not null property.
The result of the above statement we illustrate by using the following snapshot.
Let’s see how we can create tables with foreign key constraints as follows.
create table company(
company_id int auto_increment,
product_id int,
company_name varchar(255) not null,
start_date date not null,
address varchar(255) not null,
primary key(company_id, product_id),
foreign key(product_id)
references product(product_id)
);
Explanation
In the above example, we created the table by using the create statement here; we created the table name as the company with different attributes as shown in the above statement.
The company table consists of two primary keys such as company_id and product_id that means the company table does not exist without the product table.
The company table has a five-column as follows.
company_id is an integer column, and it has auto_increment property; therefore, MariaDB will automatically create a sequential number when we perform insert operation, and company_id and product_id is a primary key specified by primary key constraint.
company_name is a variable-length character with a maximum size of characters, and it has not null property.
start_date is a date column. It only accepts dates and it has not null property.
the address is the variable-length character with a maximum size of characters, and it has not null property.
The above relationship product table may have more than one company while the company belongs to only one product. This is known as a one-to-many relationship.
The result of the above statement we illustrate by using the following snapshot.
Conclusion
We hope from this article you have understood about the MariaDB create table statement. From the above article, we have learned the basic syntax of MariaDB create table statements, and we also see different examples of create table statements. We also learned how create table statement works in MariaDB. From this article we learned how and when we use MariaDB create table statements.
Recommended Articles
This is a guide to MariaDB create table. Here we discuss the basic syntax of MariaDB create table statements, and we also see different examples of creating table statements. You may also have a look at the following articles to learn more –
