Please be aware that the SAP SQL Anywhere Forum will be shut down on August 29th, 2024 when all it's content will be migrated to the SAP Community.

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.5k5417271050
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:

×14

question asked: 20 Mar '15, 15:54

question was seen: 1,797 times

last updated: 23 Mar '15, 04:52