ICAT and Derby

[Steve Fisher]

I have just found that ICAT does not work properly on the Derby Database. http://docs.oracle.com/javadb/10.3.3.0/devguide/cdevstandards806118.html states that: "The SQL standard defines that unique constraints on nullable columns allow any number of nulls; Derby does not permit unique constraints on nullable columns." This restriction means that some constraints are simply ignored and you can have unlimited datasets in an investigation with the same name. The short term solution is not to use Derby for ICAT.

In the longer term, and originally for different reasons I would like to simplify the schema. This may make Derby usable by ICAT - but that is a different post.

Steve

[Steve Fisher]

I have found that MySQL with InnoDB has a similar problem. It allows such constraints to be defined but ignores them. To get round it for MySQL you can define a couple of unique keys: 

alter table DATASET add UNIQUE KEY (INVESTIGATION_ID,NAME); 

alter table DATAFILE add UNIQUE KEY (NAME,DATASET_ID); 

to enforce a more extreme constraint.

Steve