db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From mse...@dbrack01.segel.com
Subject Re: column identity value
Date Wed, 13 Jun 2007 16:08:26 GMT
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 <dinis@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
 
 
 
Mime
View raw message