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
