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:

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:

DROP DB

If you need to drop a database, the SQL statement for that is:

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:

DROP TABLE

The same applies to delete a database 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:

Another way to ensure the existence of the database by using count instead of EXISTS, like the following:

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.

mhmd

Hello, I'm Mohammad Daka, software engineer who love coding. In my spare time I blogs about JavaScript,PHP, Chrome Extensions, helpful resources and tools, I'm also working on my own projects. Don't hesitate to contact me at my twitter account: @mohammad_daka :)

Leave a Reply

Your email address will not be published. Required fields are marked *