Hi Justin,
You may be hitting DERBY-338. I
think the problem there is Derby tries to catch possible conflict in
cascade delete actions at DDL time, when a runtime checking is what is
needed. In your case, you said, you don't expect a conflict at run-time.
There is a partial patch pending for DERBY-338 for long time now. It is
probably out of sync with the code. Do you think that could be the
problem you are seeing? Is there anyway you can define your DDL without
"conflicting actions" as Derby seems to think? Derby also has triggers,
if you could use that for part of your schema.
Satheesh
Justin Patterson wrote:
Hey
Derby Users,
I'm designing an application to keep up with my music collection. It's
currently running fine on Hibernate/One$DB until I execute 20k or so
transactions. Then, the JVM runs out of memory. After profiling it, I
determined that the leaks are related to One$DB and that's just the
final straw for me. I submitted some questions to the One$DB forums,
but they're not very active and there doesn't seem to be a new version
forthcoming. So, I'm shopping aroiund for a better embedded RDBMS and
I've heard great things about Derby.
After porting my schema over, I ran into something that's peculiar (to
me, anyway) and I'd appreciate any advice that you can give. My
apologies if this is not Derby-specific, but I didn't have any problems
doing this in my schema with either MySQL or One$DB.
Here are the important snippets from my DDL script (I snipped the
irrelevant columns and tables):
CREATE TABLE playable (
id INTEGER GENERATED ALWAYS AS IDENTITY,
CONSTRAINT pk_playable_id PRIMARY KEY (id)
);
CREATE TABLE track (
id INTEGER,
CONSTRAINT pk_track_id PRIMARY KEY (id),
CONSTRAINT fk_track_id FOREIGN KEY (id) REFERENCES playable_t(id) ON
DELETE CASCADE
);
CREATE TABLE mediafile (
id INTEGER,
trackid INTEGER,
CONSTRAINT pk_mediafile_id PRIMARY KEY (id),
CONSTRAINT fk_mediafile_id FOREIGN KEY (id) REFERENCES playable_t(id)
ON DELETE CASCADE,
CONSTRAINT fk_mediafile_trackid FOREIGN KEY (trackid) REFERENCES
track_t(id) ON DELETE SET NULL
);
When I run that, I get:
ERROR 42915: Foreign Key 'FK_MEDIAFILE_TRACKID' is invalid because
'the delete rule of foreign key must be CASCADE. (The relationship
would cause the table to be delete-connected to the same table through
multiple relationships and such relationships must have the same delete
rule (NO ACTION, RESTRICT or CASCADE).) '
So, I looked it up in the DB2 docs and I understand where the error is
coming from, but I don't see how to design around it. What I want is
to have tracks and mediafiles with unique IDs (across both tables).
The playable table should achieve that. If the parent playable is
deleted, I want the track or mediafile subclass (in Hibernate/Java
terms) to be deleted as well. If the parent track of a mediafile is
deleted, I want the mediafile's trackid to be set to NULL.
I think that I understand why the delete-connected restriction is
there, but since I have the foreign keys in two separate columns and
the parent rows of any involved track and mediafile rows must be
dependent on different rows in the playable table, I don't think that
it should be an issue for me (logically, anyway).
Can anyone think of a workaround (or a better way to design the
schema)? I thought about using a shared sequence, but that's not
avaiable either.
Thanks for your help!
-Justin
|
