The fact that SYSTABCOL.max_identity is a BIGINT seems to imply negative autoincrement values are possible, but they are not... all that the BIGINT data type does is needlessly cut the autoincrement range in half. Here's a demonstration...

(note that sa_reset_identity accepts negative values but get_identity does not return them)

-----------------------------------------------------------------------------------------
-- SYSTABCOL.max_identity is BIGINT so it can be set to a negative number.

CREATE TABLE t ( pkey BIGINT NOT NULL DEFAULT AUTOINCREMENT );

CALL sa_reset_identity ( 't', new_identity = -5 );

CHECKPOINT;
SELECT max_identity 
  FROM SYSTABCOL INNER JOIN SYSTAB ON SYSTABCOL.table_id = SYSTAB.table_id 
 WHERE SYSTAB.table_name = 't';

max_identity
-5

-----------------------------------------------------------------------------------------
-- A negative SYSTABCOL.max_identity raises an exception and is automatically set to zero.

INSERT t VALUES ( DEFAULT );

Column 'pkey' in table 't' cannot be NULL
SQLCODE=-195, ODBC 3 State="23000"

CHECKPOINT;
SELECT max_identity 
  FROM SYSTABCOL INNER JOIN SYSTAB ON SYSTABCOL.table_id = SYSTAB.table_id 
 WHERE SYSTAB.table_name = 't';

max_identity
0

-----------------------------------------------------------------------------------------
-- Positive values in SYSTABCOL.max_identity work OK.

INSERT t VALUES ( DEFAULT )
COMMIT;
SELECT * FROM t;

pkey
1

-----------------------------------------------------------------------------------------
-- GET_IDENTITY returns UNSIGNED BIGINT.

SELECT EXPRTYPE ( 'SELECT GET_IDENTITY ( ''t'' )', 1 );

EXPRTYPE('SELECT GET_IDENTITY ( ''t'' )',1)
unsigned bigint

asked 20 Mar '15, 15:54

Breck%20Carter's gravatar image

Breck Carter
32.5k5407241050
accept rate: 20%

edited 20 Mar '15, 20:54

Further evidence: You can certainly add negative values in a AUTOINCREMENT column when specifying them explicitly but that will still leave the default to be restricted to non-negative values:

CREATE TABLE t ( pkey BIGINT NOT NULL DEFAULT AUTOINCREMENT );

INSERT t VALUES (-9223372036854775808); -- -2 ^^ 63
CHECKPOINT;
SELECT max_identity 
  FROM SYSTABCOL INNER JOIN SYSTAB ON SYSTABCOL.table_id = SYSTAB.table_id 
 WHERE SYSTAB.table_name = 't';
-- returns 0
INSERT t VALUES (DEFAULT);
SELECT * from t; -- lists rows with -2 ^^ 31 and with 1
CHECKPOINT;
SELECT max_identity 
  FROM SYSTABCOL INNER JOIN SYSTAB ON SYSTABCOL.table_id = SYSTAB.table_id 
 WHERE SYSTAB.table_name = 't';
-- returns 1

Even more, GLOBAL AUTOINCREMENT values and the according GLOBAL_DATABASE_ID property are restricted to non-negative values, too.


That being said, I have never had an issue with too less BIGINT values so that may be more a theoretical problem, right?

(23 Mar '15, 04:35) Volker Barth
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×13

question asked: 20 Mar '15, 15:54

question was seen: 1,664 times

last updated: 23 Mar '15, 04:52