CREATE TABLE, RENAME TABLE and DROP TABLE statements in MySQL

CREATE TABLE

The CREATE TABLE statement is used in MySQL to create a new table in a database. The syntax for this is shown below:

Syntax:

CREATE TABLE [IF NOT EXISTS] table_name(
column1_definition,
column2_definition,
...,
table_constraints
);

In the above syntax:

• table_name is the name of the table specified after the CREATE TABLE statement.
• IF NOT EXISTS is optional, and is used to ensure if any table exists with that name or not. MySQL only creates the table if there is no existing table with the same name else it ignores the statement and no table is created.
• After this, a list of columns is specified which includes column name and data type. Some other things can also be included but they are optional. Other things that can be included in the column definition are NOT NULL, AUTO INCREMENT, DEFAULT VALUE. Here, NOT NULL says that the value in the column can't be null, AUTO INCREMENT increases the value of that column every time a new row is inserted and DEFAULT specifies the default value for that column.
• After the table_constraints is specified, it is also optional. If we want to specify a column or a group of columns as Primary Key or Foreign Key, it is included in the table_constraints. For example -

CREATE TABLE IF NOT EXISTS student(
roll_no INT AUTO INCREMENT,
name VARCHAR(255) NOT NULL,
semester INT NOT NULL,
stream VARCHAR(255),
PRIMARY KEY(roll_no,semester)
); 

It can be seen from the above query, a table named ‘student’ is being created which has four columns roll_no, name, semester, and stream. The primary key of the table is the roll_no and semester combined. The table created using the above query will look like as shown below:

RENAME TABLE

The RENAME TABLE statement is used in MySQL to rename the existing tables. One or more tables can be renamed using this statement. 

Syntax:

RENAME TABLE old_table_name to new_table_name;

In the above syntax:

• The old_table_name must exist and the new_table_name must not exist.
• If a table with new_table_name already exists, the statement will fail.

Before renaming a table, its impact should be evaluated thoroughly. There may be many applications that are using the table and the rename of the table may affect them. So, the code of the application that refers to the table should also be changed. There are other database objects such as views, foreign key constraints that also get affected and they need to be adjusted manually.

If you run a query on a table, and we need to fetch similar data again in the future, we can save the query and assign a name to it to avoid rewriting the query again. This named query is called view. 

Now, if we rename the table and try to query data from the named view again. It will return an error message. This is because the query is written reference to the old name of the table and it needs to be changed manually to avoid the error. For example - 

Suppose there is a table having the name ‘students’. It can be renamed to employees as follows:

RENAME TABLE students to employees;

Renaming Multiple Tables using RENAME TABLE

We used RENAME TABLE statement to rename the single table. We can also rename multiple tables simultaneously using this. The syntax for this is shown below:

RENAME TABLE old_table_name1 to new_table_name1,
			 old_table_name2 to new_table_name2,
			 ...;

Renaming table using ALTER TABLE 

The ALTER TABLE statement is used to rename a temporary table. RENAME TABLE can't be used to rename temporary tables.

Syntax:

ALTER TABLE temporary_table_old_name
RENAME TO temporary_table_new_name;

In the above syntax:

• the old name of the temporary table is written after ALTER TABLE statement,
• the new name of the temporary table is written after RENAME TO.

DROP TABLE

The DROP TABLE statement is used in MySQL to drop or delete a table from the database. The syntax for this is shown below:

Syntax:

DROP [TEMPORARY] TABLE [IF EXISTS] table_name;

In the above syntax:

• TEMPORARY is optional after DROP, it can be used to ensure that no non-temporary tables get deleted.
• Similarly, IF EXISTS is optional. It is used to ensure that the table will be dropped only if it exists in the database. For example-

Suppose there is a table named vehicles, it can be dropped as follows:

DROP TABLE vehicles;

The above-written query will drop the vehicles table from the database.

The TEMPORARY and IF EXISTS statements are not used in the above query as they are optional.

Note: If we try to delete a non-existing table, MySQL will return an error.

Drop multiple tables using DROP TABLE 

Multiple tables can also be dropped simultaneously using the DROP TABLE statement. For example -

DROP TABLE table1,table2;

The above query will drop both table1 and table2. Similarly, more tables can be added separated by commas which we want to drop.