<

Common pitfalls to avoid in MySQL - Part-2

Published on
7,782 Points
1,682 Views
1 Endorsement
Last Modified:
Approved
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
Employee table all records with data typeThe second is employee communication table which is as follows
EmployeecommunicationInfo tableThe third is the employee activity table which is as follows
Employeeactivity table

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

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.
Result of join
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') );

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');

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!!!
1
Comment
Author:SANDY_SK
0 Comments

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Join & Write a Comment

In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month