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

SVN or Subversion Repository – How would you set it up?

It has been a long time since I posted my last blog post. So here I am ‘populating’ my post with some information that could be of some worth to me and others in need. So without much delay, let me put down the ad hoc article on setting up svn repository. It also talks about creating a structured hierarchy for code management and also gives information on adding projects to SVN repository.

To create svn repository svnadmin command line tool is used.

$ svnadmin /home/gaurav/new_repo

To add directories to the new repository …

$ svn mkdir file:///home/gaurav/new_repo/main_project

$ svn mkdir file:///home/gaurav/new_repo/main_project/branches

$ svn mkdir file:///home/gaurav/new_repo/main_project/tags

$ svn mkdir file:///home/gaurav/new_repo/main_project/trunk

When directories are added, it asks for the commit comments.

Once the repository structure is ready, import the code like this:

$ svn import /home/gaurav/my_new_app/ file:///home/gaurav/new_repo/main_project/trunk

Following command runs svn server @ port 4444 for localhost

-r is for the repository and should be passed the repository path

$ svnserve -d -r /home/gaurav/repository/ --listen-port 4444 --listen-host localhost

Once svn server starts as a daemon, checkout can be done as:

$ svn co svn://localhost:4444/momsworld/trunk mw_0718

—————————————————————————————

Add users to your server

In order to authenticate our intended users, some changes are required to be made. Inside the new repo (c:\svn-repo or /home/gaurav svn-repo), there is a directory called conf. There you will find svnserve.conf.

You need to enable these lines in the file:

[general]
anon-access = read
auth-access = write
password-db = passwd

All other comments can be left untouched. For easy readability, I have only put the required section above.

With this setting, anyone would be able to read the files but only an authenticated user would be able to checkin. Also password-db tells server that a file named ‘passwd’ would be storing usernames and passwords.

Let’s create that file now. It should also be in the conf directory and look like this:

[users]
david = secret


Similarly many users could be added in the format NAME = PASSWORD. There are many other advanced ways to add password protection to Subversion but this one is the easiest I suppose. Subversion has good integration with Apache’s web server authentication so that could be utilized as well.