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)
|Action / Object||Description||Usage|
|DropTable objectName||The DropTable class is a query to either remove a table from a database or to remove all data from it. DropTable contains only two attributes of importance, the name of the table to manipulate and whether or not we are deleting all of the data or the data and the table itself.||DropTable drop|
|SetTable(text tableName)||This action sets the table of the query.||drop:SetTable("tableName")|
|SetEmptyOnly(boolean)||If this flag is true, when issued this DropTable query will drop the data inside the table, but not the table itself.||drop:SetEmptyOnly(true)|
|DropTable(DropTable object Name)||This action takes a DropTable query and sends it to the database.||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)
In the next tutorial, we will discuss InsertingData, which describes how to insert data into a database table.