Common pitfalls to avoid in MySQL - Part-2

This is a continuation to my previous article "Common pitfalls to avoid in MySQL - Part 1"

This article looks at two "gotchas". One with Join and the other with Check constraint.

For those of you who have come to this article directly read the note below. Others can directly jump to the point. Also note that in addition to the two tables from the previous article I am using one more table "employeeactivity" and have given the details below after the note.

Please Note : All I have written and executed here in this article is performed using MySQL version 5.0.27 with the default settings that comes when you install. People are welcome to comment and suggest if any of these pitfalls can be overcome by any changes in configuration or settings. Also if any of this has changed in the newer versions, please feel free to comment.

I will be using 3 tables for my examples.
First is the employee table as shown below
The second is employee communication table which is as follows
The third is the employee activity table which is as follows

1. Join on a comma or a pipe separated value still joins

This is a very strange behavior that I observed, from the above tables, if I execute the query below

SELECT activityName,employeeid,id,firstName,lastName 
                      FROM employeeactivity 
                      LEFT JOIN employee ON employeeid = id

Select all Open in new window

You expect the first name, last name for the last row with activity name "Approved Workflow" to be NULL as the value "1,3" does not match any of the values of the primary key in the employee table.

But what actually happens is that it does join, it takes the value before the comma casts it to an int and joins on that. see below the result of the above query.

This is the same case when when the value of employee id is "1 | 3" or "1 and 3" or "1 followed by space and any data " basically the first word it gets (which is separated by a comma or pipe or space)  , it casts to an int and joins on that

2. Check constraint does not work

In the above table suppose I create a check constraint like this and run on mysql server

ALTER TABLE employee ADD CONSTRAINT CK_Gender CHECK (Gender IN ('M' , 'F') );

Select all Open in new window

It runs successfully, then suppose I run a statement like this

insert into employee (firstName, lastName, gender, batType) values('test','test','O','R');

Select all Open in new window

You expect it to come out with an error telling invalid value, but it does not error. it just inserts the value and comes out normally.

Apparently this is a documented limitation in MySQL. This is the exact quote from the MySQL documentation "The CHECK clause is parsed but ignored by all storage engines", you can find it here http://dev.mysql.com/doc/refman/5.0/en/create-table.html .

So if you think that adding a constraint will keep your data integrity, you are in for a surprise. The workaround for this will be to use
1. A Trigger (With signal if it is MySQL version 5.5 or higher).
2. An Enum , If you are using MySQL version 5.5 or higher


This is it for now.

I know this is a very short article, but right now I got busy with many things and not getting time to spend on this. However I do intend to add to this as and when I find time.

Please do leave your comments & suggestions below.

Thank you for reading!!!