1 Reply Latest reply on Feb 7, 2003 8:24 AM by mik

    foreign key constraint failed

    mik Newbie

      Hi,

      I'm using JBoss3.0.4 with MySQL.

      It seems to have a trouble with the table relation mapping when one Entity has a composed key.

      Description :
      I've got 2 Entities defined like this ....

      =========================================================

      <display-name>Application Entity Bean</display-name>
      <ejb-name>ApplicationEJB</ejb-name>

      <local-home>com.opensugar.manager.administration.ApplicationHome</local-home>
      com.opensugar.manager.administration.Application
      <ejb-class>com.opensugar.manager.administration.ApplicationBean</ejb-class>

      <persistence-type>Container</persistence-type>
      <prim-key-class>java.lang.String</prim-key-class>
      False
      <cmp-version>2.x</cmp-version>
      <abstract-schema-name>Application</abstract-schema-name>

      <cmp-field><field-name>name</field-name></cmp-field>
      <cmp-field><field-name>description</field-name></cmp-field>
      <cmp-field><field-name>maxLevel</field-name></cmp-field>

      <primkey-field>name</primkey-field>
      ......



      <display-name>UserGroup Entity Bean</display-name>
      <ejb-name>UserGroupEJB</ejb-name>

      <local-home>com.opensugar.manager.administration.UserGroupHome</local-home>
      com.opensugar.manager.administration.UserGroup
      <ejb-class>com.opensugar.manager.administration.UserGroupBean</ejb-class>

      <persistence-type>Container</persistence-type>
      <prim-key-class>com.opensugar.manager.administration.UserGroupPK</prim-key-class>
      False
      <cmp-version>2.x</cmp-version>
      <abstract-schema-name>UserGroup</abstract-schema-name>

      <cmp-field><field-name>name</field-name></cmp-field>
      <cmp-field><field-name>operatorName</field-name></cmp-field>
      <cmp-field><field-name>description</field-name></cmp-field>
      <cmp-field><field-name>dor</field-name></cmp-field>
      ......
      =========================================================

      Then I've got a many many relation ....

      =========================================================
      ejb-jar :

      <ejb-relation>
      <ejb-relation-name>Application-UserGroup</ejb-relation-name>

      <ejb-relationship-role>
      <ejb-relationship-role-name>usergroup-has-app</ejb-relationship-role-name>

      Many

      <relationship-role-source>
      <ejb-name>UserGroupEJB</ejb-name>
      </relationship-role-source>

      <cmr-field>
      <cmr-field-name>applications</cmr-field-name>
      <cmr-field-type>java.util.Collection</cmr-field-type>
      </cmr-field>
      </ejb-relationship-role>

      <ejb-relationship-role>
      <ejb-relationship-role-name>application-to-usergroup</ejb-relationship-role-name>

      Many

      <relationship-role-source>
      <ejb-name>ApplicationEJB</ejb-name>
      </relationship-role-source>

      <cmr-field>
      <cmr-field-name>userGroups</cmr-field-name>
      <cmr-field-type>java.util.Collection</cmr-field-type>
      </cmr-field>
      </ejb-relationship-role>
      </ejb-relation>



      jbosscmp-jdbc

      <ejb-relation>
      <ejb-relation-name>Application-UserGroup</ejb-relation-name>
      <relation-table-mapping>
      <table-name>application_usergroup</table-name>
      </relation-table-mapping>

      <ejb-relationship-role>
      <ejb-relationship-role-name>usergroup-has-app</ejb-relationship-role-name>
      <key-fields>
      <key-field>
      <field-name>name</field-name>
      <column-name>usergroup</column-name>
      </key-field>
      <key-field>
      <field-name>operatorName</field-name>
      <column-name>operator</column-name>
      </key-field>
      </key-fields>
      </ejb-relationship-role>

      <ejb-relationship-role>
      <ejb-relationship-role-name>application-to-usergroup</ejb-relationship-role-name>
      <key-fields>
      <key-field>
      <field-name>name</field-name>
      <column-name>application</column-name>
      </key-field>
      </key-fields>
      </ejb-relationship-role>
      </ejb-relation>
      =========================================================

      According to me all is OK in my xml files, is it ?

      But when I deployed my jar I've got an error because of JBoss failed to add a foreign key constraint on my table :(

      I've put it on debug mode and this is what it gives :
      =========================================================
      18:18:53,215 INFO [ApplicationEJB] Created table 'application_usergroup' successfully.
      18:18:53,215 DEBUG [ApplicationEJB] Executing SQL: ALTER TABLE application_usergroup ADD INDEX ind_application (application), ADD CONSTRAINT fk_application_user
      group_userGroups FOREIGN KEY (application) REFERENCES application (NAME)
      18:18:53,275 INFO [ApplicationEJB] Added foreign key constraint to table 'application_usergroup'
      18:18:53,275 DEBUG [ApplicationEJB] Executing SQL: ALTER TABLE application_usergroup ADD INDEX ind_usergroup, operator (usergroup, operator), ADD CONSTRAINT fk_
      application_usergroup_applications FOREIGN KEY (usergroup, operator) REFERENCES usergroup (NAME, OPERATORNAME)
      18:18:53,295 WARN [ApplicationEJB] Could not add foreign key constraint: table=application_usergroup
      18:18:53,295 WARN [ServiceController] Problem starting service jboss.j2ee:jndiName=osmadmin/UserGroupHome,service=EJB
      org.jboss.deployment.DeploymentException: Error while adding foreign key constraint; - nested throwable: (java.sql.SQLException: Syntax error or access violatio
      n: You have an error in your SQL syntax near ' operator (usergroup, operator), ADD CONSTRAINT fk_application_usergroup_applica' at line 1)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.addForeignKeyConstraint(JDBCStartCommand.java:384)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.addForeignKeyConstraint(JDBCStartCommand.java:295)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.execute(JDBCStartCommand.java:127)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.startStoreManager(JDBCStoreManager.java:457)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.start(JDBCStoreManager.java:369)
      at org.jboss.ejb.plugins.CMPPersistenceManager.start(CMPPersistenceManager.java:198)
      at org.jboss.ejb.EntityContainer.start(EntityContainer.java:376)
      at org.jboss.ejb.Container.invoke(Container.java:756)
      at org.jboss.ejb.EntityContainer.invoke(EntityContainer.java:1058)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:517)
      at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:978)
      at $Proxy5.start(Unknown Source)
      at org.jboss.system.ServiceController.start(ServiceController.java:398)
      at java.lang.reflect.Method.invoke(Native Method)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:517)
      at org.jboss.util.jmx.MBeanProxy.invoke(MBeanProxy.java:174)
      at $Proxy163.start(Unknown Source)
      at org.jboss.ejb.EjbModule.startService(EjbModule.java:430)
      at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:165)
      at java.lang.reflect.Method.invoke(Native Method)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:517)
      at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:978)
      at $Proxy5.start(Unknown Source)
      at org.jboss.system.ServiceController.start(ServiceController.java:398)
      at java.lang.reflect.Method.invoke(Native Method)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:517)
      at org.jboss.util.jmx.MBeanProxy.invoke(MBeanProxy.java:174)
      at $Proxy9.start(Unknown Source)
      at org.jboss.ejb.EJBDeployer.start(EJBDeployer.java:395)
      at org.jboss.deployment.MainDeployer.start(MainDeployer.java:807)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:621)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:585)
      at java.lang.reflect.Method.invoke(Native Method)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:517)
      at org.jboss.util.jmx.MBeanProxy.invoke(MBeanProxy.java:174)
      at $Proxy4.deploy(Unknown Source)
      at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymentScanner.java:435)
      at org.jboss.deployment.scanner.URLDeploymentScanner.scanDirectory(URLDeploymentScanner.java:656)
      at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.java:507)
      at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.doScan(AbstractDeploymentScanner.java:212)
      at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.loop(AbstractDeploymentScanner.java:225)
      at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.run(AbstractDeploymentScanner.java:202)
      + nested throwable:
      java.sql.SQLException: Syntax error or access violation: You have an error in your SQL syntax near ' operator (usergroup, operator), ADD CONSTRAINT fk_applicati
      on_usergroup_applica' at line 1
      at com.mysql.jdbc.MysqlIO.sendCommand(Unknown Source)
      at com.mysql.jdbc.MysqlIO.sqlQueryDirect(Unknown Source)
      at com.mysql.jdbc.MysqlIO.sqlQuery(Unknown Source)
      at com.mysql.jdbc.Connection.execSQL(Unknown Source)
      at com.mysql.jdbc.Connection.execSQL(Unknown Source)
      at com.mysql.jdbc.Statement.executeUpdate(Unknown Source)
      at com.mysql.jdbc.jdbc2.Statement.executeUpdate(Unknown Source)
      at org.jboss.resource.adapter.jdbc.local.LocalStatement.executeUpdate(LocalStatement.java:231)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.addForeignKeyConstraint(JDBCStartCommand.java:375)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.addForeignKeyConstraint(JDBCStartCommand.java:295)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCStartCommand.execute(JDBCStartCommand.java:127)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.startStoreManager(JDBCStoreManager.java:457)
      at org.jboss.ejb.plugins.cmp.jdbc.JDBCStoreManager.start(JDBCStoreManager.java:369)
      at org.jboss.ejb.plugins.CMPPersistenceManager.start(CMPPersistenceManager.java:198)
      at org.jboss.ejb.EntityContainer.start(EntityContainer.java:376)
      at org.jboss.ejb.Container.invoke(Container.java:756)
      at org.jboss.ejb.EntityContainer.invoke(EntityContainer.java:1058)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:517)
      at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:978)
      at $Proxy5.start(Unknown Source)
      at org.jboss.system.ServiceController.start(ServiceController.java:398)
      at java.lang.reflect.Method.invoke(Native Method)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:517)
      at org.jboss.util.jmx.MBeanProxy.invoke(MBeanProxy.java:174)
      at $Proxy163.start(Unknown Source)
      at org.jboss.ejb.EjbModule.startService(EjbModule.java:430)
      at org.jboss.system.ServiceMBeanSupport.start(ServiceMBeanSupport.java:165)
      at java.lang.reflect.Method.invoke(Native Method)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:517)
      at org.jboss.system.ServiceController$ServiceProxy.invoke(ServiceController.java:978)
      at $Proxy5.start(Unknown Source)
      at org.jboss.system.ServiceController.start(ServiceController.java:398)
      at java.lang.reflect.Method.invoke(Native Method)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:517)
      at org.jboss.util.jmx.MBeanProxy.invoke(MBeanProxy.java:174)
      at $Proxy9.start(Unknown Source)
      at org.jboss.ejb.EJBDeployer.start(EJBDeployer.java:395)
      at org.jboss.deployment.MainDeployer.start(MainDeployer.java:807)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:621)
      at org.jboss.deployment.MainDeployer.deploy(MainDeployer.java:585)
      at java.lang.reflect.Method.invoke(Native Method)
      at org.jboss.mx.capability.ReflectedMBeanDispatcher.invoke(ReflectedMBeanDispatcher.java:284)
      at org.jboss.mx.server.MBeanServerImpl.invoke(MBeanServerImpl.java:517)
      at org.jboss.util.jmx.MBeanProxy.invoke(MBeanProxy.java:174)
      at $Proxy4.deploy(Unknown Source)
      at org.jboss.deployment.scanner.URLDeploymentScanner.deploy(URLDeploymentScanner.java:435)
      at org.jboss.deployment.scanner.URLDeploymentScanner.scanDirectory(URLDeploymentScanner.java:656)
      at org.jboss.deployment.scanner.URLDeploymentScanner.scan(URLDeploymentScanner.java:507)
      at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.doScan(AbstractDeploymentScanner.java:212)
      at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.loop(AbstractDeploymentScanner.java:225)
      at org.jboss.deployment.scanner.AbstractDeploymentScanner$ScannerThread.run(AbstractDeploymentScanner.java:202)
      18:18:53,375 INFO [EjbModule] Started
      18:18:53,375 DEBUG [EJBDeployer] Deployed: file:/C:/Java/jboss-3.0.4_tomcat-4.1.12/server/default/deploy/Administration.jar
      18:18:53,375 DEBUG [MainDeployer] End deployment start on package: Administration.jar
      18:18:53,375 INFO [MainDeployer] Deployed package: file:/C:/Java/jboss-3.0.4_tomcat-4.1.12/server/default/deploy/Administration.jar
      18:18:53,375 DEBUG [URLDeploymentScanner] Watch URL for: file:/C:/Java/jboss-3.0.4_tomcat-4.1.12/server/default/deploy/Administration.jar -> file:/C:/Java/jboss
      -3.0.4_tomcat-4.1.12/server/default/deploy/Administration.jar
      18:18:53,385 ERROR [URLDeploymentScanner] MBeanException: Exception in MBean operation 'checkIncompleteDeployments()'
      Cause: Incomplete Deployment listing:
      Packages waiting for a deployer:

      Incompletely deployed packages:

      MBeans waiting for classes:

      MBeans waiting for other MBeans:
      [ObjectName: jboss.j2ee:jndiName=osmadmin/UserGroupHome,service=EJB
      state: FAILED
      I Depend On:
      Depends On Me: org.jboss.deployment.DeploymentException: Error while adding foreign key constraint; - nested throwable: (java.sql.SQLException: Syntax error or
      access violation: You have an error in your SQL syntax near ' operator (usergroup, operator), ADD CONSTRAINT fk_application_usergroup_applica' at line 1)]

      =========================================================
      It seems that the syntax ADD INDEX ind_usergroup, operator (usergroup, operator) is not good !!!

      The MySQL manual :
      ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]

      alter_specification:
      ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
      or ADD [COLUMN] (create_definition, create_definition,...)
      or ADD INDEX [index_name] (index_col_name,...)
      or ADD PRIMARY KEY (index_col_name,...)
      or ADD UNIQUE [index_name] (index_col_name,...)
      or ADD FULLTEXT [index_name] (index_col_name,...)
      or ADD [CONSTRAINT symbol] FOREIGN KEY index_name (index_col_name,...)
      [reference_definition]
      ......


      Can you help me ?

        • 1. Re: foreign key constraint failed
          mik Newbie

          It comes from standardjbosscmp-jdbc.xml :

          <fk-constraint-template>ALTER TABLE ?1 ADD INDEX ind_?3 (?3), ADD CONSTRAINT ?2 FOREIGN KEY (?3) REFERENCES ?4 (?5)</fk-constraint-template>


          The name of the index becomes :
          "ind_field1, field2"

          How can I correct that ?