MariaDB Drop Table + Examples

In this MariaDB tutorial, we will study the use of the MariaDB DROP TABLE and we will also cover some examples. There are lists of the topic that comes under discussion:

• MariaDB drop table
• MariaDB drop table if exists
• MariaDB drop table ignore constraints
• MariaDB drop table hangs
• MariaDB drop table not working
• MariaDB drop table column
• MariaDB drop table warning
• MariaDB delete table data
• MariaDB delete table alias
• MariaDB delete table join

MariaDB Drop Table

The DROP TABLE statement will be explained with syntax and examples in this MariaDB tutorial.

The DROP TABLE statement in MariaDB is used to terminate or remove a table from the database.. The syntax of the MariaDB DROP TABLE statement is given below:

DROP TABLE TABLE_NAME;

FULL SYNTAX:

DROP [ TEMPORARY ] TABLE [ IF EXISTS ]
tbl_name1, tbl_name2, ...
[ RESTRICT | CASCADE ];

The syntax explanation:

• TEMPORARY: It states that the DROP TABLE statement should only be used to drop temporary tables and it is optional.
• TABLE_NAME: The name of the table from the database that we wish to delete.
• TBL_NAME1,TBL_NAME2: The table that we want to remove from the database, if there are more than one table in the DROP TABLE statement.
• IF EXISTS: [optional] If the IF EXISTS clause is used, the DROP TABLE statement will not throw an error if one of the tables does not exist.
• RESTRICT: [optional] It has no bearing on the DROP TABLE statement, however it is included in the syntax to make transferring tables between databases easier.
• CASCADE: [optional] It has no bearing on the DROP TABLE statement, however it is included in the syntax to make transferring tables between databases easier.

NOTE:

If we try to drop one or more tables that don’t exist with the MariaDB DROP TABLE query, the database will throw an error (unless we specify the IF EXISTS parameter in the DROP TABLE statement).

The following is an example of how to drop a table using the DROP TABLE statement:

EXAMPLE:

DROP TABLE USA_EARPHONEPRODUCT;

In this query, the DROP TABLE statement will drop a table called USA_EARPHONEPRODUCT.

Also, check: MariaDB Temporary Table

MariaDB Drop Table If Exists

This MariaDB tutorial walks you through the syntax and examples of using the MariaDB DROP TABLE statement with the IF EXISTS clause.

The only purpose of the IF EXISTS clause is that the DROP TABLE statement will not raise an error if one of the tables does not exist even if the IF EXISTS clause is provided.

The full syntax of the MariaDB DROP TABLE statement with the IF EXISTS clause is given below:

SYNTAX:

DROP TABLE [IF EXISTS] TABLE_NAME;

The sample example of the MariaDB DROP TABLE statement with the IF EXISTS clause is given below:

EXAMPLE:

DROP TABLE IF EXISTS USA_EARPHONEPRODUCT;

In this query, the DROP TABLE statement has dropped a table called USA_EARPHONEPRODUCT but if the table is already removed from the database and re-executing the query again by using the IF EXISTS clause then it will not throw an error.

Read: MariaDB Foreign Key

MariaDB Drop Table Ignore Constraints

We will understand how to drop the table by using the IGNORE CONSTRAINTS as the FOREIGN KEY CONSTRAINTS and which is explained with the help of an illustrated example.

First, let’s create two tables by the following query with primary key and foreign key constraint relation in the example.

CREATE TABLE COUNTRIES(
COUNTRY_ID INT AUTO_INCREMENT PRIMARY KEY,
COUNTRY_NAME VARCHAR(50),
COUNTRY_SHORTFORM VARCHAR(40));

INSERT INTO COUNTRIES (COUNTRY_NAME,COUNTRY_SHORTFORM)
VALUES('UNITED STATES OF AMERICA','US'),
('CANADA','CN'),
('NEW ZEALAND','NZ'),
('AUSTRALIA','AS');

SELECT * FROM COUNTRIES;

In this query, we have created a table COUNTRIES by using the CREATE TABLE statement and inserted some records in the COUNTRIES table by using the INSERT INTO statement. If we want to retrieve all records from the COUNTRIES table then we can use the SELECT statement.

CREATE TABLE STATES(
STATE_ID INT AUTO_INCREMENT PRIMARY KEY,
STATE_NAME VARCHAR(50),
COUNTRY_ID INT,
CONSTRAINT fk_country_id
FOREIGN KEY(country_id) references COUNTRIES(country_id));

INSERT INTO STATES(STATE_NAME,COUNTRY_ID)
values('QUEEENS',1),
('CALIFORNIA',1),
('QUEBEC',2),
('BROOKLYN',1);

SELECT * FROM STATES; 

In this query, we have created a table STATES by using the CREATE TABLE statement and inserted some records in the STATES table by using the INSERT INTO statement. If we want to retrieve all records from the STATES table then we can use the SELECT statement.

Now, due to the parent and child table relationship, we cannot directly drop the COUNTRIES table. And if we try to drop it, the server will return an error.

Now, to properly drop the COUNTRIES table, we need to ignore the foreign key constraint. And for this task, we can run the following query.

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE countries;

As we see in the above query, we set the foreign_key_checks to 0 then it will easily ignore the constraints and with the help of the DROP statement, it will delete the COUNTRIES table.

Read: MariaDB Backup Database

MariaDB Drop Table Hangs

In this section, we will know while dropping a table why it hangs by using examples.

The reason for drop table hangs in MariaDB while using the DROP TABLE statement is because of the deadlock. The process of deadlock is that the other transaction has to wait in line so that the older transaction gets completed first.

The sample example of the MariaDB drop table hangs is given below:

DROP TABLE IF EXISTS USA_DELL;

SHOW PROCESSLIST;

KILL 4;

Just Suppose, we are dropping a USA_DELL table by using the DROP TABLE statement but due to some reason, the query takes more than expected time seconds to execute the query. But for some reason, it gets delayed.

For that, then we use the SHOW PROCESSLIST query to checks the time taken for the query to execute. It will give all details about all the query execution portions like TIME, PROGRESS, etc. To remove that query from execution, we will use the KILL statement to kill that query. In the last query, we have used the ID as 4 to kill the statement by using the KILL statement.

Read: MariaDB Reset Root Password

MariaDB Drop Table Not Working

In this section, we will know why the drop table is not working and it is explained with the help of syntax and examples.

The syntax to drop a table by using the DROP TABLE statement is given below:

SYNTAX:

DROP TABLE TABLE_NAME;

The syntax explanation:

• TABLE_NAME: The name of the table that we want to remove the database by using the DROP TABLE statement.

The drop table isn’t working because of syntax or logical error, which suggests that you’re using a condition in the DROP TABLE statement that doesn’t apply to the resultset. Only when there is a grammatical error in the keyword or sentence does the syntax error occur.

The sample example of the drop table not working in the query is shown below:

DOP TABLE USA_EARPHONEPRODUCT;

The DOP keyword was typed incorrectly in the above query, and it needs to be written as a DROP keyword to retrieve the resultset. As a result, by writing the correct query, the USA_EARPHONEPRODUCT table will be removed from the database.

The correct query for the MariaDB DROP TABLE statement is given below:

DROP TABLE USA_EARPHONEPRODUCT;

To eliminate syntax issues, we rewrote the above query in the right format, which implies that it will now delete the USA_EARPHONEPRODUCT table from the AIRBNB_DB database.

Read: MariaDB Enable Remote Access

MariaDB Drop Table Column

In this MariaDB sub-topic, we will learn how to drop the drop or remove a column from a table by using the MariaDB ALTER TABLE statement and which is explained by the syntax and examples.

The MariaDB ALTER TABLE statement is used to remove or delete data from the table. It is also used to rename columns in the table.

The syntax of the MariaDB drop column by using the ALTER TABLE statement is given below:

ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME;

First, let’s have a look at the USA_COSTCAWHOLESALE table by the following query:

SELECT * FROM USA_COSTCAWHOLESALE;

The MariaDB SELECT statement is used to retrieve all records from the USA_COSTCAWHOLESALE table as the result set.

The sample example to remove or drop a column from the USA_COSTCAWHOLESALE table by using the ALTER TABLE statement and which is shown below:

EXAMPLE:

ALTER TABLE USA_COSTCAWHOLESALE DROP COLUMN PRODUCT_ID;

SELECT * FROM USA_COSTCAWHOLESALE;

We used the ALTER TABLE statement in this query to remove or drop the PRODUCT_ID column in the USA_COSTCAWHOLESALE table by using the DROP COLUMN keyword, and we can verify the remaining column with details in the USA_COSTCAWHOLESALE table by using the SELECT statement if we like.

Read: MariaDB Truncate Table

MariaDB Drop Table Warning

In this section, we’ll look at how warnings appear in the MariaDB DROP table when it’s dropped, as well as how to explain them using syntax and examples.

The syntax to MariaDB drop table is given below:

SYNTAX:

DROP FROM TABLE_NAME;

If the table_name previously existed in the database, the query will be conducted in MariaDB; however, if the unknown table_name that we want to check exists in the database or not, we must use the IF EXISTS clause. If the table_name is present, the DROP TABLE statement will remove it; otherwise, we must utilise the IF EXISTS clause in the DROP TABLE statement.

The following query is an example of how to drop a table if it already exists in the database:

DROP TABLE USA_DELL;

We attempted to delete the unknown table_name as USA_DELL from the AIRBNB_DB database, as shown in the above query. Because there is no table with that name, it will return an error.

The aim of the IF EXISTS clause is to determine whether the table name in the database or the column name in the table is already present. If it isn’t present, the warning indication for that table name in the database will be displayed.

The sample example of the MariaDB Drop table with IF EXISTS clause is given below:

DROP TABLE IF EXISTS USA_DELL;

SHOW WARNINGS;

As we can see in the above query, using the DROP TABLE statement with the IF EXISTS clause causes a warning dialogue box to appear for the USA_DELL table, which is used to check whether the table is present in the AIRBNB_DB database.

Read: MariaDB Rename Column

MariaDB Delete Table Data

We’ll learn how to erase data from a table using the DELETE statement, which is covered with syntax and examples in this section.

The MariaDB DELETE statement is used to delete one or more rows from the table. The syntax of the MariaDB Delete Table Data is given below:

SYNTAX:

DELETE FROM TABLE_NAME 
WHERE [CONDITIONS];

The syntax explanation:

• First, after the DELETE FROM keywords, enter the name of the table from which you want to remove data.
• Second, use a condition in a WHERE clause to determine which rows to delete. The where clause is not required. The DELETE statement only removes entries that cause the search condition to evaluate to true if the where clause is present.
• If no row from the table causes the search condition to evaluate to true, the delete statement will have no effect. If the where clause is omitted, the delete statement will destroy all rows from the table.

First, let’s have a look at the USA_DEVICE table by the following query:

SELECT * FROM USA_DEVICE;

The SELECT statement is used to retrieve all records from the USA_DEVICE table for the result set.

The sample example to delete table data in the MariaDB by using the DELETE statement is given below:

EXAMPLE:

DELETE FROM USA_DEVICE;

SELECT * FROM USA_DEVICE;

We used the DELETE statement in this query to remove all records from the USA_DEVICE table. So we used the SELECT statement to verify that all records in the USA_DEVICE table had been destroyed. It demonstrates that the SELECT query is useless for checking other records when the DELETE FROM statement has already been used to delete them.

Read: MariaDB Transaction

MariaDB Delete Table Alias

We’ll learn how to use the ALIAS TABLE name in the DELETE statement with the INNER JOIN clause in this section, which includes syntax and examples.

When we create an alias for a table in MariaDB, it’s either because we want to shorten the table name to make the MariaDB statement easier to understand, or because we want to list the same table name many times in the FROM clause (i.e. self join).

The MariaDB Delete Table Alias syntax is as follows:

SYNTAX:

DELETE TABLE_1.COLUMN,TABLE_2_ALIAS_NAME.COLUMN 
FROM 
TABLE_2_ALIAS_NAME 
INNER JOIN TABLE_1
ON
TABLE_1.COMMON_COLUMN=TABLE_2.COMMON_COLUMN
WHERE [CONDITIONS];

The syntax explanation:

• DELETE: The data which are going to be deleted from the table by using the DELETE statement.
• TABLE_2_ALAIS_NAME.COLUMN: We will use the alias name for the table_2 in order the call the column.
• FROM: There should be atleast one table in the FROM clause.
• TABLE_1.COMMON_COLUMN=TABLE_2.COMMON_COLUMN: The common column from the both tables i.e; table_1 and table_2 by which ON condition will take place.
• WHERE [CONDITIONS]: The parameters that must be met in order for records to be chosen.

The sample example of the MariaDB DELETE statement by using the TABLE ALIAS clause is shown below:

DELETE BK,BKT
FROM usa_basketball AS BK
LEFT JOIN usa_basKetballteam AS BKT
ON BK.player_id=BKT.player_id
WHERE BK.Current_Age<=22; 

SELECT * FROM USA_BASKETBALL;

SELECT * FROM USA_BASKETBALLTEAM;

In this query, the ALIAS NAME for both tables, USA_BASKETBALL and USA_BASKETBALLTEAM, was BK and BKT, and they were utilised with the INNER JOIN clause in the DELETE statement to get the resultset. The SELECT statement can be used to check for deleted records in both tables USA_BASKETBALL and USA_BASKETBALLTEAM.

Read: MariaDB Order By Clause

MariaDB Delete Table Join

In this section, we will learn how to use the LEFT JOIN clause in the DELETE statement and it is explained by the syntax and examples.

MariaDB LEFT OUTER JOIN is used to return all rows from the left-hand table specified in the ON condition and only those rows from the other table where the joined condition is satisfied and it is also known as LEFT JOIN.

SYNTAX:

DELETE TABLE_1.COLUMN,TABLE_2.COLUMN
FROM TABLE_1
LEFT JOIN TABLE_2
ON TABLE_1.COMMON_COLUMN = TABLE_2.COMMON_COLUMN;

First, let’s have a look at the USA_BASKETBALL and USA_BASKETBALLTEAM table by the following query:

TABLE_1: USA_BASKETBALL table (left- hand table)

SELECT * FROM USA_BASKETBALL;

In this query, we have used the SELECT statement to retrieve all records from the USA_BASKETBALL table.

TABLE_2: USA_BASKETBALLTEAM (right_hand table)

SELECT * FROM USA_BASKETBALLTEAM;

The MariaDB SELECT statement in the preceding query retrieves all records from the USA_BASKETBALLTEAM table.

The sample example of the LEFT JOIN clause used with the DELETE statement is given below:

EXAMPLE:

DELETE usa_basketball
FROM usa_basketball
LEFT JOIN usa_basketballteam
ON usa_basketball.player_id=usa_basketballteam.player_id
WHERE usa_basketball.Current_Age<=22; 

SELECT * FROM USA_BASKETBALL;

SELECT * FROM USA_BASKETBALLTEAM;

We eliminated the current_age column of the basketball player whose age is higher than or equal to 22. from the USA_BASKETBALL table in this query by utilizing the DELETE statement and the LEFT JOIN clause. We may use the SELECT statement to see which records have been deleted from the USA_BASKETBALL and USA_BASKETBALLTEAM tables.

You may also like to read the following MariaDB tutorials.

• MariaDB Select Statement
• MariaDB Rename Table
• MariaDB Logs – Helpful Guide
• MariaDB Union Operator
• MariaDB Regexp + Examples
• MariaDB Case Statement

In this MariaDB tutorial, we have studied the use of the MariaDB Drop Table and we have also covered some examples. There are lists of the topic that comes under discussion:

• MariaDB drop table
• MariaDB drop table if exists
• MariaDB drop table ignore constraints
• MariaDB drop table hangs
• MariaDB drop table not working
• MariaDB drop table column
• MariaDB drop table warning
• MariaDB delete table data
• MariaDB delete table alias
• MariaDB delete table join