MySQL – Validating your code
It’s very important when you are writing your SQL queries to validate them.
CREATE DB
If you need to create a database, the SQL statement for that is:
|
1 2 3 4 |
CREATE DATABASE database_demo_name; |
But if you are going to create a DB that is already existing you will get an ERROR message ( Can’t create database ‘database_demo_name’…), so to avoid that you can do it in a better way:
|
1 2 3 4 |
CREATE DATABASE IF NOT EXISTS database_demo_name; |
DROP DB
If you need to drop a database, the SQL statement for that is:
|
1 2 3 4 |
DROP DATABASE database_demo_name; |
But maybe that database name is not existing anymore, that way you will get an ERROR with a message like this ( Can’t drop database ‘database_demo_name’…), so again to avoid this, let’s make it better by adding a condition before any drop:
|
1 2 3 4 |
DROP DATABASE IF EXISTS database_demo_name; |
DROP TABLE
The same applies to delete a database table:
|
1 2 3 4 |
DROP TABLE IF EXISTS demo_table; |
Don’t forget if their other database tables that are connected to this database table by foreign keys, you will also get an ERROR message.
Hence, you can check the existence of the table, by searching on INFORMATION SCHEMA TABLES like this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT IF( EXISTS ( SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'database_demo_name' AND TABLE_NAME ='demo_table' ), 'TRUE', 'FALSE' ) AS 'is_demo_table_exists'; |
Another way to ensure the existence of the database by using count instead of EXISTS, like the following:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT IF ( ( SElECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'database_demo_name' AND TABLE_NAME = 'demo_table' ) = 1 ,'TRUE', 'FALSE' ) AS is_demo_table_exists; |
Information Schema is a read-only view that can provide information about all the tables in relational databases like MySQL, MSSQL, PostgresSQL… however, there are a couple of relational databases that are not supporting Information Schema…
As mentioned above, we can use this concept of validation in other cases, editing rows, deleting rows, altering tables…
And I hope this was helpful.
