Mysql Tableplus

Mysql bug postgresql feature tableplus 43 2,344 831 (9 issues need help) 3 Updated May 7, 2020. TablePlus-iOS TablePlus iOS issue tracker 0 3 2 0 Updated Nov 20, 2019. By TablePlus Inc A modern, native client with intuitive GUI tools to create, access, query & edit multiple relational databases: MySQL, PostgreSQL, SQLite, Microsoft SQL Server, Amazon Redshift.

Summary: in this tutorial, we will show you how to use the MySQL CREATE TABLE statement to create a new table in the database.

MySQL CREATE TABLE syntax

The CREATE TABLE statement allows you to create a new table in a database.

The following illustrates the basic syntax of the CREATE TABLE statement:

Docker mysql tableplus

Let’s examine the syntax in greater detail.

First, you specify the name of the table that you want to create after the CREATE TABLE keywords. The table name must be unique within a database. The IF NOT EXISTS is optional. It allows you to check if the table that you create already exists in the database. If this is the case, MySQL will ignore the whole statement and will not create any new table.

Second, you specify a list of columns of the table in the column_list section, columns are separated by commas.

Third, you can optionally specify the storage engine for the table in the ENGINE clause. You can use any storage engine such as InnoDB and MyISAM. If you don’t explicitly declare a storage engine, MySQL will use InnoDB by default.

InnoDB became the default storage engine since MySQL version 5.5. The InnoDB storage engine brings many benefits of a relational database management system such as ACID transaction, referential integrity, and crash recovery. In the previous versions, MySQL used MyISAM as the default storage engine.

The following shows the syntax for a column’s definition:

Here are the details:

  • Thecolumn_name specifies the name of the column. Each column has a specific data type and optional size e.g.,VARCHAR(255)
  • TheNOT NULL constraint ensures that the column will not contain NULL. Besides the NOT NULL constraint, a column may have additional constraint such as CHECK, and UNIQUE.
  • The DEFAULT specifies a default value for the column.
  • The AUTO_INCREMENT indicates that the value of the column is incremented by one automatically whenever a new row is inserted into the table. Each table has a maximum one AUTO_INCREMENT column.

After the column list, you can define table constraints such as UNIQUE, CHECK, PRIMARY KEY and FOREIGN KEY.

For example, if you want to set a column or a group of columns as the primary key, you use the following syntax:

MySQL CREATE TABLE statement examples

Let’s take some examples of creating new tables.

1) MySQL CREATE TABLE simple example

The following statement creates a new table named tasks:

The tasks table has the following columns:

  • The task_id is an auto-increment column. If you use the INSERT statement to insert a new row into the table without specifying a value for the task_id column, MySQL will automatically generate a sequential integer for the task_id starting from 1.
  • The title column is a variable character string column whose maximum length is 255. It means that you cannot insert a string whose length is greater than 255 into this column. The NOT NULL constraint indicates that the column does not accept NULL. In other words, you have to provide a non-NULL value when you insert or update this column.
  • The start_date and due_date are DATE columns. Because these columns do not have the NOT NULL constraint, they can store NULL. The start_date column has a default value of the current date. In other words, if you don’t provide a value for the start_date column when you insert a new row, the start_date column will take the current date of the database server.
  • The status and priority are the TINYINT columns which do not allow NULL.
  • The description column is a TEXT column that accepts NULL.
  • The created_at is a TIMESTAMP column that accepts the current time as the default value.
Mysql

The task_id is the primary key column of the tasks table. It means that the values in the task_id column will uniquely identify rows in the table.

Once you execute the CREATE TABLE statement to create the tasks table, you can view its structure by using the DESCRIBE statement:

This picture shows the database diagram of the tasks table:

2) MySQL CREATE TABLE with a foreign key primary key example

Suppose each task has a checklist or to-do list. To store checklists of tasks, you can create a new table named checklists as follows:

Mysql

The table checklists has a primary key that consists of two columns. Therefore, we used a table constraint to define the primary key:

In addition, the task_id is the foreign key column that references to the task_id column of the table tasks, we used a foreign key constraint to establish this relationship:

You will learn more about the foreign key constraint in the subsequent tutorial.

This picture illustrates the checklists table and its relationship with the tasks table:

Docker

Mysql Table Plus 2

In this tutorial, you have learned how to use MySQL CREATE TABLE statement to create a new table in the database.

TablePlus 3.12.8


Modern, native, and friendly GUI tool for relational databases: MySQL, PostgreSQL, SQLite & more. With native build, simple design and powerful features, TablePlus makes database management easier, faster & more efficient for you.
Native, Fast & Secured
Query, edit and manage your databases easily with a native app that can run fast like a Lambo.

Mysql Table Plus

It takes only half a second to get up and running.
Database security at its best
It’s equipped with many security features to protect your database, including multi-step authentication support, native libssh and TLS to encrypt your connection.
Customizable Appearance
Need a dark theme? You don’t need to go for Mojave, TablePlus has it all. You can customize the appearance & configurations that best match your personal taste.
MysqlAn intuitive GUI client
Browse, query, edit your data and database structure in a simple and clean spreadsheet-like editor.
Inline edit
Edit data rows, table structure, or query results directly with just a click.
Code Review & Safe Mode
Always stay in control of what you have changed on your database.
Export & import database
Need to migrate a database to another server?
Export and Import SQL Dump will get it done in no time.
Open anything
A quick jump to a table, schema, database, view, function, literally anything from your connection.
Multiple tabs & windows
Working with multiple databases or connections at the same time? Not a problem.
Only for V.I.P
Warning! You are not allowed to view this text.