db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Justin Patterson <jus...@pattersonhouse.net>
Subject Question about delete-connected restrictions
Date Wed, 01 Feb 2006 02:57:21 GMT
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



Mime
View raw message