From derby-user-return-12753-apmail-db-derby-user-archive=db.apache.org@db.apache.org Wed May 19 07:12:21 2010 Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 94505 invoked from network); 19 May 2010 07:12:20 -0000 Received: from unknown (HELO mail.apache.org) (140.211.11.3) by 140.211.11.9 with SMTP; 19 May 2010 07:12:20 -0000 Received: (qmail 17935 invoked by uid 500); 19 May 2010 07:12:20 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 17563 invoked by uid 500); 19 May 2010 07:12:18 -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 17548 invoked by uid 99); 19 May 2010 07:12:17 -0000 Received: from nike.apache.org (HELO nike.apache.org) (192.87.106.230) by apache.org (qpsmtpd/0.29) with ESMTP; Wed, 19 May 2010 07:12:17 +0000 X-ASF-Spam-Status: No, hits=-2.3 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; Wed, 19 May 2010 07:12:07 +0000 Received: from fe-emea-09.sun.com (gmp-eb-lb-1-fe1.eu.sun.com [192.18.6.7] (may be forged)) by gmp-eb-inf-1.sun.com (8.13.7+Sun/8.12.9) with ESMTP id o4J7Bkh1009349 for ; Wed, 19 May 2010 07:11:46 GMT MIME-version: 1.0 Content-transfer-encoding: 7BIT Content-type: text/plain; CHARSET=US-ASCII Received: from conversion-daemon.fe-emea-09.sun.com by fe-emea-09.sun.com (Sun Java(tm) System Messaging Server 7u2-7.04 64bit (built Jul 2 2009)) id <0L2N00J00NSIU700@fe-emea-09.sun.com> for derby-user@db.apache.org; Wed, 19 May 2010 08:11:39 +0100 (BST) Received: from [129.159.112.134] ([unknown] [129.159.112.134]) by fe-emea-09.sun.com (Sun Java(tm) System Messaging Server 7u2-7.04 64bit (built Jul 2 2009)) with ESMTPSA id <0L2N00C0KNZERH30@fe-emea-09.sun.com> for derby-user@db.apache.org; Wed, 19 May 2010 08:11:39 +0100 (BST) Date: Wed, 19 May 2010 09:11:38 +0200 From: Knut Anders Hatlen Subject: Re: Drop "GENERATED BY DEFAULT" In-reply-to: Sender: Knut.Hatlen@Sun.COM To: derby-user@db.apache.org Message-id: <4BF38F2A.20109@sun.com> References: User-Agent: Mozilla/5.0 (X11; U; SunOS i86pc; en-US; rv:1.9.1.9) Gecko/20100318 Lightning/1.0b1 Thunderbird/3.0.4 X-Virus-Checked: Checked by ClamAV on apache.org On 05/19/10 02:32 AM, Brett Wooldridge wrote: > Hi All, > > I have a table something like this: > > CREATE TABLE test ( > id BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, > ... > PRIMARY KEY (id) > ); > > I wish to DROP the "GENERATED BY DEFAULT AS IDENTITY" from column 'id'. > Looking at the various ALTER TABLE forms, I cannot seem to find a way > to do it. > There are a lot of other tables with foreign key references to this > column, so > dropping and recreating the column doesn't seem viable. Is there a > supported way > to do it, or even an unsupported way by manipulating system tables? Hi Brett, I don't think it is documented anywhere, but setting the default value of a column clears its auto-increment property. So this should do the trick: ALTER TABLE test ALTER COLUMN id SET DEFAULT NULL -- Knut Anders