The Lutheran Geek

The life and times of a WoW-playing, Java-programming dude in Chicago

Saturday, April 26, 2014

Foreign key declarations in MySQL

In my few years working closely with MySQL, I’ve found something surprising about how foreign keys are declared. For the longest time, I’ve been used to declaring foreign keys like so:

create table foo (
foo_id int(11) primary key auto_increment,
-- ...
);

create table bar (
bar_id int(11) primary key auto_increment,
foo_id int(11) not null references foo(foo_id),
-- ...
);

After a while, I started noticing that foreign key constraints were not being enforced like I thought they would be. When I looked at the SHOW CREATE TABLE statement for these tables, I found something surprising: the foreign keys were not created! After a bit of frustration, I took to the internet, and found the following quote in MySQL’s documentation:

MySQL does not recognize or support “inline REFERENCES specifications” (as defined in the SQL standard) where the references are defined as part of the column specification. MySQL accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification.

(emphasis mine)

What’s worse, when you try to use an “inline REFERENCES” as above, MySQL will silently ignore it! I hunted around a bit to see if I could find any way to make this syntax a fatal error, but there doesn’t seem to be. So anyway, you need to create foreign keys the “long way” in MySQL:

create table bar (
bar_id int(11) primary key auto_increment,
foo_id int(11) not null,
-- ...
foreign key bar_foo_id (foo_id) references foo(foo_id)
);

It’s frustrating that the inline REFERENCES is just ignored instead of treated as a syntax error – it fools you into thinking that your DDL was fully processed, but you don’t get so much as a warning that it was not processed 100% as you might expect. Does anyone know why don’t just treat this as an error? To me it seems like bad behavior on MySQL’s part. Maybe it’s time to switch to PostgreSQL. 😛

posted by Peter at 6:02 pm  

Friday, August 6, 2010

I’ve only got one word for you:

Acetylseryltyrosylserylisoleucylthreonylserylprolylserylglutaminylphenylalanylvalylphenylalanylleucylserylserylvalyltryptophylalanylaspartylprolylisoleucylglutamylleucylleucylasparaginylvalylcysteinylthreonylserylserylleucylglycylasparaginylglutaminylphenylalanylglutaminylthreonylglutaminylglutaminylalanylarginylthreonylthreonylglutaminylvalylglutaminylglutaminylphenylalanylserylglutaminylvalyltryptophyllysylprolylphenylalanylprolylglutaminylserylthreonylvalylarginylphenylalanylprolylglycylaspartylvalyltyrosyllysylvalyltyrosylarginyltyrosylasparaginylalanylvalylleucylaspartylprolylleucylisoleucylthreonylalanylleucylleucylglycylthreonylphenylalanylaspartylthreonylarginylasparaginylarginylisoleucylisoleucylglutamylvalylglutamylasparaginylglutaminylglutaminylserylprolylthreonylthreonylalanylglutamylthreonylleucylaspartylalanylthreonylarginylarginylvalylaspartylaspartylalanylthreonylvalylalanylisoleucylarginylserylalanylasparaginylisoleucylasparaginylleucylvalylasparaginylglutamylleucylvalylarginylglycylthreonylglycylleucyltyrosylasparaginylglutaminylasparaginylthreonylphenylalanylglutamylserylmethionylserylglycylleucylvalyltryptophylthreonylserylalanylprolylalanylserine.

(The “longest word to appear in print” – it’s the name of a protein from the “tobacco mosaic virus”.)

posted by Peter at 10:36 am  

Thursday, May 6, 2010

Java Generics – a problem I couldn’t quite overcome

For the most part, Java Generics work quite well for 99% of my needs when doing generic programming. The API is fairly intuitive, and I rarely have to use the weird features like <? super T> or whatever. However, I came across a situation that generics couldn’t quite handle.

I was working on a GWT project, and I found myself looking for a sortable table much like a JTable can do pretty much out of the box in Swing. I found a simple implementation that stores each row of the table in a RowData class. Now, the thing about such a row is that an instance of a row would contain several different objects of varying types; maybe a string in the first column,  a few numbers in the next columns, a date or two in other columns. So we can’t parameterize the RowData class directly… or can we? We do want to specify that the types of objects we put in our RowData instances are instances of Comparable, because we are going to sort a collection of rows on one column (thus, the RowData itself is declared as public class RowData implements Comparable<RowData>). So would it make sense to declare the class as public class RowData<T extends Comparable<? super T>> ...? No, because then you are restricting the type of object you are using in the RowData. We have to leave it unparameterized.

So we have to maintain a list of these objects. Well, maybe we can just parameterize this list as a list of Comparables, since we know they all have to be Comparable:

private List<Comparable<?>> columnValues = new ArrayList<Comparable<?>>();

Seems sensible. However, we are going to have a problem in our compareTo() method:

public int compareTo(RowData other) {
		Comparable<?> obj1 = this.getColumnValue(this.sortColIndex);
		Comparable<?> obj2 =  other.getColumnValue(this.sortColIndex);

		return obj1.compareTo(obj2); // compiler error!
	}

In the end, I had to compromise and drop the used of generics. My coworker Andy called this solution “concise, but not precise.”

@SuppressWarnings("unchecked")
public int compareTo(RowData other) {
	if (null == other) {
		return -1;
	}

	if (!(this.getColumnValue(this.sortColIndex) instanceof Comparable<?>)
		|| !(other.getColumnValue(this.sortColIndex) instanceof Comparable<?>)) {
		return 0;
	}
		
	// I'm promising that these are mutually comparable, but guaranteeing this
	// at compile-time is nigh impossible!
		
	Comparable obj1 = (Comparable) this.getColumnValue(this.sortColIndex);
	Comparable obj2 = (Comparable) other.getColumnValue(this.sortColIndex);
		
	return obj1.compareTo(obj2);
}

Inelegant, but I couldn’t come up with a better way to do it. I’m thinking you could use reflection to cast the objects, but unfortunately GWT does not allow much access to the reflection API beyond getClass() and the instanceof operator.

posted by Peter at 10:48 am  

Monday, July 27, 2009

Screw you gnome-terminal!

OK, geekiness time. Most people hate a blinking cursor in a terminal – that huge blinking square is irritating beyond belief. Strangely, though, a blinking caret (the thin line you see in most text editors) is quite all right by most people. However, in their infinite wisdom, GNOME decided that all users want the caret and cursor to behave the same, and have hard-wired the cursor behavior to be the same as the caret behavior, which is stupid. At least they did for a few versions; they are now decoupled again, but they don’t give you an easy way to turn off the blinking. Here is the magic incantation to access the secret setting to turn off blinking:

gconftool-2 -s /apps/gnome-terminal/profiles/Default/cursor_blink_mode -t string off

Done! now you can hack in peace. 🙂

posted by Peter at 9:37 am  

Powered by WordPress