I have a table and I am trying to insert values in it. I want to prevent gaps in the primary key column. For primary column I have tried using Identity / sequence objects. I know that this is potential drawback of using Identity or sequence object; but is there any work around? I am open to using some other technique to prevent gaps.
Here is the table structure and sample code:
CREATE SEQUENCE Service_Ticket_Seq
AS INTEGER
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100
CYCLE;
CREATE TABLE Meats
(ticket_seq INTEGER NOT NULL primary key default (NEXT VALUE FOR Service_Ticket_Seq) ,
meat_type VARCHAR(15) NOT NULL)
ROW_NUMBER() over (ORDER BY ID)
to sequence its results without gaps. – Pieter Geerkens Sep 28 '14 at 14:57