Can I use a Clob as a table key (from Java)?

Question

Following a suggestion made to another question to store large amounts of text: can one use a CLOB as primary key field (i.e., from Java's DataNucleus implementation of JDO)?

I know it is probably a very bad idea to use a CLOB as a key, but I do need to use large text as a key on a one-shot operation.

Answer 1

The simple version would be to generate a hash from the CLOB and use this as a key. This key will fit into the allowed key width for your engine (900 SQL Server, 767 InnoDB, 1000 MyISAM etc).

The hash can be generated by the engine as a computed column, trigger, or by some ETL process, or by the application

There is a faint chance of collision (birthday problem) but this would only matter for many records and poor choice of hashing algorithm