Tutorial: Databases

Dropping Tables: Deleting a Table from a Database

With a database, we want to make sure that we keep our tables organized and remove any redundancy or irrelevant information. Although our database is currently small, in a real world setting, databases can be fairly large with whole rooms of servers that contain important information and data industries use every day. The ability to delete tables inside our database is necessary to condense information and have less overhead with our machines with fewer tables. To do this, we would create a new object called DropTable, that allows us to use helper actions to delete the existing table inside of our database.

With our DropTable object, we are now able to empty the table completely and delete it. For this example, we are going to use our previous table created, "newtable" because we really will not be using this table anymore, therefore it would be a okay to completely remove this table from our database. It is okay to remove "newtable" because it had no important data stored, but it is advised to be cautious when deleting a table otherwise we may lose data that cannot be replicated.

mysql> show tables;
+---------------------------+
| Tables_in_myfirstdatabase |
+---------------------------+
| amazon_wishlist           |
| artsupply_list            |
| customer_list             |
| grocerylist               |
| likes                     |
| likes_query               |
| newtable                  |
| users                     |
+---------------------------+
8 rows in set (0.00 sec)

mysql>

To have access to these empty and delete actions, we will be creating our DropTable object and call it 'drop.' The actions we will be using are SetTable(text tableName), SetEmptyOnly(boolean), and DropTable(DropTable objectName).

use Libraries.Data.Database.Database
use Libraries.Data.Database.DropTable

Database database
database:Connect("localhost", "myfirstdatabase", "root", "password")

DropTable drop

What we want to do first is use SetTable() on our 'drop' object which allows us to select a specific table inside our database. For example, SetTable() takes in one parameter, the table title and in this case we will be passing in "newtable" into the action. We will then be switching over to our database object once again and use the action DropTable() and pass in our drop object inside the parameter. This allows us to commit the changes we made and reflect them onto the database in real time. (NOTE: As an alternative to deleting an entire table, we can empty our table using SetEmptyOnly(true). This allows us to empty the data of our entire table but not the table itself. This is similar to TRUNCATE in SQL.)

drop:SetTable("newtable")
// drop:SetEmptyOnly(true)
// we only would use this action if we want to drop the data in our table
database:DropTable(drop)

We can confirm our deletion with the SQL command: SHOW TABLES; and see that our "newtable" table has been deleted.

mysql> show tables;
+---------------------------+
| Tables_in_myfirstdatabase |
+---------------------------+
| amazon_wishlist           |
| artsupply_list            |
| customer_list             |
| grocerylist               |
| likes                     |
| likes_query               |
| users                     |
+---------------------------+
7 rows in set (0.00 sec)

mysql>

Below is the full code of deleting a table inside our database.

 use Libraries.Data.Database.Database
use Libraries.Data.Database.DropTable

Database database
database:Connect("localhost", "myfirstdatabase", "root", "password")

DropTable drop
drop:SetTable("newtable")
// drop:SetEmptyOnly(true)
// uncomment the above action only if you want to delete data, not the table itself
database:DropTable(drop)

Next Tutorial

In the next tutorial, we will discuss InsertingData, which describes how to insert data into a database table.