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.