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!!!

Migrations: Did they mature with Rails 2.1.#

With the new face of Rails (Rails 2.1 and beyond), came the change in the way migrations used to work.
So lets see some of the changes that went into migrations (that is when Rails was 1.2.3)… the then and now changes …

First of all, serial numbers for migrations are gone and they have made way for the UTC based names for
the migrations. So earlier (pre Rails 2.1 era) if one created migration scripts, they used to get their
names serially like…


001_create_books.rb
002_create_shelves.rb

whereas now the names have the UTC timestamps on them …

20080601000001_create_books.rb
20080601000002_create_shelves.rb

With this new change rake db:migrate could apply all migrations that had not yet gone into database (as now
all migrations run and add an entry into the database migration table). Because of this the issue with the earlier version of migrations was taken care of i.e old migrations heavily used to rely on once single entry to run the migrations. If you somehow happened to screw the version number, u ended up getting failed migration errors.

Along came one more functionality which now lets run the up and down functions for a particular migration.

$ rake db:migrate:up VERSION=20080601000002
$ rake db:migrate:down VERSION=20080601000002

rake db:migrate:up or rake db:migrate:down let you go up or roll back a particular migration’s changes.

The other change was the name of the table that stored the migration information. In earlier version of
rails, it used to be ’schema_info’ which gave way to ’schema_migrations’. So I would suggest not to look
for the earlier name in case you got your application migrated from Rails 1.2.# to Rails 2.1.#.

We also got rid of specifying

t.column <attribute_name> <data_type> for the attributes and

it got reduced to

t.<data_type> <attribute_name>

which would look something like this in the new migration…

class CreateBooks < ActiveRecord::Migration
  def self.up
    create_table :books do |t|
      t.integer :code, :null => false
      t.string :name
      t.string :author_id ... for the author's sake...
      t.timestamps
    end
  end

  def self.down
    reverse the gears ...
  end
end


To add to this, timestamps came into existence which was a required addition so that we could save
some keystrokes and bid goodbye to

t.column created_at
t.column updated_at


So those were some of the changes cum additions ….
To go ahead with, lets see the cycle of migration …

Now with the new mmigrations, when they are run, this is what happens …

1. Table ’schema_migrations’ is created if that does not exist.
2. For every migrations that runs successfully, an entry is created in the schema migrations table.
3. The leading part of the migration filename gets stored into the table.
     For eg. 20080601000001 gets saved when 20080601000001_create_books.rb runs and updates the database.
4. New migrations run when an entry is not found in the migrations table.

So now if you want to force and roll your database to a specific version, you supply the VERSION= parameter

$ rake db:migrate VERSION=20080601000002

If that version happens to be greater than those already in, the migrations will be applied. But in case the
version number is lesser than those in the migrations table, than Rails undoes the changes and self.down comes into play and thus migrations run in reverse direction to undo changes to the version that was specified.

One good thing which most of us must be missing and which is gone with arrival of the new migrations is the shorter version numbers for the migration files.

Earlier we could do something like

$ rake db:migrate VERSION=2

which has become this now

$ rake db:migrate VERSION=20080601000002

So I bet it is not so easy to remember the exact 14 character version number but I guess as long as we have Ctrl C n Ctrl V, it wont be much of a problem

So to end or startup with, I think they did mature just like other things that matured with time …
Rails included

Time for me to migrate to a new blog post .. till then keep migrating the new way …

Migrations

I am back on track .. track of writing something new here…
and this time it is Migrations .. Rails Migrations ..

As you must be knowing (u all Rails enthusiasts) that migrations allow
you to make changes to your database schema. In doing so, you also get the
facility of moving from version x to y of your database.

So this time I was required to add a column to my already existing model.
So I went ahead and did something like this …

def self.up
  add_column :media, :category_name, :string
  muvee = Media.find_by_name('whoami')
  muvee.update_attributes :category_name => 'action'
end

When I ran this migration, the new column got added to the table but the
migration did not update the data in the table.

I reran.. thinking I missed something but nothing happened.

After a while I came across something and added it to the migration code. This time

it did add the column along with the data.

def self.up
  add_column :media, :category_name, :string
  muvee = Media.find_by_name('whoami')
  FileType.reset_column_information # This was added ...
  muvee.update_attributes :category_name => 'action'
end

What ‘FileType.reset_column_information’ does is that it makes the new schema change
available to the model as it resets the column information ( w/ the information about the new column). Thus the changes become available for the new data to be inserted properly.

Hope this information helps someone out there in case such situation arises.

Cheers !!!

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.