-3

Disregarding the unrealistic nature of generating 18446744073709551615 rows on a given table in a database. Let's say it happens. Would you be able to maintain data integrity, if you had to create a second table to continue to store the same data you want to reference.

Benjamin Garcia
  • 1
  • 2
    What's the specific database? – The Impaler May 17 '20 at 00:51
  • 3
    A SQL table with 1^19 rows *is* unrealistic in many other ways, so this is hard to disregard. – GMB May 17 '20 at 00:53
  • Consider using a UUID instead. – Tim Biegeleisen May 17 '20 at 01:00
  • 1
    This is a faq. (And can be expected to be.) Before considering posting please read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. – philipxy May 17 '20 at 05:48
  • The question is not that unreasonable. Identity columns on parallel systems often have gaps -- and a gap of 1,000,000 is not unreasonable for future systems. Not all ids are stored in a table, but are generally associated with operations of some sort (such as failed transactions). I can imagine systems where even bigints would not be big enough. However, it is a bad question for StackOverflow because what happens obviously depends on the database and the configuration of the system. – Gordon Linoff May 17 '20 at 12:24

2 Answers2

1

Sometimes there just limitations which must be dealt with. Sometimes you just need to document that limitation and move on. So create your sequence and document the limitation. So

create sequence long_range_sequence
       minvalue   -9223372036854775808
       no maxvalue
       start with -9223372036854775808
       increment  1;
comment on sequence long_range_sequence is  
        'Warning: Excessive Usage. Created on May 2020. If values are constantly used at the rate of 10M/sec ranges values will run out sometime July 60473.'

And let maintenance deal with it later. Of course by then it might be a moot issue.

Belayer
  • 13,578
  • 2
  • 11
  • 22
0

You don't mention which database you are using so I'll give you some options:

  1. Some databases can provide you UUIDs that can overcome this limitation since they provide 128-bit values.

  2. If you are using PostgreSQL database, then you can have multiple 64-bit IDENTITY (auto-generated) columns. If you use them cleverly you can have more than 2^64 values.

  3. If you are using Oracle you can generate values up to 10^27 using sequences.

The Impaler
  • 45,731
  • 9
  • 39
  • 76