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.

  • 1
    What RDBMS and version are you running this against? – Simon Righarts Mar 4 '12 at 21:57

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.