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
