EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

MariaDB create table

Home » Data Science » Data Science Tutorials » Database Management Tutorial » MariaDB create table

MariaDB create table

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

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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:

  1. 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.
  2. 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
  3. 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.
  4. 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
  5. We can use NOT NULL option and it is used to specify the value of a particular column may or may not be
  6. 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.
  7. 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.
  8. 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.
  9. 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.

MariaDB create table output 1

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.

Popular Course in this category
Sale
SQL Training Program (7 Courses, 8+ Projects)7 Online Courses | 8 Hands-on Projects | 73+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (9,309 ratings)
Course Price

View Course

Related Courses
PL SQL Training (4 Courses, 2+ Projects)Oracle Training (14 Courses, 8+ Projects)

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.

MariaDB create table output 2

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 –

  1. Database Management Software
  2. MySQL Replication
  3. SQL MOD()
  4. PostgreSQL Log Queries

All in One Data Science Bundle (360+ Courses, 50+ projects)

360+ Online Courses

50+ projects

1500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
Database Management Tutorial
  • MariaDB
    • MariaDB Versions
    • MariaDB?list users
    • MariaDB Commands
    • MariaDB odbc
    • MariaDB Workbench
    • MariaDB for windows
    • MariaDB Server
    • MariaDB? Data Types
    • MariaDB?boolean
    • MariaDB phpMyAdmin
    • MariaDB Mysqldump
    • MariaDB Java Connector
    • MariaDB insert
    • MariaDB UPDATE
    • MariaDB? rename column
    • MariaDB AUTO_INCREMENT
    • MariaDB Timezone
    • MariaDB GROUP_CONCAT
    • MariaDB wait_timeout
    • MariaDB MaxScale
    • MariaDB? with
    • MariaDB? create?table
    • MariaDB? SHOW TABLES
    • MariaDB alter table
    • MariaDB List Tables
    • MariaDB JSON Functions
    • MariaDB Foreign Key
    • MariaDB? trigger
    • MariaDB Grant All Privileges
    • MariaDB Select Database
    • MariaDB? create database
    • MariaDB Delete Database
    • MariaDB List Databases
    • MariaDB Functions
    • MariaDB? TIMESTAMP
    • MariaDB create user
    • MariaDB add user
    • MariaDB show users
    • MariaDB Delete User
    • MariaDB? change user password
    • MariaDB? change root password
    • MariaDB reset root password
    • MariaDB IF
    • MariaDB bind-address
    • MariaDB Transaction
    • MariaDB Cluster
    • MariaDB Logs
    • MariaDB Encryption
    • MariaDB? backup
    • MariaDB Replication
    • MariaDB max_allowed_packet
    • MariaDB? performance tuning
    • MariaDB export database
    • MariaDB? import SQL
  • DataBase Management
    • Text Data Mining
    • Roles of Database Management System in Industry
    • SQL Server Database Management Tools
    • Databricks CLI
    • Database administrator skills
    • Database Management Systems Advantages
    • Database Testing Interview Questions
    • Data Administrator
    • Database Administrator
    • Database Management Software
    • DataStage
    • Types of Database Models
    • Types of Database
    • Hierarchical Database Model
    • Relational Database
    • Relational Database Advantages
    • Operational Database
    • What is RDBMS?
    • What is DB2?
    • Data Masking Tools
    • Database Security
    • Data Replication
    • Bitmap Indexing
    • Second Normal Form
    • Third Normal Form
    • Fourth Normal Form
    • Data Definition Language
    • Data Manipulation Language
    • Data Control Language
    • Transaction Control Language
    • Conceptual Data Model
    • Entity-Relationship Model
    • Relational Database Model
    • Sequential File Organization
    • Checkpoint in DBMS
    • Mapping Constraints in DBMS
    • Teradata Create Table
    • Centralized Database
    • Data Storage in Database
    • Thomas write Rule
    • DBA Interview Questions
    • What is JDBC?
    • jdbc hive
    • Apriori Algorithm
    • JDBC Architecture
    • JDBC Interview Questions
    • Wildcard Characters
    • Distributed Database System
    • Multidimensional Database
  • PL/SQL
    • What is PL/SQL?
    • Careers in PL/SQL
    • PLSQL procedure
    • PL/SQL Exception
    • PL/SQL LIKE
    • PL/SQL Raise Exception
    • PLSQL rowtype
    • PLSQL? bind variables
    • PL/SQL Record
    • PL/SQL WITH
    • PL/SQL bulk collect
    • PL/SQL Block Structure
    • PL/SQL else if
    • PL/SQL nvl2
    • PL/SQL Package
    • PL/SQL exists
    • PL/SQL instr
    • PL/SQL listagg
    • PL/ SQL Formatter
    • PLSQLlength
    • PL/SQL Commands
    • PL/SQL Data Types
    • CASE statement in PL/SQL
    • PL/SQL IF Statement
    • Loops in PL/SQL
    • PL/SQL Add Column
    • For Loop in PLSQL
    • PL/SQL Cursor Loop
    • PLSQL Array
    • Cursors in PL/SQL
    • PL/SQL FOR Loop Cursor
    • PL/SQL Queries
    • PL/SQL SELECT INTO
    • PL/SQL TO_CHAR
    • PL/SQL UNION
    • PL/SQL NOT EQUAL
    • PL/SQL varray
    • PL/SQL Concatenate
    • PL/SQL UPDATE
    • PL/SQL TRIM
    • PL/SQL GROUP BY
    • PL/SQL GOTO
    • PL/SQL Date Functions
    • PL/ SQL having
    • PL/SQL to_DATE
    • PL/SQL NVL
    • PLSQL format date
    • PLSQL mod
    • PLSQL round
    • PL/SQL Boolean
    • PL/SQL exit
    • PL/SQL DECODE
    • PL/SQL ROWNUM
    • PLSQL?pivot
    • PLSQL string functions
    • PL/SQL Block
    • PL/SQL Function
    • PL/SQL Unwrapper
    • PL/SQL Table
    • PL/SQL ALTER TABLE
    • PLSQL execute immediate
    • Triggers in PL/SQL
    • PL/SQL Collections
    • PL/SQL stored procedure
    • PL/SQL Anonymous Block
    • PLSQL Interview Questions
  • TSQL Basic
    • TSQL
    • What is T-SQL
    • T-SQL Commands
    • T-SQL String Functions
    • TSQL Interview Questions
  • SQLite
    • What is SQLite
    • SQLite Commands
    • SQLite Data Types
    • SQLite COUNT
    • SQLite Boolean
    • SQLite autoincrement
    • SQLite select
    • SQLite? Bulk Insert
    • SQLite? add column
    • SQLite? concat
    • SQLite BETWEEN
    • SQLite group by
    • SQLite CASE
    • SQLite group_concat
    • SQLite array
    • SQLite? enum
    • SQLite sum
    • SQLite create table
    • SQLite Alter Table
    • SQLite Create Database
    • SQLite Delete
    • SQLite connection string
    • SQLite Database
    • SQLite Describe Table
    • SQLite Show Tables
    • SQLite exit
    • SQLite create index
    • SQLite foreign key
    • SQLite Stored Procedures
    • SQLite Extension
  • DB2
    • DB2? current date
    • DB2 purescale
    • DB2 backup
    • DB2 restore
    • DB2 C Express
    • DB2 Version
    • DB2? Architecture
    • DB2? Data Types
    • DB2? load
    • DB2? order by
    • DB2 date
    • DB2 NVL
    • DB2? update
    • DB2 warehouse
    • DB2 grant
    • DB2 database
    • DB2 VARCHAR
    • DB2? INSERT
    • DB2 LISTAGG
    • DB2 LIKE
    • DB2 TRUNCATE TABLE
    • DB2 LIST TABLES
    • DB2 between
    • DB2? current timestamp
    • DB2? length
    • DB2? bind
    • DB2 limit rows
    • DB2? export
    • DB2 with
    • DB2 Create Table
    • DB2 case statement
    • DB2 CAST
    • DB2 Functions
    • DB2 Date Functions
    • DB2? row_number
    • DB2 trim
    • DB2? Translate
    • DB2 UNION
    • DB2 timestamp
    • DB2? TIMESTAMPDIFF
    • DB2? replace
    • DB2 merge
    • DB2 COALESCE
    • DB2 ISNULL
    • DB2? explain
    • DB2 Join
    • DB2 alter column
    • DB2 rename column
    • DB2? Describe Table
    • DB2? rename table
    • DB2 List Databases
    • DB2 LUW
    • DB2 Query
    • DB2 GROUP BY
    • DB2 TO_DATE
    • View Serializability in DBMS
    • MariaDB Join
    • MariaDB JSON
    • MariaDB? show databases
    • Dataset Normalization
    • MariaDB Max Connections
    • jdbc connection
    • MariaDB GUI
  • DBMS
    • Introduction To DBMS
    • DBMS ER Diagram
    • What is DBMS?
    • DBMS join
    • DBMS Functions
    • Data Administrator in DBMS
    • DBMS Canonical Cover
    • DBMS Log-Based Recovery
    • DBMS Multivalued Dependency
    • Netezza Database
    • DBMS Concepts
    • DBMS Constraints
    • DBMS_Scheduler
    • B+ Tree in DBMS
    • DBMS_LOB
    • dbms entity
    • DBMS Foreign Key
    • DBMS Users
    • DBMS_Metadata.get_ddl
    • Relational Algebra in DBMS
    • DBMS Components
    • DBMS Features
    • DBMS Models
    • DBMS Relational Model
    • Hashing in DBMS
    • DBMS network model
    • Relationship in DBMS
    • ER Model in DBMS
    • Data Models in DBMS
    • Static Hashing in DBMS
    • Advantages of DBMS
    • dbms_output.put_line
    • DBMS Data Dictionary
    • dbms_xplan.display_cursor
    • Normal Forms in DBMS
    • DBMS helps achieve
    • DBMS 3 tier Architecture
    • Relational Calculus in DBMS
    • Serializability in DBMS
    • File Organization in DBMS
    • DBMS Transaction Processing
    • States of Transaction in DBMS
    • Functional Dependency in DBMS
    • Generalization in DBMS
    • Data Independence in DBMS
    • Lock Based Protocols in DBMS
    • Deadlock in DBMS
    • Integrity Constraints in DBMS
    • Concurrency Control in DBMS
    • Validation Based Protocol in DBMS
    • DBMS Locks
    • Normalization in DBMS
    • Transaction Property in DBMS
    • Specialization in DBMS
    • Aggregation in DBMS
    • Types of DBMS

Related Courses

SQL Certification Course

PL/SQL Certification Course

Oracle Certification Course

Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

Special Offer - SQL Certification Course Learn More