8 Replies Latest reply on Sep 6, 2004 10:15 PM by Stjepan Brbot

    Delete Foreign Key Object

    Marcus Cordes Newbie

      Hello,

      I´m using JBOSS 3.23 with mysql
      How can I prevent to delete the foreign key component when its still in use in the primary key object:

      I have two Beans BEAN A and BEAN B
      Both have an autoincrement ID
      and the ID of Bean A shall be the foreign key of BEAN B.

      This already works fine,
      but I can still dlete Bean A when its used as foreign key in Bean B
      How can I automatically prevent this?

      And an easier question: How can I make the field BeanA_name a NOT NULL field?


       <entity>
       <ejb-name>BeanAEJB</ejb-name>
       <local-home>liato.ejb.BeanAHomeLocal</local-home>
       <local>liato.ejb.BeanALocal</local>
       <ejb-class>liato.ejb.BeanABean</ejb-class>
       <persistence-type>Container</persistence-type>
       <prim-key-class>java.lang.Object</prim-key-class>
       <reentrant>False</reentrant>
       <cmp-version>2.x</cmp-version>
       <abstract-schema-name>BeanABean</abstract-schema-name>
       <cmp-field><field-name>BeanA_Name</field-name></cmp-field>
       <query>
       <query-method>
       <method-name>ejbSelectBeanAList</method-name>
       <method-params>
       </method-params>
       </query-method>
       <ejb-ql>
       SELECT OBJECT(o) FROM BeanABean AS o
       </ejb-ql>
       </query>
       </entity>


       <entity>
       <ejb-name>BeanBEJB</ejb-name>
       <local-home>liato.ejb.BeanBHomeLocal</local-home>
       <local>liato.ejb.BeanBLocal</local>
       <ejb-class>liato.ejb.BeanBBean</ejb-class>
       <persistence-type>Container</persistence-type>
       <prim-key-class>java.lang.Object</prim-key-class>
       <reentrant>False</reentrant>
       <cmp-version>2.x</cmp-version>
       <abstract-schema-name>BeanBBean</abstract-schema-name>
       <cmp-field><field-name>BeanB_Name</field-name></cmp-field>
       <cmp-field><field-name>BeanA_Id</field-name></cmp-field>
       <ejb-local-ref >
       <ejb-ref-name>BeanALocalRef</ejb-ref-name>
       <ejb-ref-type>Entity</ejb-ref-type>
       <local-home>liato.ejb.BeanAHomeLocal</local-home>
       <local>liato.ejb.BeanALocal</local>
       <ejb-link>BeanA</ejb-link>
       </ejb-local-ref>
       </entity>


      The relationship in the ejb.jar

      <ejb-relation>
      <ejb-relation-name>BeanB-BeanA</ejb-relation-name>
      <ejb-relationship-role>
      <ejb-relationship-role-name>beanA-has-beanB</ejb-relationship-role-name>
      <multiplicity>Many</multiplicity>
      <relationship-role-source>
      <ejb-name>BeanBEJB</ejb-name>
      </relationship-role-source>
      <cmr-field>
      <cmr-field-name>BeanA</cmr-field-name>
      </cmr-field>
      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>beanB-has-beanA</ejb-relationship-role-name>
      <multiplicity>One</multiplicity>
      <relationship-role-source>
      <ejb-name>BeanAEJB</ejb-name>
      </relationship-role-source>
      </ejb-relationship-role>
      </ejb-relation>



      and the Jbosscmp-jdbc.xml

      <jbosscmp-jdbc>
       <defaults>
       <datasource>java:/MySqlDS</datasource>
       <datasource-mapping>mySQL</datasource-mapping>
       <create-table>true</create-table>
       <remove-table>false</remove-table>
       <fk-constraint>true</fk-constraint>
       </defaults>
      
       <entity>
       <ejb-name>BeanAEJB</ejb-name>
       <table-name>BEANA</table-name>
       <cmp-field>
       <field-name>BeanA_Name</field-name>
       <column-name>BEANA_NAME</column-name>
       </cmp-field>
       <unknown-pk>
       <unknown-pk-class>java.lang.Integer</unknown-pk-class>
       <field-name>BeanA_Id</field-name>
       <column-name>BEANA_ID</column-name>
       <jdbc-type>INTEGER</jdbc-type>
       <sql-type>INT(11)</sql-type>
       <auto-increment/>
       </unknown-pk>
       <entity-command name="mysql-get-generated-keys"
       class="org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCMySQLCreateCommand"/>
       </entity>
      
      
       <entity>
       <ejb-name>BeanBEJB</ejb-name>
       <table-name>BEANB</table-name>
       <cmp-field>
       <field-name>BeanB_Name</field-name>
       <column-name>BEANB_NAME</column-name>
       </cmp-field>
       <cmp-field>
       <field-name>BeanB_Id</field-name>
       <column-name>BEANB_ID</column-name>
       </cmp-field>
       <unknown-pk>
       <unknown-pk-class>java.lang.Integer</unknown-pk-class>
       <field-name>BeanB_Id</field-name>
       <column-name>BEANB_ID</column-name>
       <jdbc-type>INTEGER</jdbc-type>
       <sql-type>INT(11)</sql-type>
       <auto-increment/>
       </unknown-pk>
       <entity-command name="mysql-get-generated-keys"
       class="org.jboss.ejb.plugins.cmp.jdbc.keygen.JDBCMySQLCreateCommand"/>
       </entity>




      and the relationship in jbosscmp

      <ejb-relation>
      <ejb-relation-name>BeanB-BeanA</ejb-relation-name>
      <foreign-key-mapping />
      <ejb-relationship-role>
      <ejb-relationship-role-name>BeanA-has-BeanB</ejb-relationship-role-name>
      <fk-constraint>true</fk-constraint>
      <key-fields/>
      </ejb-relationship-role>
      <ejb-relationship-role>
      <ejb-relationship-role-name>BeanB-has-BeanA</ejb-relationship-role-name>
      <fk-constraint>true</fk-constraint>
      <key-fields>
      <key-field>
      <field-name>BeanA_Id</field-name>
      <column-name>BEANA_ID</column-name>
      </key-field>
      </key-fields>
      </ejb-relationship-role>
      </ejb-relation>


        • 1. Re: Delete Foreign Key Object
          Marcus Cordes Newbie

          My Tables after the create look like this:

          CREATE TABLE `BEANB` (
           `BEANB_NAME` varchar(250) binary default NULL,
           `BEANA_ID` int(11) default NULL,
           `BEANB_ID` int(11) NOT NULL auto_increment,
           PRIMARY KEY (`BEANB_ID`)
          ) TYPE=MyISAM;
          
          CREATE TABLE `BEANA` (
           `BEANA_NAME` varchar(250) binary default NULL,
           `BEANA_ID` int(11) NOT NULL auto_increment,
           PRIMARY KEY (`BEANA_ID`)
          ) TYPE=MyISAM;


          No ALTER TABLE command or something like this....



          • 2. Re: Delete Foreign Key Object
            Stjepan Brbot Newbie

            First of all you have to know that MyISAM table type in MySQL is obsolete because it does NOT support referential integrities! It is strongly recommended to use InnoDB database type where you can implement referentional integrities. Database without referential integrities and without transactional management cannot be treated as real database!

            I suggest the following SQL;

            # Host: localhost
            # Database: test
            # Table: 'beana'
            #
            CREATE TABLE `beana`
            (
             `BEANA_ID` int(11) NOT NULL auto_increment,
             `BEANA_NAME` varchar(250) binary NOT NULL,
             PRIMARY KEY (`BEANA_ID`)
            ) TYPE=InnoDB;
            
            # Host: localhost
            # Database: test
            # Table: 'beanb'
            #
            CREATE TABLE `beanb`
            (
             `BEANB_ID` int(11) NOT NULL auto_increment,
             `BEANA_ID` int(11) default NULL,
             `BEANB_NAME` varchar(250) binary NOT NULL,
             PRIMARY KEY (`BEANB_ID`),
             KEY `BEANA_FK_IDX` (`BEANA_ID`),
             CONSTRAINT `beana_FK` FOREIGN KEY (`BEANA_ID`) REFERENCES `beana` (`BEANA_ID`)
            ) TYPE=InnoDB;
            


            With this MySQL database model you're not allowed to delete row from table BEANA if it is referenced by one or more rows from BEANB (regarding FK). If you need cascade delete then change previous code:

            CONSTRAINT `beana_FK` FOREIGN KEY (`BEANA_ID`) REFERENCES `beana` (`BEANA_ID`) ON DELETE CASCADE
            


            Take into account that all NOT NULL columns must be implemented as parameters of ejbCreate(...) method of EJB.

            • 3. Re: Delete Foreign Key Object
              Marcus Cordes Newbie

              OK, thanks for your answer.

              maybe I missed something in my question.
              I know how to set a NOT NULL Command inside the database, and also how to set the fk-constraints, but I want JBOSS to do this...

              My table shall look like the one you suggested, but in my JBOSSCMP-JDBC I want to use the create table = true command

              <defaults>
               <datasource>java:/MySqlDS</datasource>
               <datasource-mapping>mySQL</datasource-mapping>
               <create-table>true</create-table> <remove-table>false</remove-table>
               <fk-constraint>true</fk-constraint>
               </defaults>


              and the automatically created database shall include the following lines:
              KEY `BEANA_FK_IDX` (`BEANA_ID`),
               CONSTRAINT `beana_FK` FOREIGN KEY (`BEANA_ID`) REFERENCES `beana` (`BEANA_ID`)
              ) TYPE=InnoDB;


              so how do I have to configurate my xml`s for creating innoDB and creating foreign key constraints?

              Thanks for reply

              • 4. Re: Delete Foreign Key Object
                Marcus Cordes Newbie

                Anyone?

                Anything not clear in my question?

                • 5. Re: Delete Foreign Key Object
                  Stjepan Brbot Newbie

                  Although Alexey Labourney (JBoss CMP developer) should answer this question, I can only say that MySQL database with its database type variants (MyISAM, InnoDB, etc.) is not fully supported by JBoss in terms of foreign keys autocreation. As far as you know MySQL is specific in that way that it requires InnoDB (which is not default DB type). If you want foreign keys constraints, additionally MySQL (or InnoDB) expects from you to manually create indexes for FK columns in advance. I'm not sure if these requirements are managed by JBoss <fk-constraints>true</fk-constraints> declaration in deployment descriptor.

                  • 6. Re: Delete Foreign Key Object
                    Stjepan Brbot Newbie

                    Sorry, Alexey Loubyansky (not Labourney).

                    • 7. Re: Delete Foreign Key Object
                      Alexey Loubyansky Master

                      If you have fk-contraint true and JBossCMP creates the tables, foreign key constraints will also be created using ALTER TABLE statement.

                      • 8. Re: Delete Foreign Key Object
                        Stjepan Brbot Newbie

                        In MySQL ALTER TABLE is not enough! Tables have to be of InnoDB type and indexes for foreign keys have to be created before creation of references! ALTER TABLE executes fine on MyISAM tables but do nothing in terms of relations and referential integrity.