cmr and database constraint

[benholland]

So I got CMR working and it is v cool.

Anyway: Does JBoss limit you to relying on the Container/AppServer to enforce referential integrity.Or can
I get JBoss to work with DB Foreign key constraints.

I have an entity Customer that has a reference to the entity CustomerDetails. Here is the sql.

Customer
CREATE TABLE mvdc_customer(
customer_id INT NOT NULL,
name VARCHAR(30) NOT NULL,
pword VARCHAR(30) NOT NULL,
PRIMARY KEY (customer_id)
) TYPE=INNODB;

CREATE TABLE mvdc_customer_details(
pk_id INT NOT NULL,
fk_customer_id INT NOT NULL,
customer_id INT NOT NULL,
email_address VARCHAR(50) NOT NULL,
PRIMARY KEY (fk_customer_id,pk_id),
FOREIGN KEY (fk_customer_id) REFERENCES mvdc_customer (customer_id) ON DELETE CASCADE
) TYPE=INNODB;

I currently have a foreign key constraint set up on customer details.
If I manually add rows I can call get CustomerDetails on Customer easy. However, if I do a home.create() I get the following error:

java.sql.SQLException: General error, message from server: "Column 'fk_customer
_id' cannot be null"
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1628)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:886)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:945)
at com.mysql.jdbc.Connection.execSQL(Connection.java:1809)

[tommyg]

With JBoss3.0.x foreign keys can not be null, as far as I know. JBoss3.2 RC3 is different though. If I have a situation like you do, I can describe each field-even foreign key fields-using setX and getX. I can then use a 'many' table that has a foreign key constraint of not null.

Then, I simply add the many table using ejbCreate and in my ejbCreate method I populate the foreign key value. This allows me to use CMP, CMR with a database that requires the foreign key to be null.

Anybody have an idea on when JBoss3.2 will hit final?

[tommyg]

That is, with JBoss3.0.X foreign keys must be NOT NULL.

[carlolf]

I discovered the same problem with a simple cmr 1:n example i have tried to get run. Obviously JBoss does not generate a value for the foreign key needed on the dependant table in the sql statement.
I was very shocked, because i thought this is one of the simplest task you can do with EJB2.0 cmr.
Given this experience, i do not understand, why the test examples with cmr are running without errors.

Is there a solution or patch available?

[benholland]

I got round my problem by using indexes and allow null in my sql. Here is the mysql schema:

CREATE TABLE mvdc_customer_details(
pk_id INT NOT NULL,
fk_customer_id INT NULL,
email_address VARCHAR(50) NOT NULL,
INDEX cust_ind(fk_customer_id),
PRIMARY KEY (pk_id),
FOREIGN KEY (fk_customer_id) REFERENCES mvdc_customer (customer_id) ON Delete SET NULL
) TYPE=INNODB;

Jboss will set the fk fields null when I do a delete.
And it works. now on to one to many which doesnt!


Ben.

[tommyg]

One workaround or solution (may not be the best) is to use JBoss3.2RC3. And use it this way.

For add: In a one-to-may relationship, add the 'one' table with ejbCreate, then add the 'many' table with ejbCreate-populating the foreign key field in the ejbCreate method. Because with JBoss3.2RC3 you can now define setX and getX for foreign key fields.

For update and delete it should be how you would expect it.

[joerk]

Hello,

where can I find information how JBoss3.2RC4 handels CMR?

add: I set the relation fields in ejbPostCreate() - OK, works fine

update: OK, works fine

delete: JBoss first want to update the foreign key field to NULL - UPDATE table set FK_ID = null where ID = X - The Database fires the NOT NULL constraint. I have no idea who to handel the remove() ? Any hints ?

Thanks Joerk