Drop Unique Constraint in MySQL

If you would like to drop a unique constraint from your table in MySQL, there is a very simple way to do it. Thanks to how to drop unique constraint, you can do so as well. The trick is to know that you have to drop an index, instead of a unique constraint. Consider the following example:

create table mytable (
accountNumber tinyint unsigned not null,
authCode tinyint unsigned not null,
authorized enum ('NO', 'YES') not null default 'NO',
constraint uk_mytable unique(accountNumber, authCode));

In this example, I am setting a name (of my choosing) for the constraint. The reason is if I want to do anything with the constraint, I have an easily recognizable name for it. Now if you want to drop a unique constraint, you do this (tested in MySQL 5.0 and confirmed it works):

alter table mytable drop index uk_mytable;

Remember, do not do the following (or any variation of it) because it will give you SQL error:

alter table mytable drop unique constraint uk_mytable;

To recap, if you want to drop a unique constraint in MySQL, you have to drop an index rather than the unique constraint itself. The reason, I guess, is that MySQL consider a unique constraint to be an index.

Connect to Database Using Python

Although you can connect to databases using Python on many platforms, the specific examples here are on Ubuntu.

MySQL

If you would like to connect to MySQL using Python, the process is quite simple. You will need MySQLdb. In Ubuntu, it is quite easy to install

sudo apt-get install python-mysqldb

Once you have installed it, you are ready to go. How do you actually connect to it? I would rather let people smarter than me explain it. One such resource is Using the MySQLdb Interface.

Microsoft SQL Server

A fantastic guide for beginners has been provided at Accessing MSSQL using Python on Ubuntu. Head over there to read how to do it. Some useful links: pymssql, freetds, and ubuntu freetds.

However, I can also try to customize install instructions here.

sudo apt-get install python2.5-dev freetds-dev build-essential

You have to download the pymssql module, but choose the platform independent files, not Windows specific. After downloading (I am assuming the .zip file), you may install it using the following steps:

unzip pymssql-0.8.0.zip

cd pymssql-0.8.0

sudo python setup.py install

To see how to use it in your scripts, try Example script – pymssql module, Example script – _mssql module, or Accessing MSSQL using Python on Ubuntu.

Django in Ubuntu

As I use Django in Ubuntu, I would like to point out little quirks here.

Install Django

Installing Django in Ubuntu is quite simple:

sudo apt-get install python-django

Django is installed in the path /var/lib/python-support/python2.5/django

Since I will be using MySQL, I needed to install another package which would let Python connect to it.

sudo apt-get install python-mysqldb

Start New Project

While trying to create a new project, run the following command:

cd /home/me/

django-admin startproject newprojectname

Although the documentation says to use django-admin.py, in Ubuntu Gutsy, you should not use the .py extension. Instead, just use django-admin. The source of this information is a post on Ubuntu Forums: Path to Django Installation.

Start a New Application

Go into the directory of your project. For example, if your project is called newprojectname, then

cd /home/me/newprojectname

Then run the following command

python manage.py startapp newappname

Inspect the Database

If you need to generate a model of your database automatically, you can easily inspect the database by first going into the directory of the project

cd /home/me/newprojectname

and then running this

python manage.py inspectdb

I usually save the output to a file so that I can look at it and make changes to it as required.

python manage.py inspectdb > ~/inspectdb-date.py

In my example, inspectdb-date.py can be used as a models.py file after rearranging the contents (Cleaning Up Generated Models).

Deploy Django with Apache

Although the Django Book has a very good way of explaining this stuff (Using Django with Apache and mod_python), I would like to explain the same thing step-by-step.

Install Apache: sudo apt-get install apache2

Install mod_python: sudo apt-get install libapache2-mod-python

Check if mod_python is enabled: ls /etc/apache2/mods-enabled/mod_python.load and if you see mod_python.load as a result, it is enabled

If mod-python is not enabled: sudo a2enmod mod_python

Restart Apache server: /etc/init.d/apache2 restart

Configure Virtual Host

Your virtual host config file should look something like this

sudo vim /etc/apache2/sites-available/mysite

NameVirtualHost *:80
<VirtualHost *:80>
ServerAdmin admin@mysite.com
ServerName mysite.com
ServerAlias http://www.mysite.com
DocumentRoot /home/me/mysite/
ErrorLog /home/me/logs/error.log
<Location "/">
SetHandler mod_python
PythonHandler django.core.handlers.modpython
PythonPath "['/home/me/'] + sys.path"
SetEnv DJANGO_SETTINGS_MODULE mysite.settings
PythonDebug On
</Location>
</VirtualHost>

Of course, if it is a production site, PythonDebug On should be omitted.

If you are moving your Django site from one server to another, it might be a good idea to first create a new site on the new server with the same name as the site being transferred. Then just copy all the files and folders into the new site. I don’t know how Django works in this regard but it is possible it keeps a list of projects created.

How Do I do it?

I have created a directory for templates and static (css, images, etc.) files: /home/me/templates/ and /home/me/templates/static/. My Django application resides in /home/me/mysite/. My file looks like

NameVirtualHost 192.168.0.10:80
<VirtualHost 192.168.0.10:80>
ServerAdmin myemail@mydomain.com
ServerName mysite.mydomain.com
DocumentRoot /home/me/templates/
ErrorLog /home/me/logs/error.log
LogFormat "%v %l %u %t \"%r\" %>s %b" comonvhost
CustomLog /home/me/logs/custom.log comonvhost
<Directory /home/me/templates/*>
Options -Indexes
</Directory>
<Location "/">
SetHandler mod_python
PythonHandler django.core.handlers.modpython
PythonPath "['/home/me/'] + sys.path"
SetEnv DJANGO_SETTINGS_MODULE myapp.settings
PythonDebug On
</Location>
<Location "/static">
SetHandler None
</Location>
<Location "/media">
SetHandler None
</Location>
</VirtualHost>

I also created a symlink to the Django admin media files in /home/me/templates/ (my apache root directory) so that http://mydomain.com/admin/ would look the way it’s supposed to with all its GUI intact.

cd /home/me/templates/
ln -s /var/lib/python-support/python2.5/django/contrib/admin/media media

Hat tips to: Django Installation on Apache; Serving the Admin Files;

Playing With MySQL Storage Engines in Ubuntu

To see which tables are using which storage engine, you may run the following command on your database

show table status from databasename;

Now you can decide how to manage your tables. A useful comparison of storage engines in MySQL is also available.

Set InnoDB as Default Storage Engine

If you want to use InnoDB as the default storage engine for MySQL in Ubuntu 7.10 (Gutsy Gibbon), you may do so easily. A forum post (Re: default table type = innodb is stable??) helped a lot in learning about this. First, stop the server

sudo /etc/init.d/mysql stop

Read the MySQL configuration file

cat /etc/mysql/my.cnf

Look for the following part in the configure file

# * Basic Settings
#
user = mysql

Edit the file

sudo vim /etc/mysql/my.cnf

And change the text (we need to look for) to

# * Basic Settings
#
user = mysql
default-storage-engine = InnoDB

Start the server

sudo /etc/init.d/mysql start

Now whenever you create a new table, it will use the InnoDB storage engine.

Change Storage Engine for Tables

A very good post (Convert a bunch of tables to InnoDB) helped me gain this knowledge. If you want to change the storage engine for tables, just run the following query on your database. If you want the table to use InnoDB use

alter table tablename type=InnoDB;

If you want the table to use MyISAM use

alter table tablename type=MyISAM;

Create a Table With Storage Engine

If you want to create a table and specify which storage engine to use, you may do so in the following way

create table dummytable1 (id int, name varchar (10)) type = InnoDB;
create table dummytable2 (id int, name varchar (10)) type = MyISAM;

Type or Engine?

You can replace type = InnoDB with engine = InnoDB. They are both the same (I think). It is up to you to decide.

Pivot Tables in Databases

Sometimes there are situations where you have to pivot a table or view. It could be that you need to pivot columns into rows or pivot rows into columns. There are always elegant ways to accomplish database tasks but not all of us are that good. So we resort to brute force hacks. This post will explain the brute force methods which will give you a very good chance to accomplish your task. It won’t be pretty and it may not be efficient, but it gets the job done.

The reason for discussing pivots is to invite everyone to provide better ways to accomplish this cumbersome task. I have employed this technique on MySQL so it should work. If Oracle, MS SQL, DB2 and other commercial DBMSes have better ways to do this stuff, and you know how to, please share with us.

I don’t like to use keywords INNER JOIN because to me it is more readable to have an equal to sign than inner join keywords.

Pivot Columns into Rows

Say you have a table such as this:

ID | STUDENT | GRADE1 | GRADE2 | GRADE3

Say you want to pivot it so that you get a view such as this:

ID | STUDENT | GRADE1

ID | STUDENT | GRADE2

ID | STUDENT | GRADE3

Your best friend in this case is UNION. For example,

SELECT ID, STUDENT, GRADE1 FROM SOMETABLE UNION ALL
SELECT ID, STUDENT, GRADE2 FROM SOMETABLE UNION ALL
SELECT ID, STUDENT, GRADE3 FROM SOMETABLE;

There you have it. Columns have been pivoted to rows. It is a brute force method but doesn’t it seem logical enough?

Pivot Rows into Columns

This is where it gets tricky. It is not very easy to pivot rows into columns. However, it can be done. The secret ingredient is the use of aggregate functions along with group by. The function I like to use is min() along with an arbitrary rank assigned to every row based on how you want to pivot the data.

Let’s say you have a table such as this:

TARGET_PHONE_NUMBER | SERVICE_PROVIDER | RATE_PER_MINUTE | QUALITY_OF_SERVICE

If you need to generate a horizontal listing of carriers based on what they charge for a particular destination (target_phone_number), from the lowest (to the left of the listing) to the highest (to the right of the listing) cost, first assign a rank to each row.

In MySQL 5, what I did was create a table with the same structure as our example table, plus an auto_increment column.

CREATE TABLE RANKING_TABLE LIKE ORIGINAL_TABLE;

ALTER TABLE RANKING_TABLE ADD COLUMN RANK INT AUTO_INCREMENT PRIMARY KEY;

The inserts, however, have to be grouped and ordered according to the way we wish to rank them. For example,

INSERT INTO RANKING_TABLE (TPN, SP, RPM, QOS) SELECT TARGET_PHONE_NUMBER, SERVICE_PROVIDER, RATE_PER_MINUTE, QUALITY_OF_SERVICE FROM SOMETABLE ORDER BY TARGET_PHONE_NUMBER, QUALITY_OF_SERVICE, RATE_PER_MINUTE, SERVICE_PROVIDER;

This will insert all data according to the order you defined and give a rank to each row. You will then have to group your data together based on TPN (TARGET_PHONE_NUMBER).

In other databases that provide you with rank() functions, you may use those. Consider the brute method of ranking as a poor man’s ranking.

Once you have the rankings, you just need to create a mega-script to pivot based on ranks. The bad thing about this method is that as you add more rows to be pivoted, your script grows as well and it becomes very difficult to maintain after some point.

SELECT TPN, MIN(RANK) FROM RANKING_TABLE GROUP BY TPN;

This was your first pivot, where the one with the lowest rank is supposed to be the best phone company to deal with. The next best carrier we get by:

SELECT A.TPN, B.RANKONE, A.MIN(RANK) RANK_TWO FROM RANKING_TABLE A, (SELECT TPN, MIN(RANK) RANKONE FROM RANKING_TABLE GROUP BY TPN) B WHERE A.TPN = B.TPN AND A.RANK <> B.RANKONE GROUP BY A.TPN;

Here you have created one row with two phone service providers. The left-most is the best and the next one is one notch less. Now we add a third provider to the query.

SELECT C.TPN, D.RANKONE, D.RANKTWO, MIN(C.RANK) RANKTHREE FROM RANKING_TABLE C, (SELECT A.TPN, B.RANKONE, A.MIN(RANK) RANKTWO FROM RANKING_TABLE A, (SELECT TPN, MIN(A.RANK) RANKONE FROM RANKING_TABLE GROUP BY TPN) B WHERE A.TPN = B.TPN AND A.RANK <> B.RANKONE GROUP BY A.TPN) D WHERE C.TPN = D.TPN AND D.RANKONE <> C.RANK AND D.RANKTWO <> C.RANK GROUP BY C.TPN;

You can add more layers to this query as you pivot more and more rows. As I said, it can become very unwieldy as you need to create more and more columns. However, once you know one way to do something, you can always fall back on it as default.

Conclusion

People say I like to write mega-queries, and I do. I like the step-by-step way of going over the query later on or for debugging while I am writing it. Using temporary tables is also something I like to do as less of as possible. Why create tables when a query can work just as well?

I have read that DBMSs are now coming out with pivot operators which are much cleaner to use. As soon as MySQL implements it, I would definitely like to check it out. Otherwise, I know that this method will work with or without pivot operators.

Brute force methods are very much possible to pivot tables. It takes a lot of analysis to come up with better ways to do the same thing, especially when it comes to customizing to your situation. If you know you can always rely on at least one solution to give you accurate results, you can use it to test whatever you need to test. For production, you may come up with a better alternative. If you do know to improve pivoting data in tables, please share it with the world.

Common Queries for MySQL 5

I found a very cool and helpful web page: Common Queries for MySQL 5. I found the idea very inspiring. So now I would like to emulate them. Whatever queries I write shall be posted here. Just look for the query category on this blog.

Follow

Get every new post delivered to your Inbox.

Join 30 other followers