Hello to all. Faced with an unusual problem. I have a DB, the COUPON_ID column is defined using an identifier, and is incremented every time by 1. Example table:

CREATE TABLE Coupon ( COUPON_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), TITLE VARCHAR(30), START_DATE DATE, END_DATE DATE, AMOUNT INTEGER, TYPE VARCHAR(20), MESSAGE VARCHAR(300), PRICE DOUBLE, IMAGE VARCHAR(500), CONSTRAINT PRIME_COUPON_ID PRIMARY KEY(COUPON_ID) );

Let's say I entered new data, changed or deleted ... and everything works OK. And so the question arose when deleting. If I have a certain number of lines, let's say 1000 which I intend to delete. Everything is deleted normally and everything continues to work normally. But the question is, what should I do with these empty lines? If I want to insert something into them, then Derby refuses because I have marked the id of the column COUPON_ID as an identified column and it increases step by step. And I would like the empty spaces of the lines not to accumulate. Is there a solution to this problem?

    2 answers 2

    Faced with an unusual problem

    In fact, such questions are often asked. I did not find analogues on ruSO, so I answer.

    What makes you think that this is a problem? Well, there are gaps and what?

    You did not specify a DBMS, judging by the syntax, this is Oracle .

    In Oracle maximum value is BIGINT = 9223372036854775807. Suppose that 1,000,000 lines per second are inserted into your system. Calculate in how many days the BIGINT data type will overflow.

    9223372036854775807/60/60/24 ~ 213,503,982 days

    which is approximately 584,942 and a half years.

    Neither you, nor your great-grandchildren, nor the hardware on your servers will live to see the overflow event of the id field.

    There are ways to insert data in the blanks by disabling auto-increment (at least in MS SQL). But in your case it is useless.

      Need to use AUTO_INCREMENT

      the AUTO_INCREMENT value will be reused if a group with the highest AUTO_INCREMENT value is deleted in any group.

      The last value of the AUTO_INCREMENT field that was created automatically can be obtained using the SQL LAST_INSERT_ID() function or the mysql_insert_id() API mysql_insert_id() .

      Taken from this article

      • and what have mysql? - pegoopik
      • @pegoopik the author did not specify which base, however, as you said. but doesn’t really change much) - Senior Pomidor