Return-Path: Delivered-To: apmail-db-derby-user-archive@www.apache.org Received: (qmail 55267 invoked from network); 30 May 2006 15:23:48 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (209.237.227.199) by minotaur.apache.org with SMTP; 30 May 2006 15:23:48 -0000 Received: (qmail 91374 invoked by uid 500); 30 May 2006 15:23:47 -0000 Delivered-To: apmail-db-derby-user-archive@db.apache.org Received: (qmail 90994 invoked by uid 500); 30 May 2006 15:23:46 -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 90983 invoked by uid 99); 30 May 2006 15:23:46 -0000 Received: from asf.osuosl.org (HELO asf.osuosl.org) (140.211.166.49) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 30 May 2006 08:23:46 -0700 X-ASF-Spam-Status: No, hits=0.0 required=10.0 tests=UNPARSEABLE_RELAY X-Spam-Check-By: apache.org Received-SPF: pass (asf.osuosl.org: local policy) Received: from [192.18.42.249] (HELO nwkea-pix-1.sun.com) (192.18.42.249) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 30 May 2006 08:23:44 -0700 Received: from d1-sfbay-10.sun.com ([192.18.39.120]) by nwkea-pix-1.sun.com (8.12.10+Sun/8.12.9) with ESMTP id k4UFNN73022256 for ; Tue, 30 May 2006 08:23:24 -0700 (PDT) Received: from conversion-daemon.d1-sfbay-10.sun.com by d1-sfbay-10.sun.com (Sun Java System Messaging Server 6.2-4.02 (built Sep 9 2005)) id <0J0300A0141G3O00@d1-sfbay-10.sun.com> (original mail from Craig.Russell@Sun.COM) for derby-user@db.apache.org; Tue, 30 May 2006 08:23:23 -0700 (PDT) Received: from [129.145.133.119] by d1-sfbay-10.sun.com (Sun Java System Messaging Server 6.2-4.02 (built Sep 9 2005)) with ESMTPSA id <0J0300FYR42ZL200@d1-sfbay-10.sun.com> for derby-user@db.apache.org; Tue, 30 May 2006 08:23:23 -0700 (PDT) Date: Tue, 30 May 2006 08:23:21 -0700 From: Craig L Russell Subject: Re: "generated by default" question In-reply-to: <20060530144043.52834243A4@dbrack01.segel.com> Sender: Craig.Russell@Sun.COM To: Derby Discussion Message-id: MIME-version: 1.0 X-Mailer: Apple Mail (2.750) Content-type: multipart/signed; protocol="application/pkcs7-signature"; boundary=Apple-Mail-45-1044194096; micalg=sha1 References: <20060530144043.52834243A4@dbrack01.segel.com> X-Virus-Checked: Checked by ClamAV on apache.org X-Spam-Rating: minotaur.apache.org 1.6.2 0/1000/N --Apple-Mail-45-1044194096 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Hi G, Do you have a good solution that you can write up in detail and post to JIRA? Then someone with an itch can fix it. Do-ocracy in action. Craig On May 30, 2006, at 7:38 AM, derby@segel.com wrote: > > Hmmm, > > Yeah, that's what I'm afraid of. > > Essentially what they are asking is that if you try to insert a row > and it > fails, you have two options. > > 1) Increment your count and try again (Restart) until you succeed, > or, find > the max value, and reset to max value + 1. > > The trouble in using #2, is that lets say you have rows 1,2,3,4,5 > entered, > and someone manually enters 10, 1001, 1002, 1004. So when you > search for the > max value, you'll end up 1004, and restart with 1005. > > This is something that should be done behind the scenes. > > SO its not a "bug" but a design defect. > > -G > >> -----Original Message----- >> From: news [mailto:news@sea.gmane.org] On Behalf Of hilz >> Sent: Monday, May 29, 2006 4:19 PM >> To: derby-user@db.apache.org >> Subject: Re: "generated by default" question >> >> I wish it was a bug! >> it seems this is by design, but i hope there is some other way to >> overcome this behavior. >> >> here is a quote from the docs at >> http://db.apache.org/derby/docs/dev/ref/ref-single.html >> that show it is by design: >> >> >> RESTART WITH integer-constant specifies the next value to be >> generated >> for the identity column. RESTART WITH is useful for a table that >> has an >> identity column that was defined as GENERATED BY DEFAULT and that >> has a >> unique key defined on that identity column. Because GENERATED BY >> DEFAULT >> allows both manual inserts and system generated values, it is >> possible >> that manually inserted values can conflict with system generated >> values. >> To work around such conflicts, use the RESTART WITH syntax to specify >> the next value that will be generated for the identity column. >> Consider >> the following example, which involves a combination of automatically >> generated data and manually inserted data: >> >> CREATE TABLE tauto(i INT GENERATED BY DEFAULT AS IDENTITY, k INT) >> CREATE >> UNIQUE INDEX tautoInd ON tauto(i) INSERT INTO tauto(k) values 1,2 >> >> The system will automatically generate values for the identity >> column. >> But now you need to manually insert some data into the identity >> column: >> >> INSERT INTO tauto VALUES (3,3) INSERT INTO tauto VALUES (4,4) INSERT >> INTO tauto VALUES (5,5) >> >> The identity column has used values 1 through 5 at this point. If >> you >> now want the system to generate a value, the system will generate >> a 3, >> which will result in a unique key exception because the value 3 has >> already been manually inserted. To compensate for the manual >> inserts, >> issue an ALTER TABLE statement for the identity column with >> RESTART WITH >> 6: >> >> ALTER TABLE tauto ALTER COLUMN i RESTART WITH 6 >> >> ALTER TABLE does not affect any view that references the table being >> altered. This includes views that have an "*" in their SELECT >> list. You >> must drop and re-create those views if you wish them to return the >> new >> columns. >> >> >> >> >> >> >> Michael Segel wrote: >>> On Monday 29 May 2006 3:31 pm, hilz wrote: >>> After a quick glance, >>> >>> This looks like a bug. >>> >>> You should be able to insert your own values in the ID column, >>> which you >> do... >>> then on rows that are auto generated, they should chose an >>> incremental >> value. >>> >>> Using your example, it should have trapped the error and then >>> tried to >> insert >>> using 2... until it found an integer value that was not in use. >>> >>> But hey, what do I know. >>> I'm sure someone is going to tell me that this functionality is >>> behaving >> per >>> spec.... >>> >>> -G >>> >>>> Hi all. >>>> If i have a table A defined as follows: >>>> >>>> create table A >>>> ( >>>> ID INTEGER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, >>>> NAME VARCHAR(255) >>>> ); >>>> >>>> then i do the following: >>>> >>>> insert into A (ID, NAME) values (1,'hello 1'); >>>> >>>> and then i do the following: >>>> >>>> insert into A (NAME) values ('hello 2'); >>>> >>>> I will get this error: >>>> >>>> The statement was aborted because it would have caused a >>>> duplicate key >>>> value in a unique or primary key constraint or unique index >>>> identified >>>> by 'SQL060529010004440' defined on 'A'. >>>> >>>> To avoid this, I will have to do : >>>> >>>> alter table....RESTART WITH.... >>>> >>>> Is there another way to make the autoincrement smart enough to know >> that >>>> the value already exists and just generate a new value for me? >>>> I find it odd to have to set the "restart with" to skip the >>>> values that >>>> i set manually. >>>> >>>> thanks for any help. >>> > > > Craig Russell Architect, Sun Java Enterprise System http://java.sun.com/products/jdo 408 276-5638 mailto:Craig.Russell@sun.com P.S. A good JDO? O, Gasp! --Apple-Mail-45-1044194096 Content-Transfer-Encoding: base64 Content-Type: application/pkcs7-signature; name=smime.p7s Content-Disposition: attachment; filename=smime.p7s MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIGUDCCAwkw ggJyoAMCAQICEB7Y7fLHmKoeHTUdbtYrvpAwDQYJKoZIhvcNAQEEBQAwYjELMAkGA1UEBhMCWkEx JTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQ ZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBMB4XDTA1MTIxNTIxNDc0NFoXDTA2MTIxNTIxNDc0 NFowbDEQMA4GA1UEBBMHUnVzc2VsbDEUMBIGA1UEKhMLQ3JhaWcgTGFpcmQxHDAaBgNVBAMTE0Ny YWlnIExhaXJkIFJ1c3NlbGwxJDAiBgkqhkiG9w0BCQEWFUNyYWlnLlJ1c3NlbGxAU3VuLkNPTTCC ASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAMXXgEAm88nu8cFbxXSWqTq+GaYpCx/0QSom 8kBbVxpJIURuO4ErLDupeXu6y9+5e7ZKLbeSQE5xbfYPcQR/IhqmYKy5YqRcuzPXREBj2zKEkZph pNXWpHgMdx9W8dq0Cu2i9Ux/S6c2MuEHrP6gfGGll+b/mzLoO280QHTuE4pcpKntRnwZdGxQ/5l8 IL+eLP+jpJAbYW9C+KNKofZtS6V6R0uzlqTOsEdZvwxZQ4mmPgHoz1+Gjwme/PC5sKvF09MaJDiI pj9SvZ4CTCgcDZV78J086YwlVbMC0VQotjhu1p42lr8CS33IXLz3OWNrDETCAepah/Dgw2ZZApQ9 9L0CAwEAAaMyMDAwIAYDVR0RBBkwF4EVQ3JhaWcuUnVzc2VsbEBTdW4uQ09NMAwGA1UdEwEB/wQC MAAwDQYJKoZIhvcNAQEEBQADgYEAKdIkgAWCg2Bi7ocnstfJA4iymTRI2/L4oQx9zvllM9bNJ2cR cecJIx3HuoHbhPvemh1GExEPgHU+dXSxDmD0BEmPnhSReKCURyslnbMphPZ5kR6USzQFrRa+v0ii J+SBO9VQYTQWT+xEjmRLM76MfkBFw3IOC9CUkRoYZ88pOoUwggM/MIICqKADAgECAgENMA0GCSqG SIb3DQEBBQUAMIHRMQswCQYDVQQGEwJaQTEVMBMGA1UECBMMV2VzdGVybiBDYXBlMRIwEAYDVQQH EwlDYXBlIFRvd24xGjAYBgNVBAoTEVRoYXd0ZSBDb25zdWx0aW5nMSgwJgYDVQQLEx9DZXJ0aWZp Y2F0aW9uIFNlcnZpY2VzIERpdmlzaW9uMSQwIgYDVQQDExtUaGF3dGUgUGVyc29uYWwgRnJlZW1h aWwgQ0ExKzApBgkqhkiG9w0BCQEWHHBlcnNvbmFsLWZyZWVtYWlsQHRoYXd0ZS5jb20wHhcNMDMw NzE3MDAwMDAwWhcNMTMwNzE2MjM1OTU5WjBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhhd3Rl IENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVtYWls IElzc3VpbmcgQ0EwgZ8wDQYJKoZIhvcNAQEBBQADgY0AMIGJAoGBAMSmPFVzVftOucqZWh5owHUE cJ3f6f+jHuy9zfVb8hp2vX8MOmHyv1HOAdTlUAow1wJjWiyJFXCO3cnwK4Vaqj9xVsuvPAsH5/Ef kTYkKhPPK9Xzgnc9A74r/rsYPge/QIACZNenprufZdHFKlSFD0gEf6e20TxhBEAeZBlyYLf7AgMB AAGjgZQwgZEwEgYDVR0TAQH/BAgwBgEB/wIBADBDBgNVHR8EPDA6MDigNqA0hjJodHRwOi8vY3Js LnRoYXd0ZS5jb20vVGhhd3RlUGVyc29uYWxGcmVlbWFpbENBLmNybDALBgNVHQ8EBAMCAQYwKQYD VR0RBCIwIKQeMBwxGjAYBgNVBAMTEVByaXZhdGVMYWJlbDItMTM4MA0GCSqGSIb3DQEBBQUAA4GB AEiM0VCD6gsuzA2jZqxnD3+vrL7CF6FDlpSdf0whuPg2H6otnzYvwPQcUCCTcDz9reFhYsPZOhl+ hLGZGwDFGguCdJ4lUJRix9sncVcljd2pnDmOjCBPZV+V2vf3h9bGCE6u9uo05RAaWzVNd+NWIXiC 3CEZNd4ksdMdRv9dX2VPMYIDEDCCAwwCAQEwdjBiMQswCQYDVQQGEwJaQTElMCMGA1UEChMcVGhh d3RlIENvbnN1bHRpbmcgKFB0eSkgTHRkLjEsMCoGA1UEAxMjVGhhd3RlIFBlcnNvbmFsIEZyZWVt YWlsIElzc3VpbmcgQ0ECEB7Y7fLHmKoeHTUdbtYrvpAwCQYFKw4DAhoFAKCCAW8wGAYJKoZIhvcN AQkDMQsGCSqGSIb3DQEHATAcBgkqhkiG9w0BCQUxDxcNMDYwNTMwMTUyMzIyWjAjBgkqhkiG9w0B CQQxFgQUbGMm/dmOh/KbUTgQ1LXodTihTnMwgYUGCSsGAQQBgjcQBDF4MHYwYjELMAkGA1UEBhMC WkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0 ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAhAe2O3yx5iqHh01HW7WK76QMIGHBgsqhkiG 9w0BCRACCzF4oHYwYjELMAkGA1UEBhMCWkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQ dHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAhAe 2O3yx5iqHh01HW7WK76QMA0GCSqGSIb3DQEBAQUABIIBABraeOkTPB1a6XTpn+FEB2ZcImuU39Ld U9Sm7J5R05X24YRM3hwbENxUD4R07jkdxRlOR8s/yGoiaN9Xv1kkj/u4P0EJXPuJc7msNvFHeUo3 BPuzyfi09tzV9m8OyF514TO7DHlOuk/fqFWfXH1yFkvVVzbuRNkLCBXU2XUEZpIzyFekKDw1bgM7 2kAHWOJvot1GdyuPGx3NPhs6850Ylz/o4b4j6HAGnmYgJR1zPTHDlwLm/ARgWKL/Zy+XjSdHr9nq w7rk733M6BR4pXXFDUdr/3FKgFJA3sc7Yvxf6OmsmvmCrtw3N+2URhqcdPeDEDVi7/F+kD8j2J+/ QRMoMDoAAAAAAAA= --Apple-Mail-45-1044194096--