SQL (Structured Query Language) is a fantastic tool for building databases and maintaining data integrity, but this nifty programming language is just as handy when it comes to the destructive side of data management.
Data, especially large amounts of it, is rarely a static entity. As the world changes, data changes, and some of it becomes redundant. Dealing with this defunct data efficiently is a key concern in database maintenance, and SQL makes it a total breeze, but how is it done?
Let’s explore how we can use SQL to drop our database deadweight efficiently, with minimum effort!
What You Need To Know Before Deleting A Table
We’ll get to the deletion process in just a sec, but before we Marie Kondo the heck out of your database, there are some things you need to know.
Foreign Key Restrictions
Essentially, a Foreign Key is a database key utilized to link two tables within the database together to help identify and highlight relational data between them. Now, I know what you’re thinking… What does this have to do with my database table demolition derby?
Well, typically, when a Foreign Key Constraint is in place, you cannot delete or “Drop” one of the referenced tables. So, before you give your table the boot, you’ll need to Drop the Foreign Key Constraint.
If you plan on Dropping the referencing table and the table holding the primary Foreign Key simultaneously in the same Drop statement, you’ll need to list the referencing table before the key-holder table.
Triggers And Associations
When you delete a table, any triggers or associations it shares with other tables will be deactivated and severed, so if you plan on replacing it with a new table, you won’t be able to maintain all the pre-established relational programming. It will have to be done from scratch.
Keep It Simple
You cannot Drop and Create a table in the same SQL statement, so delete first, then focus on rebuilding after the fact.
The Permanence Of Loss
This is a big one! Always remember that once you delete a table using SQL, you cannot get it or the data it holds back. Once it’s gone, it’s gone for good! There is no undo function, so you have to be sure you’re ready to part ways with the problem table before pulling the trigger, so to speak.
The Persistence Of References
If your database stores references to the table you’re about to whack, Tony Soprano style, they will have to be deleted or modified separately.
SQL servers tend to offer custom levels of user access that will be determined by the manager of your operation. In light of this, you may need certain permissions in order to Drop a table.
DDL Giveth, and DDL Taketh Away
Okay, so to get rid of all that stagnant data that’s just taking up space and bites on your computer system, we’re actually going to be using DDL (Data Definition Language), which is a subset of SQL.
Although it’s called a language, DDL essentially just piggybacks on SQL’s syntactical architecture, so it’s not strictly a discrete computer language, but it’s powerful nonetheless. In the world of databases, DDL is divine — it can create, and it can destroy.
If you were responsible for the development of the database and table(s) in question, then you’ll already be well acquainted with DDL, as it’s the tool you’ll have used to assemble the database objects in the first place.
However, it doesn’t really matter if you’re only hearing of DDL for the first time right now, as the deletion process is one of the most straightforward commands in the entirety of SQL — hooray!
Keyword For Deleting A Table
You’d think that the keyword for deleting a table would be “Delete”, right? Well, you know these tech companies like to play it fast and loose with their verbiage, so it was never going to be as simple as that.
Nope, the keyword you’re looking for isn’t “Delete”, but “Drop”. That’s right, folks… the word “Drop” in SQL and DDL refers to the deletion of tables and data. So, when you’re trying to get rid of something, the syntax of your statement will always begin with “Drop”.
DDL Drop Statement Syntax
After “Drop”, you’ll need to choose the database object that you want to trash. Today, it’s a table, so, at this juncture, the Drop statement should look like this:
- Drop Table
Pretty simply, eh? And the beautiful thing about “Dropping” is that you only need one more bit of info, and that’s the table name. So, let’s say, for example, your table is called, I don’t know… Dinosaurs, the Drop statement should look like this…
- Drop Table Dinosaurs;
Activate that statement, and boom! The dinosaurs are no more, but whatever you do, don’t forget that sneaky little semicolon at the end. That’s the magic ingredient that actions the preceding command.
Deleting Data Without Deleting Tables
If your problem is with the data within a table rather than the table itself, you don’t have to delete them both at the same time.
To rid a table of data without killing off the table, simply replace your Drop keyword with “Truncate”. Everything else stays the same, so your DDL statement would look something like this…
- Truncate Table Dinosaurs;
That’s literally it. Your dino data will be no more, but the table will be intact, ready for use at a later date.
I now officially declare you an SQL Don of table deleting, although, let’s be honest… It’s pretty easy. As I mentioned earlier, you just have to be very sure that you’re ready to delete the table in question, because there’s no bringing it back from the abyss once it’s gone.
If you’re deleting tables from a workspace database, my advice is to always seek the express permission of your superior before actioning anything. That way, if things go south due to the deletion, you can’t be held accountable.