From: | Brice André <brice(at)famille-andre(dot)be> |
---|---|
To: | Francisco Calderón <fjcalderon(at)gmail(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Problem with sequence increment |
Date: | 2012-01-25 14:31:04 |
Message-ID: | CAOBG12kkZcOLDmxuGgAAVp6bz_s5trjMrJ50ogYuxSponeUZWA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Hello,
This is a normal behaviour of the sequence. In case of rollback, a sequence
is never decremented. Read the note at the end of this page for more info :
http://www.postgresql.org/docs/8.1/static/functions-sequence.html
Regards,
Brice
2012/1/25 Francisco Calderón <fjcalderon(at)gmail(dot)com>
> Hello,
>
> I am having a situation with postgresql 8.3, i have two tables, ta and tb,
> with a relation "one tb has many ta" and... well, i will let the SQL talk
> for me ;)
>
> -----------SQL-----------
> CREATE TABLE tb
> (
> id serial NOT NULL,
> descripcion character varying(200) NOT NULL,
> CONSTRAINT tb_pkey PRIMARY KEY (id)
> )
> WITH (OIDS=FALSE);
> INSERT INTO tb (descripcion) values ('desc 1');
> INSERT INTO tb (descripcion) values ('desc 2');
> CREATE TABLE ta
> (
> id serial NOT NULL,
> descripcion character varying(200),
> tb_id integer default null,
> CONSTRAINT ta_pkey PRIMARY KEY (id),
> CONSTRAINT ta_tb_id FOREIGN KEY (tb_id)
> REFERENCES tb (id) MATCH SIMPLE
> ON UPDATE NO ACTION ON DELETE NO ACTION
> )
> WITH (OIDS=FALSE);
>
> When i make an insert like this:
>
> INSERT INTO ta (descripcion, tb_id) values ('prueba', 0);
>
> we can expect this error:
>
> ERROR: insert or update on table "ta" violates foreign key constraint
> "ta_tb_id"
> DETAIL: Key (tb_id)=(0) is not present in table "tb".
>
> and that is what i am getting but the unusual situation is the sequence
> "ta_id_seq" is incrementing every time i get the "violates foreign key
> constraint" error and i think this is not a good behavior, what do you
> think?
>
> Thanks in advance.
> //
> // Francisco J. Calderón S.
> //
>
From | Date | Subject | |
---|---|---|---|
Next Message | ktm@rice.edu | 2012-01-25 14:31:49 | Re: Problem with sequence increment |
Previous Message | Francisco Calderón | 2012-01-25 14:12:05 | Problem with sequence increment |