Problem with JBOSS 3.0.3 and CMP 2.0
The problen is with a bidirectional relation one to many .
jbosscmp-jdbc.xml is:
<ejb-relation>
<ejb-relation-name>party-event</ejb-relation-name>
<foreign-key-mapping/>
<ejb-relationship-role>
<ejb-relationship-role-name>PartyHasEvents</ejb-relationship-ole-name>
<key-fields>
<key-field>
<field-name>year</field-name>
<column-name>anio</column-name>
</key-field>
<key-field>
<field-name>nplan</field-name>
<column-name>nplan</column-name>
</key-field>
</key-fields>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>EventInAParty</ejb-relationship-role-name>
<fk-constraint>true</fk-constraint>
<key-fields/>
</ejb-relationship-role>
</ejb-relation>
In deployment state, I get the following message:
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 ' anio (nplan, anio), ADD CONSTRAINT fk_eventos_party FOREIGN KEY (nplan, anio) R' at line 1)
The sql script is:
CREATE TABLE events (id DECIMAL(10, 0) NOT NULL, sucu INTEGER NOT NULL, parti INTEGER NOT NULL, total INTEGER NOT NULL, totalr INTEGER NOT NULL, totalp INTEGER NOT NULL, totalc INTEGER NOT NULL, totalt INTEGER NOT NULL, situ INTEGER NOT NULL, nplan VARCHAR(2) not null, anio VARCHAR(4) not null, CONSTRAINT pk_events PRIMARY KEY (id))
CREATE TABLE parties (anio VARCHAR(4) not null NOT NULL, nplan VARCHAR(2) not null NOT NULL, mes VARCHAR(2), f_instal DATETIME, total INTEGER NOT NULL, CONSTRAINT pk_parties PRIMARY KEY (anio, nplan))
ALTER TABLE events ADD INDEX ind_nplan, anio (nplan, anio), ADD CONSTRAINT fk_events_party FOREIGN KEY (nplan, anio) REFERENCES parties (anio, nplan)
The ADD INDEX sentence is invalid. Is anyway of avoiding this?
With JBOSS 3.0.0 I get a right sql script (without an ADD INDEX sentence) :
CREATE TABLE events (id DECIMAL(10, 0) NOT NULL, sucu INTEGER NOT NULL, parti INTEGER NOT NULL, total INTEGER NOT NULL, totalr INTEGER NOT NULL, totalp INTEGER NOT NULL, totalc INTEGER NOT NULL, totalt INTEGER NOT NULL, situ INTEGER NOT NULL, nplan VARCHAR(2) not null, anio VARCHAR(4) not null, CONSTRAINT pk_events PRIMARY KEY (id))
CREATE TABLE parties (anio VARCHAR(4) not null NOT NULL, nplan VARCHAR(2) not null NOT NULL, mes VARCHAR(2), f_instal DATETIME, total INTEGER NOT NULL, CONSTRAINT pk_parties PRIMARY KEY (anio, nplan))
ALTER TABLE events ADD CONSTRAINT fk_events_party FOREIGN KEY (nplan, anio) REFERENCES parties (anio, nplan)
Had anybody a similar problem?
Thank you