SQL portability: indexes
Saturday, February 2nd, 2008Anybody who told you that SQL is portable was lying.
I always understood that SQL wasn’t the best language in the world, and that if I went poking into obscure corners, I would be in trouble. But this week my world-view was shattered.
SQL does not include indexes.
Yep. Even the newer versions like SQL-2003 don’t contain “CREATE INDEX” anywhere in them. So while your SELECTs might be generally portable across databases, your INDEXes are anybody’s guess. As just one example I ran into: MySQL index names only need to be unique per-table, but SQLite index names must be globally-unique.
When I manage to get all of our tests running on SQLite, I’ll list all of the things I needed to change.
