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 …

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