Re: Problem with UNIQUE constraint

[Dmitri Pissarenko]

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