Is DROP TABLE a common action before CREATE TABLE?


#1

Question

Is DROP TABLE a common action before CREATE TABLE?

Answer

The use of DROP TABLE is not recommended to be kept outside of the development process of an app, so we would not commonly find it as part of a deployed application, as a matter of fact, neither would be a CREATE TABLE action. There is a good reason for this, for example, once our application behaves and works as we want it to and we decide to deploy it, usually the same service we use to store our app, will have a database storage as well where we can migrate our local db, or start one connected to our 'live'code (code that is accessible already through the browser). At this point, we would run a CREATE TABLE once, because databases are meant to preserve data as much as possible, it is usually cumbersome when there is the need to restructure your schema (for example).

In that example, imagining that we created FaceBook using an SQL database, by now there are millions of rows, dropping a table could be a catastrophic thing, yet if we really needed to, there is also the practice of creating back up databases.

Let’s back up now, while we develop our application, because we keep on making changes to its behavior and how it uses the database, sometimes we need to change the schema which as we may remember is the structure of what a row has in the table, if we need to change the schema, it means that forcefully we need to create a new table, and since we will not use our old version of that table, the best practice there would be to drop it.

Something I will like to mention here is that if we just DROP TABLE IF EXISTS, since SQL is index-based, even though we get rid of the table, the id or index of each row will usually stay, so if we had ten rows and deleted the table, the first entry on the new table with the same name will start at the index 11, to prevent this there is an action like IF EXISTS called CASCADE, it propagates the delete operation on the parent key to each dependent child key, in this case the rows, cleaning up their indexing so when we create a new table, the first item to be inserted will be at the index of 1 (remembering that SQL indexing starts at 1 and not 0). That way, when necessary, as we work on our projects and we need to DROP a TABLE because of schema changes or any need, we could safely run:

DROP TABLE IF EXISTS table_name CASCADE;

and now when we create our new table:

CREATE TABLE IF NOT EXISTS table_name(
 propname TYPE,
 propname TYPE,
 propname TYPE);

Then confidently we will know that the first entry we make will start at the index of 1:

INSERT INTO table_name VALUES(valueOne, valueTwo, valueThree);