column identity value

[Pedro Dinis <di...@mentesvirtuais.com>]

Hi,
 I have a table with  one of its  column defined like this:

CREATE TABLE WORK(
SM_ID  INT  GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1),
(...)
);

To insert rows in this table I have this Prepared Statment:
INSERT INTO WORK VALUES (default,?,?,?)

This way I know that in each row insertion Derby will increased SM_ID 
value by one....but, suppose that in the last row insertion SM_ID got 
the value *2147483647* (/java.lang.Integer.MAX_VALUE).
What will  happen in the next insertion???SM_ID is an INT,  I know that 
it can't have a value of //(java.lang.Integer.MAX_VALUE)+1.

1) Do I get an SQL error?
2) SM_ID returns to its starting value  as a cycle?
3) anything else?

Can I use ALTER TABLE ....RESTART WITH....to control the next value to 
be assigned to my identity column ....In Derby docs RESTAR WITH can be 
used when a identity column is defined with GENERATED BY DEFAULT
http://db.apache.org/derby/docs/10.2/ref/
(alter table statement)

/Thanks,
APDinis
//

Re: column identity value

[ms...@dbrack01.segel.com]

Ah yes, the wrap around on an identity column issue.

The short answer is that you' ll hit other limitations first. You could catch the exception, but when you wrap around this number you'll have issues with the insert.

Sent via BlackBerry.

-Mike Segel
Principal
MSCC
312 952 8175


-----Original Message-----
From: Pedro Dinis <di...@mentesvirtuais.com>

Date: Wed, 13 Jun 2007 16:52:30 
To:derby-user@db.apache.org
Subject: column identity value


Hi,
  I have a table with  one of its  column defined like this:
 
 CREATE TABLE WORK(
 SM_ID  INT  GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1),
 (...) 
 );
 
 To insert rows in this table I have this Prepared Statment:
 INSERT INTO WORK VALUES (default,?,?,?)
 
 This way I know that in each row insertion Derby will increased SM_ID value by one....but, suppose that in the last row insertion SM_ID got the value 2147483647 (java.lang.Integer.MAX_VALUE).
 What will  happen in the next insertion???SM_ID is an INT,  I know that it can't have a value of (java.lang.Integer.MAX_VALUE)+1.
 
 1) Do I get an SQL error?
 2) SM_ID returns to its starting value  as a cycle?
 3) anything else?
 
 Can I use ALTER TABLE ....RESTART WITH....to control the next value to be assigned to my identity column ....In Derby docs RESTAR WITH can be used when a identity column is defined with GENERATED BY DEFAULT
 http://db.apache.org/derby/docs/10.2/ref/ <http://db.apache.org/derby/docs/10.2/ref/> 
 (alter table statement)
 
 Thanks,
 APDinis