Hands on: Relationships in databases

caSE SEnsitIve joins
Joins, as described above, are case insensitive. Sara Athwal emailed with a problem that can only be solved with a case-sensitive join. She has written an Access database for her company and has a product table.

Each product in her company has a unique identifier made up of characters and numbers. As she says, these are completely unreadable (like Df3dD) because they are machine generated. The machine in question is a Unix-based system and Sara was horrified to discover, after she had built the database, that DF3dD was being used to signify a different product from Df3dD and Df3dd - in other words, it is case sensitive.

Her question is simple: “How can I perform a case-sensitive join?” It’s an interesting question with at least three answers - or one solution and two answers.

Solution 1
One solution is: “Are you sure you want to do that?” An alternative is to keep the machine-generated value in the table but not use it as the primary key. Instead, assign another unique number (possibly using an autonumber field) to each product and use that number as the primary key. This is potentially easier and may be faster when you perform the joins.

While this may be a solution, it doesn’t answer the question I was asked and you may find a case one day where a case-sensitive join is essential, so let’s look for active solutions.

Solution 2
Access doesn’t support case-sensitive joins without a bit of creative table-building, which relies on a more or less undocumented feature. When you’re shown the list of available field types when you’re creating a table, the Binary type does not figure. Nevertheless, it’s there and is used by some of Access’ internal system tables.

You can use it as you’d use the Text field type and it will behave as a Text field, except when used with any of the comparison operations (such as making joins and sorting or indexing fields). In these cases it uses the ASCII code value equivalent to the field contents and uses that for joining, sorting and indexing. There are different ASCII codes for each character in both cases (‘A’ is 65 and ‘a’ is 97) and this is how the Binary field can act in a case-sensitive fashion.

The Binary type isn’t on offer during the normal table-building process so we have to be a little sneaky in order to create a table with a binary field and use raw SQL. As part of its data definition language, SQL has a command called CREATE TABLE that does exactly what the name suggests. So if we run the following SQL:

CREATE TABLE PENGUIN (MyField binary (30))

it will create a table called ‘Penguin’ with a field called ‘MyField’ that is of type binary and able to accept up to 30 characters. How do we run this SQL?