It looks like that is, indeed, the problem. I just commented out that
DDL-time check and now it's working fine. I realize that I have nothing
to cover me at run-time, but the situation is not going to come up in my
app. I'll just upgrade when the fix makes it in officially.
Thanks!
-Justin
Satheesh Bandaram wrote:
Hi Justin,
You may be hitting DERBY-338
<http://issues.apache.org/jira/browse/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
