Sunday, October 28, 2007

Data Validation with MySQL Triggers

One of the applications that I get to play with on a regular basis is a PHP, Javascript AJAX environment that acts as a frontend for a MySQL database. I wanted to add range constraints to some of the fields in order to reduce typo's.

I created before triggers with the following basic skeleton for each table that I wanted to add the range constraint to.

if NEW.columnname > theupperrange then
SELECT `**columnname is out of range**` from thetable into @str_val;
end if;

This will cause the insert to fail with the follow error message
Unknown column `**columnname is out of range**`

because of the nature of the application (if an error occurs in MySQL the PHP returns the error message) it then becomes the JavaScript's XMLHttprequest.responseText. so on the javascript side of the application i look for the Unknown column like this

//req is the name of the XMLHttpRequest object i create to handle interaction between
//the javascript and PHP.

if(req.responseTest.indexOf("Unknown column"))
{
msg = req.responseText
arr_msg = msg.split("**")
themsg = arr_msg[1]
alert(themsg)
//window1 is where data is entered, I know it's a real meaningful name:)
window1.focus()
}

else
{
regular application stuff
}

The idea of how to write the trigger was not original with me. I got it from http://www.brokenbuild.com/blog/category/mysql/ , who I believe said he got it from another source.

Wednesday, April 25, 2007

Insert into a foreign key field looks like it should match.....

You bring data into a referenced column through LOAD DATA LOCAL INFILE but when you try to insert data into the referencing column you get the "Cannot add or update a foreign key constraint fails...." error. You have looked at the referenced column and the referencing data again and again and you don't see the problem.

If you open up Query Browser and look at the referenced data you may find paragraph marks. This problem confounded me for quite some time. I understood that the paragraph marks were causing the problem but I didn't understand how they were getting there.

This is a classic case where "RTFM" applies. MySQL by default understands line termination by the return character "\r". The Windows OS by default writes line termination as a carriage return and a new line "\r\n". The extra \n is causing the problem.

How to deal with this is right in the MySQL manual. But I thought I would post it because I do and did "RTFM" and still missed it. So maybe I can save someone else the frustration. The solution is to simply use LINES TERMINATED BY... at the end of your load data statement like so

LOAD DATA LOCAL INFILE "/path/to/file" INTO TABLE tablename LINES TERMINATED BY '\r\n'.

This can also cause you trouble in reverse if you are importing referencing data with the wrong kind of line termination

Monday, April 23, 2007

Find unmatched referenced column values

When you use LOAD DATA LOCAL INFILE on a table that contains a foreign key constraint you get an error with an explanation that a the table cannot be updated because a foreign key constraint fails.....

1. Turn off foreign key checks with the following command at the MySQL command line client

a. set foreign_key_checks = 0;

3. issue your load data command again

4. once the data is loaded issue the following query

select referencing_table . referencing_column
FROM referencing_table
LEFT JOIN referenced_table ON
(
referencing_table . referencing_column
=
referenced_table.referenced_column
)
WHERE
referenced_table.referenced_column IS NULL;

This will give you a list of all the column values in the offending table that do not match a value in the referenced table.

5. Update all of the values listed in the query results from above

6. turn the foreign_key_checks back on with the following
a. set foreign_key_checks = 1;

Why this works

We are doing a LEFT JOIN which means that everything on the left side of the expression will be returned regardless of whether or not there is a match on the join. We purposefully put our referencing column on the left.

Then we joined it to the table we want to reference with the JOIN....ON statement so there is a relationship between the tables.

We then excluded all instances where there was a match with the WHERE clause by only selecting instances of the join where the referenced column was null (if it wasn't null then the values in the 2 tables did match and therefore, was not breaking the constraint).

Why this is important
Sure you got your data loaded by turning off the foreign key check so you're good right? No you need to take this step and update all the values that don't match because

1. you added the constraint for a reason right (probably consistent, reliable data) so you want to make sure that your data is consistent and reliable.

2. If you issue an alter table statement on a table that is violating a foreign key constraint the server will error and you won't be able to alter your table with out setting the foreign_key_checks = 0 again.