What's the difference between creating a table if it doesn't exist, and dropping the tables if they exist before creating them?

Question

I have been working on a database, and been through some problems with drop table TABLE if exists statements in order to not duplicate the tables. The issue is that there is an order to respect (which table has to be dropped first and second etc) based on foreign keys and dependency of inclusion. but there are other statements which allow to create a table if it doesn't exist already create table table_name if not exists and seems to be doing the same job without order issues. I just need to know if there is a difference between these two ways of proceeding, and are there other conditions to consider ?

Answer 1

Yes, there is a difference. If the table exists, CREATE TABLE IF NOT EXISTS does nothing, and therefore generates no conflict.

Again if the table exists, DROP TABLE IF EXISTS will drop the table and cause conflicts if the table is needed.

Moreover, as other have said: - permissions will be reset (this might be a plus, if intentional; or a drawback if permissions need then to be restored). - all data will be lost when the table is dropped. - views, procedures, functions, indexes involving the table will need to be resubmitted.

For all these reasons, you probably don't want to use DROP TABLE unless you have no further need of said table.

If you do this to reset the table to a known state, this is a database smell - it means that you have trouble tracking changes to the database. Instead of going around the problem, it is better to invest some time in understanding why the database gets out of control (any changes you make should be done with ALTER TABLE or CREATE INDEX and should be undoable one by one¹), and how to bring it back under control. This will yield huge dividends in the future.

In MySQL you can SET FOREIGN_KEY_CHECKS = 0. See here.

---

(1) if your change leads to data loss, for example you change a VARCHAR(30) to VARCHAR(15), you can create an undo table by selecting the primary key and the affected field only - CREATE TABLE undo_20161229_183415 AS SELECT tbl.pkey, tbl.changedfield1, tbl.changedfield2 with the old values before altering. Later, you can change back the field definition, and do an UPDATE JOIN on the table.

NOTE: You may want first to add a WHERE to cast the old values to the new format, and verify that this matches what is in the new table:

2016-12-29.1    1234    PEOPLE'S REPUBLIC OF CHINA
2016-12-29.2    1234    PEOPLE'S REPUBLI    # the change was ill advised
2017-01-15      1234    TAIWAN              # the customer moved

When on February 2 you want to expand back and recover your data, you want to replace PEOPLE'S REPUBLI with the PRC's full name... except that on that one record you absolutely don't want to restore PRC to what is now TAIWAN.

Answer 2

Dropping and then creating will naturally reset all permissions to the table , and execution plans for the queries that use this table. All procedures/functions that use this table will be invalidated as well.

Answer 3

Generally when releasing db updates, it get used to compose sql scripts with statements for creating tables or other objects.

From here you may follow several strategies. Mysql seems to support also the strategy of creating one script file that you append with the updates, and so you will create the table only the first time you exec the script, all the following executions won't create the tables. Of course in this scenario you will have add the "if not exists" clause to all objects of your script file.

Instead, a more followed strategy is to create several files as many as are the db objects, one file per object. And so, if the object is a table, then first to create the table you have drop it if exists, and so explained the other statement.

Of course, when you execute this table script files you have pay close attention at the db data you want preserve from lost, and also to the order of their execution due to eventual foreign key constraints that may affects the dropping operations.

Generally this scripts are launched only in dev or test environments or when you are installing first time your db in production.

In prod env usually, as far as updating db table objects, get launched scripts that contain only alter statements.