Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (athena.apache.org: local policy)
Date: Wed, 13 Aug 2008 15:38:13 -0700
From: Craig L Russell <Craig.Russell@Sun.COM>
Subject: Re: Problem with UNIQUE constraint
In-reply-to: <e012616b0808131430h744d1acfmeea223c1744b0f8e@mail.gmail.com>
Sender: Craig.Russell@Sun.COM
To: Derby Discussion <derby-user@db.apache.org>
Message-id: <15516722-1EBE-4E58-BF31-5B483F3658DB@SUN.com>
MIME-version: 1.0
Content-type: multipart/signed; protocol="application/pkcs7-signature";
 micalg=sha1; boundary=Apple-Mail-17--158273473
References: <e012616b0808131307x16cac8f7v17073c761a5a45af@mail.gmail.com>
 <27AC242A-F71A-42F9-BD42-11C80310EEDC@SUN.com>
 <e012616b0808131430h744d1acfmeea223c1744b0f8e@mail.gmail.com>


--Apple-Mail-17--158273473
Content-Type: text/plain;
	charset=US-ASCII;
	format=flowed;
	delsp=yes
Content-Transfer-Encoding: 7bit

The two Timestamp columns have resolution of nanoseconds, which you  
might not see depending on how you format the columns.

Can you show two rows that have "duplicate" values?

Thanks,

Craig

On Aug 13, 2008, at 2:30 PM, Dmitri Pissarenko wrote:

> Hello!
>
> Thanks for your answer!
>
>> It would help if you would show your DDL to define the table
>
> Create table MyTable
> (
> 	MyTableId BIGINT GENERATED ALWAYS AS IDENTITY,
> 	field01 Varchar(40) NOT NULL,
> 	field02 Timestamp NOT NULL,
> 	field03 Timestamp NOT NULL,
> 	field04 Varchar(40) NOT NULL,
> 	field05 Varchar(40) NOT NULL,
> 	field06 Time NOT NULL,
> 	field07 Time NOT NULL,
> 	field08 Varchar(40) NOT NULL,
> 	field09 Varchar(40) NOT NULL,
> 	field10 Varchar(40) NOT NULL,
> 	field11 Varchar(4) NOT NULL,
> 	field12 Varchar(3) NOT NULL,
> 	field13 Varchar(4) NOT NULL,
> 	field14 Integer NOT NULL,
> 	field15 Varchar(3) NOT NULL,
> 	field16 Varchar(40) NOT NULL,
> 	field17 Varchar(40) NOT NULL,
> 	field18 Varchar(40) NOT NULL,
> 	field19 Integer NOT NULL,
> 	field20 Integer NOT NULL,
> 	field21 Integer NOT NULL,
> 	field22 Integer NOT NULL,
> 	field23 Integer,
> 	field24 Integer,
> 	field25 Integer,
> 	field26 Integer,
> 	field27 Integer,
> 	field28 Integer,
> 	field29 Integer,
> 	field30 Integer DEFAULT -1,
>    CONSTRAINT MyTablePk PRIMARY KEY(MyTableId),
> 	CONSTRAINT MyTableUnique UNIQUE (field02, field01, field03, field04,
> field05, field06, field07, field08, field09,
> 		field10, field11, field12, field13, field14, field15, field16,
> field17, field18,
> 		field19, field20, field21, field22)
> );
>
> CREATE INDEX MyTable_field02
> ON MyTable(field02);
>
> CREATE INDEX MyTable_field30
> ON MyTable(field30);
>
>> and a short
>> program segment that would demonstrate the problem.
>
> PreparedStatement statement = null;
>
> try
> {
> statement = aConnection.prepareStatement(
>       MyTable.INSERT_STATEMENT);
>
>  statement.setString(1, "someText");
>  statement.setTimestamp(2, getTimeStamp());
>  // ...
>  statement.execute();
> }
> catch (SQLException exception)
> {
> LOGGER.error("", exception);
> }
> finally
> {
> DatabaseUtils.closeIfNotNull(statement);
> }
>
> If I insert duplicate records with this code, no exception is thrown
> for this particular table.
>
> I have other tables, too, with UNIQUE constraint and there the
> constraint works properly, i. e. when an attempt is made to insert a
> duplicate record, an exception is thrown.
>
> I appreciate any help about how to solve this problem.
>
> Thanks in advance
>
> Dmitri Pissarenko
> -- 
> http://www.xing.com/profile/Dmitri_Pissarenko

Craig L 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-17--158273473
Content-Disposition: attachment;
	filename=smime.p7s
Content-Type: application/pkcs7-signature;
	name=smime.p7s
Content-Transfer-Encoding: base64

MIAGCSqGSIb3DQEHAqCAMIACAQExCzAJBgUrDgMCGgUAMIAGCSqGSIb3DQEHAQAAoIIGUDCCAwkw
ggJyoAMCAQICECvOQSuIjHMvOZRC95BRg/wwDQYJKoZIhvcNAQEFBQAwYjELMAkGA1UEBhMCWkEx
JTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQ
ZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBMB4XDTA3MTIxMDE1MjM1MVoXDTA4MTIwOTE1MjM1
MVowbDEQMA4GA1UEBBMHUnVzc2VsbDEUMBIGA1UEKhMLQ3JhaWcgTGFpcmQxHDAaBgNVBAMTE0Ny
YWlnIExhaXJkIFJ1c3NlbGwxJDAiBgkqhkiG9w0BCQEWFUNyYWlnLlJ1c3NlbGxAU3VuLkNPTTCC
ASIwDQYJKoZIhvcNAQEBBQADggEPADCCAQoCggEBAKzqGlLUastboCRqc0iBoTz2ODcqpMpEyPUo
nYtluchkSIoWzOW63AuoTczRt9sKfhwoK5mope+62B6Li06WJabm2UHqKAaNSuMHLsmyqvOdwbSt
enY7/HxOSCMqVoyVBTRJc2M8feCSVgi7ptGq9cM+Maa64R1/p9zqaQNucceU/1uper90bWplsjAT
rHgicgr9XJIQb6uYjhjlgxxnY/aispnCvLxMX+CiA2FWeeJTI7AiFlLwibTXYF4v12ToByvXtTiJ
knuND8qpwhK3Wp0tL4ae8mZ0nlKjCuNnqh99ZyEyTFHZBfVx8WSWRXkY4qxCG/IDQUo7WUaefOQT
1mECAwEAAaMyMDAwIAYDVR0RBBkwF4EVQ3JhaWcuUnVzc2VsbEBTdW4uQ09NMAwGA1UdEwEB/wQC
MAAwDQYJKoZIhvcNAQEFBQADgYEAEqfFNFoch0QPVKWJ4maAZl3MJD10yMeWt5xb+WNSkhYKHD8I
42E8tpdE3kmc5wp2cZrz9JqJF/KCQ/gI4pmDk1qpTs5pvXzFNiD5Lu5eLza4iyxSlTHUXcCnyNC6
4m0qC8p4m/51NEql5hyacj/+vdlEe5dygpyNGUCiyA/SdAswggM/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
YWlsIElzc3VpbmcgQ0ECECvOQSuIjHMvOZRC95BRg/wwCQYFKw4DAhoFAKCCAW8wGAYJKoZIhvcN
AQkDMQsGCSqGSIb3DQEHATAcBgkqhkiG9w0BCQUxDxcNMDgwODEzMjIzODE0WjAjBgkqhkiG9w0B
CQQxFgQUVT1nmNnErRI+9474ZKa1IyZO/84wgYUGCSsGAQQBgjcQBDF4MHYwYjELMAkGA1UEBhMC
WkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQdHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0
ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAhArzkEriIxzLzmUQveQUYP8MIGHBgsqhkiG
9w0BCRACCzF4oHYwYjELMAkGA1UEBhMCWkExJTAjBgNVBAoTHFRoYXd0ZSBDb25zdWx0aW5nIChQ
dHkpIEx0ZC4xLDAqBgNVBAMTI1RoYXd0ZSBQZXJzb25hbCBGcmVlbWFpbCBJc3N1aW5nIENBAhAr
zkEriIxzLzmUQveQUYP8MA0GCSqGSIb3DQEBAQUABIIBAG3tNYWALjbTh+0d+lff+jP13ygxeTWF
HYYkWhexYi4xA7jkct5wpJRxm8gMcst6/gNgr2ffT9tqlRIFRQGRb4RMsIqEm/jAGkkSqsoTR0UD
WX/xoVl2lMK/Ash92xXS1r75Wn2OkukzqU9ZUVpWphMjOdECUf+6mg1ZcCGeQnkccX6tbs4Ro+wP
r1S1Zt9mDYJvnxAeDfNMuCy4QO/QBVz4KWe7Jm9LDni/GdBXXcy9txn4bi218R1142a5PGVCCkmP
fGaZfwIEwqqabtWxd0G6NFpd/XydMPUrWug8y96jbiSpHk47FwcHfsOuwuBEim0QPBrdCBMZUmLn
q5bgMYwAAAAAAAA=

--Apple-Mail-17--158273473--