Postgres Serial primary key

[thiagofernando]

I create 2 tables

CREATE TABLE continentes (
id serial NOT NULL,
cenario_id integer REFERENCES cenarios ON DELETE RESTRICT NOT NULL ,
nome text NOT NULL,
quantidade_territorios smallint NOT NULL,
exercitos_posse smallint NOT NULL,
criacao timestamp DEFAULT now(),
CONSTRAINT continentes_id PRIMARY KEY (id)
);

CREATE TABLE cenarios (
id serial NOT NULL ,
nome text NOT NULL,
criacao timestamp DEFAULT now(),
CONSTRAINT cenarios_id PRIMARY KEY (id)
);

when i try insert a data in continentes with a nonexistent foreing key, the right message is showed, but the primary key is incremented anyway. Example:

cenarios:
id nome criacao
1 a 2016-02-06 19:16:44
2 b 2016-02-06 19:17:06

continentes
id |cenario_id |nome |quantidade_territorios |exercitos_posse | criacao |
1 | 2 | j | 1 | 1 |2016-02-07 10:46:45 |

try insert in continentes
cenario_id = 4
nome = z
quantidade_territorios = 1
exercitos_posse = 1

error on foreing key --> OK!

try insert in continentes
cenario_id = 1
nome = z
quantidade_territorios = 1
exercitos_posse = 1

create a register with id = 3 (jumping id = 2)

[serge-rider]

This is the default PostgreSQL behavior. It increases sequence value on every attempt of insert. And I can't change this behavior from DBeaver.
You could try to work in transactional mode (just disable auto-commit mode in main toolbar) and rollback after error.

[thiagofernando]

thanks Serge