Re: Foreign key error, please help.

On Wed, 9 Mar 2005, Kumar S wrote:

> Hi Stephan,
> Thank you very much, it was my stupid mistake.
>
> One more question. aplogies if this is also not so
> worthy question.
> In a transaction does currval work only if a previous
> statement defines it. In my case I wanted to get FK
> from existing currval in sample table (sample_id).
> when I execute my statements in a transasction, I get
> the following error. Do I have to just use a SELECT
> statement here, because currval is not working? Or did
> i made a mistake instead?

Currval is effectively defined as the most recent value of the sequence
given from nextval in this session. If you haven't called nextval, it
doesn't have a value to give you. But, it's session based, not transaction
based.

In the case below what value of sample_id were you looking for? The "most
recent" one is hard to define because you could have another transaction
that's added one but hasn't committed yet.

> My statement: BEGIN TRANSACTION; INSERT INTO
> HYBRID_LABEL(sample_id,hyb_extraction_method,hyb_rna_mrna_genomicdna_extracted,
> hyb_amplification_pcr_rnapol,hyb_amount_nacid_labeled,hyb_label_used,
> hyb_label_incorporation_method,hyb_solution,hyb_blocking_agent,
> hyb_wash_procedure,hyb_quantity_labeled_target,hyb_time,
> hyb_concentration,hyb_volume,hyb_temp,hyb_instrument_desc)
> VALUES(currval('sample_sample_id_seq'),'NA','NA','NA','NA','NA',
> 'NA','NA','NA','NA','NA','NA','NA','NA','NA','NA');