New relationship creation failed

Hi,

I recently took the plunge and moved to a mySQL-based database system with an evaluation license (mySQL 5.0.45-7, IJC 2.4.2, Centos 5.2). When trying to build a relationship between a Structure and Compound Batch table I get the following error:

New relationship creation failed: StatementCallback; bad SQL grammar [ALTER TABLE COMPOUNDS ADD CONSTRAINT UQ_CPD_ID UNIQUE (Cpd_ID); nested exception is com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: specified key was too long; max key length is 767 bytes

I tried to create a simple many-to-one relationship called Compounds_Batches, with Compound ID as the common field (I have added these to both entities). This worked fine under Windows with a local Derby database.

What does the key length refer to?

Regards, HomerE

Without a real example to test I can't be totally sure on this, but after a bit of Googling it looks like there are some MySQL related bugs when using foreign key constraints and/or unique indexes on text columns. See here for example

http://forum.mamboserver.com/showthread.php?t=30925

It looks like the MySQL database might need to have a default characterset defined. Or maybe a more recent version of MySQL might have fixed this problem.

However, one thing of note is that you are presumably using a text column (VARCHAR) for the columns that are used by the relationship. Whilst this should work (except for this MySQL bug)  it is usually better to use integer columns if that is possible. That would certainly avoid this problem.

 

Tim

 

OK, thanks. I was indeed using a text-based column as the common denominator. I now changed to the Cdid column, and added this to the batch table as well, and this works fine.

Thanks!

Hi there Tim,

I would like to follow up on this limitation again. I'm still using the same version of mySQL, hence cannot use text-based fileds as foreign keys for relationship building. My impression is that Oracle XE does not support this feature either. Do you know if other versions of mySQL and Oracle allow the use of text fields for this purpose?

Thanks!

I don't think its a problem with text columns per se. just the length of the text columns. For instance, if the column definition is VARCHAR(1000) it will be too long, but if its VARCHAR(32) it should be OK. There is an additional aspect related to the characterset being used (the characterset affects the actual lenght of the column so if you specify 32 characters the actual bytes used in the column may be more formany charactersets, but if you use a relatively short length then you should still be well under the 1000 limit.

By default text columns in IJC are set to VARCHAR(1000), so if you need to use them in foreign key constraints then you should use a shorted column length. If you are importing data then the column length for each field can be defined in the import wizard. If you already have data that is too long then for MySQL you can shortern the column (this is not possible in a local Derby database). Use the schema editor to do this. Make sure you don't shortern it too much or your data might be truncated!

Tim

Hi Tim,

I tried this with a 9-character VARCHAR field for our Batch IDs (they contain 9 characters), and that seems to work for building relationships. Is that what you were suggesting?

Anyway, thanks for suggesting this.

Yes, the length of the column was the problem. Short ones will be OK.

Tim