Обсуждение: Foreign key error, please help.
Dear group,
when I try to insert a statement :
INSERT INTO
processed_data(exp_id,seq_anno_id,cel_id,proc_symbol,proc_exprs,proc_pval)VALUES((currval('experiment_exp_id_seq')),(SELECT
platform_id from platform where platform_filename =
'RGeo-ESHSC-H1ES1-1a-U133A'),(SELECT seq_anno_id FROM
sequence_annotation, genechip WHERE
sequence_annotation.seq_probeset_id =
'AFFX-r2-Ec-bioB-M_at'AND
sequence_annotation.genechip_id = genechip.genechip_id
AND genechip.genechip_array =
'U133A'),'AFFX-r2-Ec-bioB-M_at',6.419,0.1671675);
I get a weird error:
Now I took the :
bob=> (SELECT seq_anno_id FROM sequence_annotation,
genechip WHERE sequence_annotation.seq_probeset_id =
'AFFX-r2-Ec-bioB-M_at'AND
sequence_annotation.genechip_id = genechip.genechip_id
AND genechip.genechip_array = 'U133A');
seq_anno_id
-------------
45881
(1 row)
Why is it so?
When I give the select statement seperate I get the
answer and when I embed the same in INSERT statement I
get the following error:
ERROR: insert or update on table "processed_data"
violates foreign key constraint "$3"
DETAIL: Key (seq_anno_id)=(6) is not present in table
"sequence_annotation".
My sequence_annotation table:
seq_anno_id | integer |
not null default
nextval('public.sequence_annotation_seq_anno_id_
seq'::text)
seq_probeset_id | character varying(15000) |
seq_source | character varying(15000) |
.......................|.........................
Indexes:
"sequence_annotation_pkey" primary key, btree
(seq_anno_id)
Foreign-key constraints:
"$1" FOREIGN KEY (genechip_id) REFERENCES
genechip(genechip_id)
My Processed data table:
proc_id | integer | not null
default
nextval('public.processed_data_proc_id_seq'::text)
exp_id | integer |
seq_anno_id | integer |
cel_id | integer |
proc_symbol | character varying(100) |
proc_exprs | real |
proc_pval | real |
Indexes:
"processed_data_pkey" primary key, btree (proc_id)
Foreign-key constraints:
"$2" FOREIGN KEY (exp_id) REFERENCES
experiment(exp_id)
"$3" FOREIGN KEY (seq_anno_id) REFERENCES
sequence_annotation(seq_anno_id)
"$4" FOREIGN KEY (cel_id) REFERENCES cel(cel_id)
Sorry if the wrapping of text makes it uncomfortable
for you to read.
__________________________________
Celebrate Yahoo!'s 10th Birthday!
Yahoo! Netrospective: 100 Moments of the Web
http://birthday.yahoo.com/netrospective/
On Tue, 8 Mar 2005, Kumar S wrote:
> INSERT INTO
> processed_data(exp_id,seq_anno_id,cel_id,proc_symbol,proc_exprs,proc_pval)VALUES(
> (currval('experiment_exp_id_seq')),(SELECT
> platform_id from platform where platform_filename =
> 'RGeo-ESHSC-H1ES1-1a-U133A'),(SELECT seq_anno_id FROM
> sequence_annotation, genechip WHERE
> sequence_annotation.seq_probeset_id =
> 'AFFX-r2-Ec-bioB-M_at'AND
> sequence_annotation.genechip_id = genechip.genechip_id
> AND genechip.genechip_array =
> 'U133A'),'AFFX-r2-Ec-bioB-M_at',6.419,0.1671675);
It looks to me like the select platform_id subselect is in the column
position for seq_anno_id not the select seq_anno_id subselect which
doesn't seem to be what's intended.
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?
Thank you.
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');
bob=> \i /home/torun/ESHSC-H1ES/test.sql
BEGIN
psql:/home//torun/ESHSC-H1ES/test.sql:2: ERROR:
currval of sequence "sample_sample_id_seq" is not yet
defined in this session
bob=> rollback
--- Stephan Szabo <sszabo@megazone.bigpanda.com>
wrote:
> On Tue, 8 Mar 2005, Kumar S wrote:
>
> > INSERT INTO
> >
>
processed_data(exp_id,seq_anno_id,cel_id,proc_symbol,proc_exprs,proc_pval)VALUES(
> > (currval('experiment_exp_id_seq')),(SELECT
> > platform_id from platform where platform_filename
> =
> > 'RGeo-ESHSC-H1ES1-1a-U133A'),(SELECT seq_anno_id
> FROM
> > sequence_annotation, genechip WHERE
> > sequence_annotation.seq_probeset_id =
> > 'AFFX-r2-Ec-bioB-M_at'AND
> > sequence_annotation.genechip_id =
> genechip.genechip_id
> > AND genechip.genechip_array =
> > 'U133A'),'AFFX-r2-Ec-bioB-M_at',6.419,0.1671675);
>
> It looks to me like the select platform_id subselect
> is in the column
> position for seq_anno_id not the select seq_anno_id
> subselect which
> doesn't seem to be what's intended.
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
On Wed, Mar 09, 2005 at 06:46:45 -0800, Kumar S <ps_postgres@yahoo.com> 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? If you haven't used nextval in the current session, currval doesn't have a value yet, so you can't use it. Which value would you expect it to use?
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');
--- Kumar S <ps_postgres@yahoo.com> 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? > > Thank you. curval reports the most recent value of nextval (serial datatype). nextval automatically occurs on an insert. therefore, when you do an insert, you don't need to specifically call nextval, it has already been done by postgres. yes, you need to SELECT the fk from the row in question if you have not done and insert or if nextval was never called. __________________________________ Celebrate Yahoo!'s 10th Birthday! Yahoo! Netrospective: 100 Moments of the Web http://birthday.yahoo.com/netrospective/