db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John English <checkpoint...@gmail.com>
Subject "Conglomerate could not be created"?
Date Fri, 01 Jun 2012 12:19:05 GMT
I'm having trouble with the following error: "Conglomerate could not be
created". It happens when I do this:

   INSERT INTO resource_usage (resid,itemid,itemtype)
     (SELECT resid,?,? FROM resource_usage
      WHERE itemid=?
      AND   itemtype=?
      AND   NOT EXISTS (SELECT resid FROM resource_usage
                        WHERE itemid=? AND itemtype=?));

The first two and last two parameters are the same; the idea is that an
item uses a set of resources, and I want to copy the item from one table
where it's being edited to another where it is publicly visible. To do
this I need to duplicate the set of resource IDs so that they appear
for the item being edited (which has an ID and an item type of 'temp')
and the released version (another ID and type 'final'), but without
creating duplicate rows if the same resources were already listed
prior to the editing process. For example:

   INSERT INTO resource_usage (resid,itemid,itemtype)
     (SELECT resid,55,'final' FROM resource_usage
      WHERE itemid=11
      AND   itemtype='temp'
      AND   NOT EXISTS (SELECT resid FROM resource_usage
                        WHERE itemid=55 AND itemtype='final'));

The resource_usage table is defined like this:

   CREATE TABLE resource_usage (
     resid       INTEGER       NOT NULL,
     itemid      INTEGER       NOT NULL,
     itemtype    VARCHAR(16)   NOT NULL,
     CONSTRAINT resusage_1     FOREIGN KEY (resid)
                               REFERENCES resources(id)
                               ON DELETE CASCADE,
     CONSTRAINT resusage_2     UNIQUE(resid,itemid,itemtype)
   );

So to create a new item I insert a copy of the item from the editing
table into the final destinationtable, then try to do the above insert
on resource_usage for the newly created item, and get "Conglomerate
could not be created".

If I stop after copying the item to the public table but before the
insert above and then try each of the selects in turn manually, I get
this:

   SELECT resid FROM resource_usage WHERE itemid=55 AND itemtype='final';
            -- 0 rows selected

   SELECT resid,55,'final' FROM resource_usage
      WHERE itemid=11
      AND   itemtype='temp'
      AND   NOT EXISTS (SELECT resid FROM resource_usage
                        WHERE itemid=55 AND itemtype='final');
            -- 4 rows selected:
RESID 	| ITEMID | ITEMTYPE
--------+--------+-------
29      | 11     | temp
30      | 11     | temp
31      | 11     | temp
32      | 11     | temp

   SELECT resid,55,'final' FROM resource_usage
      WHERE itemid=11
      AND   itemtype='temp'
      AND   NOT EXISTS (SELECT resid FROM resource_usage
                        WHERE itemid=55 AND itemtype='final');
            -- 4 rows selected:
RESID 	| 2 	| 3
--------+-------+-------
29      | 55    | final
30      | 55    | final
31      | 55    | final
32      | 55    | final

On the other hand I can do the insert manually:

   INSERT INTO resource_usage (resid,itemid,itemtype) VALUES
      (29,55,'final'),(30,55,'final'),(31,55,'final'),(32,55,'final');
            -- 4 rows inserted

Can anybody throw any light on what's going on here please?

-- 
John English

Mime
View raw message