From derby-user-return-10649-apmail-db-derby-user-archive=db.apache.org@db.apache.org Thu Mar 05 21:32:05 2009 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 44743 invoked from network); 5 Mar 2009 21:32:05 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 5 Mar 2009 21:32:05 -0000 Received: (qmail 71979 invoked by uid 500); 5 Mar 2009 21:32:03 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 71951 invoked by uid 500); 5 Mar 2009 21:32:03 -0000 Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm Precedence: bulk list-help: list-unsubscribe: List-Post: List-Id: Reply-To: "Derby Discussion" Delivered-To: mailing list derby-user@db.apache.org Received: (qmail 71942 invoked by uid 99); 5 Mar 2009 21:32:03 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 05 Mar 2009 13:32:03 -0800 X-ASF-Spam-Status: No, hits=-4.0 required=10.0 tests=RCVD_IN_DNSWL_MED,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (nike.apache.org: local policy) Received: from [192.18.6.21] (HELO gmp-eb-inf-1.sun.com) (192.18.6.21) by apache.org (qpsmtpd/0.29) with ESMTP; Thu, 05 Mar 2009 21:31:53 +0000 Received: from fe-emea-10.sun.com (gmp-eb-lb-2-fe2.eu.sun.com [192.18.6.11]) by gmp-eb-inf-1.sun.com (8.13.7+Sun/8.12.9) with ESMTP id n25LVLPA016551 for ; Thu, 5 Mar 2009 21:31:33 GMT MIME-version: 1.0 Content-transfer-encoding: 7BIT Content-type: text/plain; format=flowed; charset=ISO-8859-1 Received: from conversion-daemon.fe-emea-10.sun.com by fe-emea-10.sun.com (Sun Java(tm) System Messaging Server 7.0-3.01 64bit (built Dec 23 2008)) id <0KG100J00YDXC300@fe-emea-10.sun.com> for derby-user@db.apache.org; Thu, 05 Mar 2009 21:31:21 +0000 (GMT) Received: from [192.168.5.4] ([unknown] [84.208.186.89]) by fe-emea-10.sun.com (Sun Java(tm) System Messaging Server 7.0-3.01 64bit (built Dec 23 2008)) with ESMTPSA id <0KG1004IKYFVAW50@fe-emea-10.sun.com> for derby-user@db.apache.org; Thu, 05 Mar 2009 21:31:12 +0000 (GMT) Date: Thu, 05 Mar 2009 22:31:06 +0100 From: Kristian Waagan Subject: Re: generated always as identity In-reply-to: Sender: Kristian.Waagan@Sun.COM To: Derby Discussion Message-id: <49B0449A.6070104@Sun.COM> References: <49B00176.3000400@gmx.at> User-Agent: Thunderbird 2.0.0.17 (X11/20081023) X-Virus-Checked: Checked by ClamAV on apache.org George H wrote: > Have you tried setting the start and increment values of the identity > statement like > > GENERATED ALWAYS AS IDENTITY(START WITH x, INCREMENT BY y ) > > where x is the last auto_increment number from mysql and y is well the > step number to increment by. > > -- > George H > george.dma@gmail.com > > > > On Thu, Mar 5, 2009 at 6:44 PM, wrote: >> I need to replicate data from mysql to derby. I have the init script for the >> empty derby db. Some tables have columns, which provide generated keys. >> These tables are created like this: >> create table foo (foo_id bigint not null generated always as identity >> primary key, ... ); >> >> If I try to replicate my data from the other database to derby, it won't >> work because derby wants to generate the key for foo_id. So my idea was to >> create all tables with these columns without the "generated always as >> identity" statement. Then to import all data. And after then to alter the >> column again. >> >> The import worked, but what I couldn't do was to add the statement >> "generated always as identity" to the column foo_id. >> Does anybody know how can I do that? Hello, As far as I know, you cannot alter the column in such a way. Can you use GENERATED BY DEFAULT AS IDENTITY instead? If so, you have to adjust the identity value after the initial data has been imported, as George described in his answer, to make sure you don't get any duplicate values. It may also be wise to make sure your insertion code properly handles duplicate primary key exceptions. HTH, -- Kristian >> >>