How to Create Table in MySQL Using if not Exists?

In this article, we shall learn how to create a table if it does not exist. While working with MySQL, the key component of the RDBMS system is the database. In each of these databases are various tabs over which one can run the MySQL queries and get the desired output. Here, in this article, we shall be learning how to create a table if it does not exist MySQL.

With the help of the CREATE TABLE statement, the user can easily get started with creating new tables in a database.

Syntax

The syntax to the CREATE TABLE statement when you want to create a table if it does not exist MySQL is shown below:

Parameters

Let's dive deep into the various parameters used in the syntax above.

• The user can start by defining the table with a name. This can be easily done with the help of the CREATE TABLE statement keywords. It is required to always have a distinct name given to a table inside a database.
• The IF NOT EXISTS constraint is entirely optional. With that constraint, one can pre-check if the same name table is already acting or not. It creates a table if not exists MySQL is not already existing; then, it allows users to validate if the table already resides within the database. Else, MySQL instantly ignores the entire statement and would not create any such new table within the database.
• Users can define the list (having columns) in the table with the column_list section. As you can observe above, the columns are segregated with the help of commas.
• The ENGINE constraint helps to define the storage engine. This is an optional constraint; if one does not know or doesn't want to specify, one can easily skip this constraint.
• Widely popular storage engines are InnoDB and the MyISAM storage engine. It is the InnoDB storage engine that is taken into account by default by MySQL when a user does not explicitly declare a storage engine.
• From MySQL 5.5, InnoDB became the default storage engine. Various pros offered by the relational database management system are executed by the InnoDB storage engine.
• These include the features such as referential integrity, ACID transaction, and crash recovery.
• Before MySQL 5.5, the MyISAM storage engine was considered the default storage engine.

Syntax for column's definition:

Let us quickly dive into the syntax for the definition of columns when creating a table if it does not exist in mysql:

Parameters:

Let's dive deep into the various parameters that have been used in the syntax above.

• The user can use the column_name parameter to define the name of the column.
• It is mandatory that every column in a table has a defined data type added to it. The optional size is VARCHAR(255).
• With the NOT NULL clause, the user can make sure that the no NULL value is found in the column.
• Additional constraints including CHECK and UNIQUE can also be implemented besides the NOT NULL constraint.
• With the DEFAULT constraint, the user can define the default value ( or the value that would appear if nothing is determined) for the column if n other column is specified.
• The AUTO_INCREMENT constraint helps the user to indicate that the column value for every column in the table must get increased by one automatically. This must follow when a new row is getting added or inserted in the table. For each table, the maximum AUTO_INCREMENT column is one.
• Once the list of the column is defined, the user can specify the table constraints, including UNIQUE, PRIMARY KEY, FOREIGN KEY and CHECK.

For instance, when the user needs to set a bunch of columns or a column as the main primary key, it must implement the same with the below-defined syntax:

Now, let us start implementing the theoretical knowledge that we just learned to understand how to create a table if it does not exist.

MySQL CREATE TABLE Simple Example

In this example, we shall be learning how we can create a table if it does not exist in mysql with a simple example. First, we use the syntax learned above to create a table.

Code:

Output:

Explanation:

In the above code, we learn how we can easily create a table if it does not exist MySQL. The various columns that the table bucket_list has been described below:

• The bucket_id can be defined as a column that could be auto-incremented. With the help of the INSERT statement, the user can easily insert or add a new row in the table without defining any value for the bucket_id column.
• The MySQL shall then automatically create a consecutive integer for the bucket_id column initiating from 1.
• The bucket_activity_name column can be defined as the variable character having a string datatype column. It has a maximum length of up to 255 for the names in the bucket_activity_name column. When any user tries to enter a string with a length greater than 255, they might not be successful in doing so. Therefore, no string would be inserted.
• The NOT NULL constraint shows that no value in that specific column could be NULL. Therefore, when a user tries to INSERT a NULL value, it shall not accept it. Also, the user needs to implement a NOT NULL value when the user wants to update or insert the rows in that same column.
• The start_activity_date and due_activity_date are the two DATE columns. As these columns do not contain the NOT NULL clause, the user can insert or update any value with a NULL value. The start_activity_date column also has the default value set concerning the current date.
• When no value is provided for the start_activity_date column, and users try to add or insert a new row, the start_activity_date column shall take the present date from the database server.
• No NULL values are allowed for the current_status and set_priority. It is so, as both current_status and set_priority are having the datatype as TINYINT.
• In The bucket_description column, the user can only insert or update the TEXT column, which could also accept NULL.
• In the bucket_created_at column, the user can add a value which is the TIMESTAMP datatype. It accepts the present time which also acts as the default value.
• The bucket_id acts as the primary key column as specified in the query above. This also means that values in the bucket_id column will always be distinctly identified rows in the bucket_list table.

Once the above MySQL query is executed, users will be able to see the table named bucket_list. All this is possible with the CREATE TABLE statement that created the bucket_list table. Next, one can view its structure via the DESCRIBE statement.

MySQL CREATE TABLE with a Foreign Key Primary Key Example

Let us dive into this second example, where we shall be CREATE TABLE with a foreign key primary key and learn how to create a table if not exists mysql.

Code:

Output:

The below illustration depicts the relationship shared between the bucket_list table and the assignment_list table:

Explanation:

• As seen in the above code, assignment one could be captured in another table named the assignment_list table. We create a table for storing the assignments of tasks; the user could create a new table called the assignment_list table, as seen.
• In this assignment_list table, the primary key consists of two columns. Primary keys having more than one column are also known as Composite keys.
• To do so, the user can implement a table constraint for defining the primary key. As seen in the code above, the same could be implemented - PRIMARY KEY (assignment_id , assignment_tobe_id).
• Moreover, the assignment_id acts as the foreign key column for the previous table - bucket_list. It references the bucket_id column of the first table - bucket_list.
• To define this relationship, the user could implement a foreign key constraint as shown below: FOREIGN KEY (assignment_id) REFERENCES bucket_list (bucket_id) ON UPDATE RESTRICT ON DELETE CASCADE
• Summing up, the bucket_id of the first table is the primary key for the bucket_list table. While the assignment_id is the primary key for the assignment_list table. Most importantly, the assignment_id is the foreign key for the bucket_list table. This way one can easily create a table if not exists mysql in the CREATE TABLE with a foreign key primary key example we just learned above.