Delete Foreign Key Object

[hustenbolschen]

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>

[hustenbolschen]

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....

[sbrbot]

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.

[hustenbolschen]

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

[hustenbolschen]

Anyone?

Anything not clear in my question?

[sbrbot]

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.

[sbrbot]

Sorry, Alexey Loubyansky (not Labourney).

[aloubyansky]

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

[sbrbot]

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.