Re: Problem with sequence increment

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.
//