{"id":438,"date":"2019-10-19T11:41:03","date_gmt":"2019-10-19T04:41:03","guid":{"rendered":"https:\/\/mariadbtutorial.com\/?page_id=438"},"modified":"2020-04-11T23:24:03","modified_gmt":"2020-04-11T16:24:03","slug":"mariadb-create-table","status":"publish","type":"page","link":"https:\/\/www.mariadbtutorial.com\/mariadb-basics\/mariadb-create-table\/","title":{"rendered":"MariaDB Create Table"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the MariaDB create table<\/code> statement to create a new table in a database.<\/p>\n\n\n\n

Introduction to MariaDB create table statement<\/h2>\n\n\n\n

The create table<\/code> statement allows you to create a new table in a database.<\/p>\n\n\n\n

Here is the basic syntax of the create table<\/code> statement:<\/p>\n\n\n

create<\/span> table<\/span> [if<\/span> not<\/span> exists<\/span>] table_name(\n    column_1_definition,\n    column_2_definition,\n    ...,\n    table_constraints\n) engine<\/span>=storage_engine;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
  • First, specify the name of the table that you want to create after the create table<\/code> keywords. The table name must be unique within a database.<\/li>
  • Second, use the if not exists<\/code> option to conditionally create the new table only if it does not exist.<\/li>
  • Third, specify a list of columns for the table within the parentheses, the columns are separated by commas (,).<\/li>
  • Fourth, specify table constraints like primary key, foreign key, check, and unique after the column list.<\/li>
  • Finally, optionally specify a storage engine for the table in the engine<\/code> clause. If you skip the storage engine, MariaDB will use the InnoDB by default.<\/li><\/ul>\n\n\n\n
    MariaDB has made InnoDB as the default storage engine since version 10.2. The InnoDB is a good transaction storage engine that supports ACID, referential integrity, and crash recovery.<\/p>\n\n\n\n
    column definition<\/h3>\n\n\n\n
    To define a column for a table, you use the following syntax:<\/p>\n\n\n
    column_name data_type(length) [not null] [default value] [auto_increment] column_constraint;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    In this syntax:<\/p>\n\n\n\n
    • First, specify the name of the column.<\/li>
    • Next, specify the data type and maximum length of the column if the data type requires it.<\/li>
    • Then, use the not null<\/code> to enforce non-null values in the column. Besides the not null constraint, you can use the check and primary key column constraints for the column.<\/li>
    • After that, use the default value<\/code> clause to specify a default value for the column when the insert and update statements do not explicitly specify it.<\/li>
    • Finally, use the auto_increment<\/code> property to instruct MariaDB to implicitly generate sequential integers for the column. A table has one and only one column with the auto_increment<\/code> property.<\/li><\/ul>\n\n\n\n
      or replace option<\/h3>\n\n\n\n
      The create table<\/code> statement has an or replace<\/code> option:<\/p>\n\n\n
      create<\/span> [or<\/span> replace<\/span>] table_name (\n    ...\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      The or replace<\/code> option drops the table if it exists and creates a new one. It is a shortcut for the following sequence of statements:<\/p>\n\n\n
      drop<\/span> table<\/span> if<\/span> exists<\/span> table_name;\n\ncreate<\/span> table<\/span> table_name(\n    ...\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      Note that you cannot use the or replace<\/code> and if not exists<\/code> option at the same time.<\/p>\n\n\n\n
      MariaDB create table statement examples<\/h2>\n\n\n\n
      Let’s take some examples of using the create table<\/code> statement.<\/p>\n\n\n\n
      A) Using MariaDB create table statement to create a simple table example<\/h3>\n\n\n\n
      The following example uses the create table<\/code> statement to create a new table called projects<\/code>:<\/p>\n\n\n
      create<\/span> table<\/span> projects(\n    project_id int<\/span> auto_increment,\n    project_name varchar<\/span>(255<\/span>) not<\/span> null<\/span>,\n    begin_date date<\/span>,\n    end_date date<\/span>,\n    cost<\/span> decimal<\/span>(15<\/span>,2<\/span>) not<\/span> null<\/span>,\n    created_at timestamp<\/span> default<\/span> current_timestamp<\/span>,\n    primary key<\/span>(project_id)\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
      The projects<\/code> table has six columns:<\/p>\n\n\n\n
      • The project_id<\/code> is an integer column. It has the auto_increment<\/code> property, therefore, MariaDB will automatically generate a sequential number when you insert a row into the table. In addition, the project_id<\/code> column is the primary key specified by the primary key constraint at the end of the table. It means that a value in the project_id<\/code> column will uniquely identify a row in the table.<\/li>
      • The project_name<\/code> is a variable-length character with a maximum size of 255 characters. Because it has a not null<\/code> constraint, you cannot insert null<\/code> values into this column.<\/li>
      • The begin_date<\/code> and end_date<\/code> are the date columns. They accept dates and null values only.<\/li>
      • The cost<\/code> is a decimal column that also does not accept null values.<\/li>
      • The created_at<\/code> column is a timestamp column that accepts date and time values. In addition, its default value is the current timestamp of the operating system on which the MariaDB server runs.<\/li><\/ul>\n\n\n\n
        Because we don’t explicitly specify the storage engine for the projects table, it takes InnoDB as the storage engine.<\/p>\n\n\n\n
        B) Using MariaDB create table statement to create a table with a foreign key constraint<\/h3>\n\n\n\n
        The following example uses the create table<\/code> statement to create a new table called milestones<\/code>:<\/p>\n\n\n
        create<\/span> table<\/span> milestones(\n    milestone_id int<\/span> auto_increment,\n    project_id int<\/span>,\n    milestone varchar<\/span>(255<\/span>) not<\/span> null<\/span>,\n    start_date date<\/span> not<\/span> null<\/span>,\n    end_date date<\/span> not<\/span> null<\/span>,\n    completed bool<\/span> default<\/span> false<\/span>,\n    primary key<\/span>(milestone_id, project_id),\n    foreign<\/span> key<\/span>(project_id)\n        references<\/span> projects(project_id)\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
        In the milestones<\/code> table, the primary key consists of two columns milestone_id<\/code> and project_id<\/code> specified by the following table constraint:<\/p>\n\n\n
        primary key(milestone_id, project_id)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
        It means that a milestone will not exist without a project.<\/p>\n\n\n\n
        The project_id<\/code> column is the foreign key column that references the project_id<\/code> column of the projects<\/code> table:<\/p>\n\n\n
        foreign key(project_id)\n    references projects(project_id)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
        The following diagram illustrates the relationship between projects and milestones tables:<\/p>\n\n\n\n
        In this relationship, a project may have one or more milestones while each milestone belongs to only one project. This relationship is called the one-to-many.<\/p>\n\n\n\n
        In this tutorial, you have learned how to use MariaDB create table<\/code> statement to create a new table in the database.<\/p>\n
        \n\t
        \n\t\t
        Was this tutorial helpful ?<\/div>\n\t\t
        \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
        \n\t\t
        \n\t\t\t
        <\/div>\n\t\t\t\n\t\t\t