Hello All,
I am building a database and I am in troubles to get a field
autonumbering itself.
I have:
% -- Begin -----------------------------------------------
CREATE SEQUENCE patr_seq;
CREATE TABLE patr_local (
local_cod INTEGER default nextval('patr_seq')
local_descr CHAR(30) not null
);
% -- End --------------------------------------------------
And:
% -- Begin ------------------------------------------------
CREATE TABLE patr_local (
local_cod SERIAL,
local_descr CHAR(30) UNIQUE not null
);
% -- End --------------------------------------------------
In both cases, it runs properly, the field local_cod is automatically
written; in both case happens:
=> insert into patr_local (local_descr) values ('local 1');
INSERT
=> insert into patr_local (local_descr) values ('local 2');
INSERT
=> select * from patr_local;
1 - local 1
2 - local 2
BUT, if I try to insert a wrong tupple (duplicating the value of the field
local_descr, that is unique), the counter is also added +1, so the table
become:
=> insert into patr_local (local_descr) values ('local 1');
INSERT
=> insert into patr_local (local_descr) values ('local 1');
ERROR: duplicated key local_descr
=> insert into patr_local (local_descr) values ('local 2');
INSERT
=> select * from patr_local;
1 - local 1
3 - local 2
As you can see, the #2 wasn't used. So, as many errors I get while
inserting tupples in the table will be number of 'numbers' not used.
How can I create a field that avoid this action? I would like the field
local_cod had the sequence:
1,2,3,4,5,6,7,8,9,10,...,N,...
...whatever happens while inserting tupples.
Thanks in advance,
Best regards,
Marcelo Pereira
-- Remember that only God and Esc+:w saves.
__
(_.\ Marcelo Pereira |
/ / ___ |
/ (_/ _ \__ Matematica/99 - IMECC |
_______\____/_\___)___Unicamp_______________/