Mailing-List: contact ddlutils-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: ddlutils-user@db.apache.org
Received-SPF: pass (herse.apache.org: domain of tomdzk@gmail.com designates
 66.249.92.172 as permitted sender)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws;
        s=beta; d=gmail.com;
        h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
        b=j137kX7cXOPCWG5vY3+AfOOCmZqo8a25PmkXsXW6lC8tf09CXjX+j7Zjxwq6qf8L8R5EQCN6Bhqtyd0MYmf788AXQCgZOfX69z/tazMMwt91H+8MMa0hw2x6zcwAtksudiEIpxdby25Kd8hVMagPGcRyk5ufQcEZd56FhDVMX1g=
Message-ID: <224f32340611241630j6a86854at5323b27b3d5e29c8@mail.gmail.com>
Date: Fri, 24 Nov 2006 16:30:18 -0800
From: "Thomas Dudziak" <tomdzk@gmail.com>
To: ddlutils-user@db.apache.org
Subject: Re: Identity start values (from Postgres to Derby)
In-Reply-To: <20061124180007.26223.qmail@web34415.mail.mud.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <20061124180007.26223.qmail@web34415.mail.mud.yahoo.com>

On 11/24/06, Laurent ROCHE <laurent_roche@yahoo.com> wrote:

> I could not find anything about this issue on the website nor JIRA. Can the mailing list archives be searched ?
>
> I am doing an export from a PostgreSQL database to a Derby database.
> Some of my tables have one SERIAL colum (in Postgres) which is correctly translated into Identity (in Derby) ... however the Identity value generated will be always 1 as the tables are created like this:
> CREATE TABLE my_table_auto(auto_id  SMALLINT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1), ...
>
> I would expect DDL-Utils to generate something along the lines of:
> ALTER TABLE my_table_auto ALTER COLUMN auto_id  RESTART WITH max_val_in_the_table
>
> Where of course max_val_in_the_table is the maximum plus 1 of the id of the table
> (i.e. SELECT MAX(auto_id) + 1 FROM my_table_auto)

Sorry, I don't quite understand the issue here? AFAICS There are three
different cases:

(1)
The table in Derby is new (i.e. CREATE TABLE). In this case, starting
at 1 is perfectly fine and valid.

(2)
The table already exists in Derby and it has the same auto-increment
settings. In this case, it makes no sense to reset the auto-increment
counter because it should already be at the correct value (and if not,
e.g. because of manual insertions, then you should perhaps not use
auto-increment anyways).

(3)
The table already exists in Derby but it does not have auto-increment
specified. If the table already has data in it, you should perhaps not
use auto-increment (it depends on how the data was put into the table;
e.g. if a different program also uses the table, then you might break
it with making the column autoincrement).
If it does not have data in it, then starting at 1 would be fine.

cheers,
Tom