MySQL – Disable Foreign Key Checks or Constraints

Databases are all about saving data. With DBMS and RDBMS, the entire data became relational and all the records became related to each other as in the real world. So came into existence the concepts of primary keys, foreign keys, foreign key constraints and whole bunch of other terms like composite keys, referential integrity, indexes and what not.

So coming back to the objective of pinning down this post, some days ago I came across the requirement of deleting some user records from the user table. As soon as I tried deleting the records, I came across the error of referential integrity where in I was greeted with the error that child records were there and hence the delete operation was not allowed.

So I searched for a shortcut which could let me do my task. And I came across this …

SET foreign_key_checks = 0;
DELETE FROM users where id > 45;
SET foreign_key_checks = 1;

By setting the foreign key check to 0, I was able to update / delete my users table. Once I was done with my operations on the user table, I reset the key check to 1 again and everything is back in place now.

You can always drop a FOREIGN KEY the usual way ..


ALTER TABLE users DROP FOREIGN KEY <foreign_key_name>


As correctly pointed out by tboehm in the comments, I disabled the foreign key checks for some requirement of mine. It is absolutely important to think about the repercussions of this. It is always advisable to start with the child entries and then proceed for the parents. The sole purpose of this article is to make you aware of the option which disables the foreign key constraints.

I guess other available options and arguments for ALTERING TABLES can be found here ..

MySQL ALTER TABLE syntax

Ciao!!!

SQL query results into CSV file

You must have fired those select statements tons of times. So you hit a query, get your results and you are done. But what if the next day you felt like going through the results you found the other day. Simple..hit the query again. But what if the query was complex and you need the results fast. What you shud have done in the first place was to generate a CSV file out of your query result.

The way I do it and others must be doing is pretty simple.

So say your query was something like this:

mysql> select * from users where dob > ‘1981-10-07′;

To get the output of this query into a CSV file is pretty simple. Do dis….

mysql> select * from users where dob > ‘1981-10-07′
INTO OUTFILE ‘user_details.csv’ FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘n’;

Bingo..u have ur file waiting for u. In case u don’t know where that file got created…check in dis location:

$/var/lib/mysql/<ur_database>/user_details.csv

So its dat very simple. So next time just keep this in mind.

And though this is off topic, here is a way to add time to a datetime attribute in your table.

mysql> select batchid, date_add(created_at, interval ‘9:30′ HOUR_MINUTE)
from batches where created_at >= ‘2000-01-01 00:00:00′ and created_at <= ‘2000-01-02 01:00:00′ ;

Simple enough.

MySQL – Dump those tables

So how do you take backup of your MySQL database? Simple, launch SQLYog or HeidiSQL and export your database. Well its an easy solution on Windows. How would you go about it on Linux or simply put how wud you do it on command prompt. The answer is mysqldump.

Its there on your system once MySQL gets installed. Once you know what database you need to take backup of, its pretty simple then:

Lets say you have a database named workshop and you want to back it up in a file named workshop_backup.sql. Simple execute the following statement:

$ mysqldump -u root -p workshop > workshop_backup.sql

-u stands for user and -p is for password. If u have set some password for root, it will ask you to enter it once you hit enter.

In case of large database, you can gzip your dump like this:

$ mysqldump -u root -p workshop | gzip > workshop_backup.sql.gz

What if you want only specific tables from your database and not the entire database as such. Do this:

$ mysqldump -u root -p workshop payments orders > only_payment_orders.sql

This will copy only payments and orders tables from workshop database.

Whatz the use of a database dump unless you use it. So to restore the files generated in the examples above, simply run this command:

$ mysql -u root -p new_workshop < workskop_backup.sql

This will dump the entire workshop database into new_workshop database.

In case you had created gzipped file:

$ gunzip < workshop_backup.sql.gz | mysql -u root -p new_workshop

Therez one more utility called mysqlhotcopy. It uses LOCK TABLES, FLUSH TABLES, and cp or scp to make a database backup quickly. It can make backup of the database or single table pretty fast, but it can be run only on the same machine where the database directories are located.

One more thing. if you are on windows and you happen to have a big dump file, restoring it might give you an error, something like MySQL is down or something similar. In that case you need to add the following line in my.ini file in your MySQL directory (generally under C:\Program Files\MySQL\MySQL Server 5.1)

max_allowed_packet=32m

Save the changes. Don’t forget to restart the mysql service or you will keep getting the error.

Now that you know how to take backup, do not forget to take one today. Happy dumping!!

MySQL – Indexing

Database is nothing but collection of tables. Tables are useless unless we perform some operations on them. Operations on such tables require time to process. Sometimes these operations are pretty fast, sometimes not. In case of databases these operations get executed when we run some query. So depending on what and how much of information is being retrieved from the tables, queries may take some time to return what you want them to. But when this wait becomes annoyingly long, you need to think about it. You need to think judiciously on queries. And you need to add INDEXES.

As it happened in our case, not using indexes on our tables made our application slow. When we analyzed our database queries using EXPLAIN statement, we came to know that for almost all queries, all table rows were being queried.

Lets say we had Users table with total 7 users.

SQL> explain select * from users where role = ‘Director’;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	users	ALL	(NULL)	(NULL)	(NULL)	(NULL)	7	Using where

We added indexes then and reran the explain statement to check the result.

SQL> create index index_on_role on users(role);

SQL> explain select * from users where role = ‘Director’;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	users	ALL	(NULL)	(NULL)	(NULL)	(NULL)	1	Using where

Thus adding index on role resulted into reducing the row search from 7 to 1. Imagine thiss for millions of records. Thus adding indexes to your tables reorders the records in such a way that its retrieval becomes fast.

I will try to put up some thoughts on Indexing soon…till then stay indexed