I Will Have No Hair In Five Years.

MySQL is an open-source relational database system. XML is a markup language designed to provide a good way to organize data and allow computers to get meaning from data (as opposed to HTML, where meaning must be inferred - not something today's computers are equipped to do). So what exactly do they have in common, to both be included in the same post?

They are, colloquially stated, a pain in the butt.

Let's start with XML. The Extensible Markup Language is a descendant of SGML (Standard Generalized Markup Language), which was designed to provide a standard document storage format. XML took that one step further by allowing users to define their own schemata. Once the schema has been defined, the XML document can be validated. After that, it can be transformed through the use of a stylesheet document. Pages thusly transformed will render as HTML, with whatever formatting you specify.

Sounds great, doesn't it? You have a language that you can fully customize to your needs and you can make it look pretty! This, my friends, was what I was still doing at 2:30 this morning, after having started some time in the middle of the afternoon. In my foolish, naive way, I believed that I could create an XML document with sample data, define the schema from that, create the stylesheet, and be good to go. Um...not exactly. The stylesheet is a very finicky animal, and it wants things served to it on a silver platter (oops, I meant "in a particular order, in a particular format). So now I have to figure out how to retool my schema and document in order to display the content I want, in the order I want.

However, MySQL was the topic for the day, when I finally dragged myself out of bed late this morning. After my compatriots and I had input all the data in our database, it occurred to me that one of our tables really needed a 1:1 recursive relationship. This particular table has 7 fields in its primary key. After being reminded of the fact that the ALTER TABLE command does, in fact, exist, I added in the appropriate columns and populated them.

Then I tried to add the foreign key constraint. Nothing worked. It simply would not add that foreign key, choosing instead to give me Error #150. At wit's end, I Googled "mysql 150 add foreign key" and came up with a 2006 MySQL bug report (bug 16290) complaining about the unclear error message that results from attempting to create a foreign key on a non-indexed column. "Aha!" I said to myself. "The columns I'm trying to reference aren't indexed." I created an index on all 7 primary key columns and executed the ALTER TABLE command to add the foreign key - and it worked. I was exceedingly happy.

Quote of the Day:

If you'll come up one by one, unarmed, I'll engage to clap you all in irons and take you home to a fair trial in England. If you won't, my name is Alexander Smollett, I've flown my sovereign's colours, and I'll see you all to Davy Jones.

--Captain Smollett, Treasure Island