db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Keith Irwin <keith.ir...@gmail.com>
Subject Re: have identity_val_local(), want table.curr_id
Date Thu, 16 Nov 2006 18:22:56 GMT

On Nov 16, 2006, at 7:05 AM, Daniel John Debrunner wrote:

> Keith Irwin wrote:
>> Folks---
>> I want to run something similar in a single transaction (just a  
>> loop that runs each query one after another then commits):
>> Assuming table (shorthand):
>>   order (order_id autoincrement, name)
>>   items (item_id autoincrement, order_id, name)
>> And queries:
>>   insert into orders (name) values ('foo');
>>   insert into items (order_id, other) values (identity_val_local 
>> (), 'bar');
>> This works fine.   It picks up the order_id inserted automatically  
>> (via autoincrement) on the order table and uses it in the items  
>> table foreign key.
>> However, when I do the following:
>>   insert into orders (name) values ('foo');
>>   insert into items (order_id, other) values (identity_val_local 
>> (), 'bar');
>>   insert into items (order_id, other) values (identity_val_local 
>> (), 'quux');
>> I get a constraint error because the second identity_val_local()  
>> call refers to the recent item table autoincrement, and not the  
>> order autoincremented key (which makes sense reading the docs).
>> Is there some way to do the above without having to use:
> Can you use a single INSERT statement for the items?
> insert into items (order_id, other) values
>       (identity_val_local(), 'bar'),
>       (identity_val_local(), 'quux');

Hm. I think I could do that if I construct the SQL statement each  
time I need to, rather than using a constant.  What I'd really love  
is the ability to pass in the name of the table for the  
identity_val_local() function.  That would fix everything and not  
require sequences! ;)


> Dan.

View raw message