many-to-many relationship

Hello,

I need make relationship of many-to-many type between two tables, but actually I can't do it... 

table 1:

id	Linked_field1
1	123
2	123
3	568
4	78

table 2:

Linked_field1	val_field
123	5.86
123	6.86
123	24.3
78	1.3
568	78.4
568	62.3
568	0.12

and for example for record with id 2 from 1st table should be shown all three  records from 2nd table.

when I try to make many-to-many relationship I get error

org.springframework.dao.DataIntegrityViolationException: The relationship could not be created. One of the fields was not REQUIRED, which may cause unique constraint creation failure on some databases. If this is the case, set both fields to REQUIRED.

The original error was:



StatementCallback; SQL [ALTER TABLE APP.SAMPLE ADD CONSTRAINT UK_FK_SAMPLE UNIQUE (COMPOUND_ID)]; The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL140905142228830' defined on 'SAMPLE'.; nested exception is java.sql.SQLIntegrityConstraintViolationException: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL140905142228830' defined on 'SAMPLE'.

; nested exception is org.springframework.dao.DuplicateKeyException: StatementCallback; SQL [ALTER TABLE APP.SAMPLE ADD CONSTRAINT UK_FK_SAMPLE UNIQUE (COMPOUND_ID)]; The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL140905142228830' defined on 'SAMPLE'.; nested exception is java.sql.SQLIntegrityConstraintViolationException: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL140905142228830' defined on 'SAMPLE'.

	at com.im.df.impl.db.DBRelationshipMtoNNT.create(DBRelationshipMtoNNT.java:84)

	at com.im.ijc.core.wizards.newdfitem.NewDFItemWizardPanel1$2.phase1InRequestProcessor(NewDFItemWizardPanel1.java:140)

	at com.im.commons.progress.BackgroundRunner.processTheTask(BackgroundRunner.java:133)

	at com.im.commons.progress.BackgroundRunner$1.run(BackgroundRunner.java:96)

	at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:1411)

	at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:1991)

Caused by: org.springframework.dao.DuplicateKeyException: StatementCallback; SQL [ALTER TABLE APP.SAMPLE ADD CONSTRAINT UK_FK_SAMPLE UNIQUE (COMPOUND_ID)]; The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL140905142228830' defined on 'SAMPLE'.; nested exception is java.sql.SQLIntegrityConstraintViolationException: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL140905142228830' defined on 'SAMPLE'.

	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:245)

	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)

	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:407)

	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:428)

	at com.im.ijcs.impl.ddl.TableManagerImpl.addUniqueConstraint(TableManagerImpl.java:434)

	at com.im.ijcs.impl.ddl.operations.constraint.AddUniqueConstraintOperation.executeImpl(AddUniqueConstraintOperation.java:105)

	at com.im.ijcs.impl.ddl.operations.AbstractOperation.execute(AbstractOperation.java:117)

	at com.im.ijcs.impl.ddl.DefaultDDLService.performOperations(DefaultDDLService.java:860)

	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

	at java.lang.reflect.Method.invoke(Unknown Source)

	at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)

	at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)

	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)

	at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:96)

	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:260)

	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:94)

	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)

	at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)

	at com.sun.proxy.$Proxy20.performOperations(Unknown Source)

	at com.im.df.impl.db.DBSchemaImpl.applyOperations(DBSchemaImpl.java:1062)

	at com.im.df.impl.db.DBRelationshipNT.createNew(DBRelationshipNT.java:136)

	at com.im.df.impl.db.DBRelationshipMtoNNT.create(DBRelationshipMtoNNT.java:75)

	... 5 more

Caused by: java.sql.SQLIntegrityConstraintViolationException: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL140905142228830' defined on 'SAMPLE'.

	at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)

	at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)

	at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)

	at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)

	at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)

	at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)

	at org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(Unknown Source)

	at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)

	at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)

	at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)

	at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)

	at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)

	at org.springframework.jdbc.core.JdbcTemplate$1ExecuteStatementCallback.doInStatement(JdbcTemplate.java:421)

	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396)

	... 26 more

Caused by: java.sql.SQLException: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL140905142228830' defined on 'SAMPLE'.

	at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)

	at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)

	... 40 more

Caused by: ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'SQL140905142228830' defined on 'SAMPLE'.

	at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)

	at org.apache.derby.impl.sql.execute.UniqueWithDuplicateNullsIndexSortObserver.insertDuplicateKey(Unknown Source)

	at org.apache.derby.impl.store.access.sort.SortBuffer.insert(Unknown Source)

	at org.apache.derby.impl.store.access.sort.MergeInserter.insert(Unknown Source)

	at org.apache.derby.impl.sql.execute.CreateIndexConstantAction.loadSorter(Unknown Source)

	at org.apache.derby.impl.sql.execute.CreateIndexConstantAction.executeConstantAction(Unknown Source)

	at org.apache.derby.impl.sql.execute.CreateConstraintConstantAction.executeConstantAction(Unknown Source)

	at org.apache.derby.impl.sql.execute.AlterTableConstantAction.executeConstantActionBody(Unknown Source)

	at org.apache.derby.impl.sql.execute.AlterTableConstantAction.executeConstantAction(Unknown Source)

	at org.apache.derby.impl.sql.execute.MiscResultSet.open(Unknown Source)

	at org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(Unknown Source)

	at org.apache.derby.impl.sql.GenericPreparedStatement.execute(Unknown Source)

	... 34 more

 

Thanks!

Igor.

Hi Igor,

creating many-to-many relationship involves additional so called "Join table". There is also other rule that values in Linked_field1 in both tables should be unique. And that these records will be connected by the join table, where many-to-many relation is formed as follows for example:

TABLE1_Linked_field1	TABLE2_Linked_field1
1	1
2	2
2	3
3	3

As you can see row in TABLE1 with Linked_field1 is connected only to one record in record in TABLE2, which has Linked_field1 = 1

Similarly row 2 in TABLE1 corresponds to 2 and 3 in TABLE2. And 3 from TABLE2 corresponds to rows 2 and 3 in TABLE1.

Unfortunately we don't have any tutorial explaining this in detail. And it is more-less database orientated concept rather that intuitive chemoinformatics work.

Hope it helps though!

Filip

Dear Igor,

Filip was faster with the response, however, I have several comments to your relation logic too. First of all, the logic is fine, but IJC does not work currently in this particular way. What it creates is a 3rd table in the database, which contains two-way many-to-one relationship -> hence, it expects a unique key in the constrain. This is what causes the error.

This was basically already explained by Filip in previous post.

To overcome this, I have 3 possible solutions for you (I will explain that in detail in following text):

First:

If you can, the current logic would best suit for many-to-one relationship, provided that your linked_field will have always unique value. This solution works out of the box, you just use many-to-one relationship instead of many-to-one. I am attaching a zip file, where you can see this. Created on IJC 6.3.3. (testZIP63.zip)

 

Second and Third:

If you need many-to-many relationship, there are 2 cases that can occur:

1. You do not yet have data in the database


2. You already do have data in the database

Case 1:

In the first case, it is simple. You just create the desired tables in the structure you need. Both tables will have unique IDs fields. Make the relationship over them.

This will create a 3rd table in the database, that is not visible in IJC user interface. If you have tables Test1 and Test2 and assuming that the Test1 table will be the parent table, it will create a table Test1_Test2 with 2 relationships (Test1_Test2 from Test1 and Test1_Test2 to Test2). This 3rd table uniquely associates the IDs of the 2 tables.

Then, you can create a form view with both tables. You then just add the data manually in the logic that you want.

 

Case 2:

You already have the tables with the data. Then, create the many-to-many relationship over IDs as in case 1. This creates this 3rd join table. What you need to do then, is to connect to the Derby DB directly (Via some SQL editor, or directly from IJC. See documentation here) and fill this table with definition that fits your case. In your particular case, you would use this particular SQL:

INSERT INTO test1_test2 (SELECT test1.id, test2.id FROM test1, test2 WHERE test1.linkedfield = test2.linkedfield)

Assuming that your tables are test1 and test2 and the field you want to link over is called linkedfield in both tables. You can see this in attached many2manyTest.zip attached file.

 

Other, unorthodox solution would possibly be instead of all of this, creating 2 many-to-one relationships in both directions, with "Create DB constrains" option unselected. The disadvantage of this approach is however the fact, that this will not watch for broken links in the database. 

I hope this explains the problematics in more detail and that it helps.

 

Wishing all the best

David

David, great thanks for so detailed answer!