JDBC: Checking if a unique column already exists during insertion

[priyom]

Hi,

Say I have the following table:

CREATE TABLE TEST (

COL1 BIGINT PRIMARY KEY,
COL2 VARCHAR(50),
COL3 VARCHAR(50,
COL4 INT,

CONSTRAINT uniq_2_3 UNIQUE KEY(COL2, COL3)

);

I want to data to this table through JDBC. Is there any way to check if
the data I'm tryin to insert violates the unique constraint?
On a more generic note, during insertion, is there any way to check if
the data I'm trying to insert to a table, violates any particular
(unique or not) constraints on it?
Of course, if I try to insert a row with duplicate data, it would cause
an SQL exception but is there any way to programmatically retrieve the
cause?
I do not want to execute a select query before insertion.

Note: I am using MySql.

Thanks in advance,
Priyom

 

[timoi]

I think it would be best to work with the SQLException. By using the
getErrorCode() you can retrieve MySQL's error code for a duplicate
entry in a unique column.

Another (although imho cumbersome) possibility that should not produce
any message: Create (or use an existing) table with only one row (in
Oracle, this would be DUAL). Instead of a simple INSERT, do the
following:

INSERT INTO test (col1, col2, col3, col4)
SELECT 1, 'content', 'more', 123
FROM my_table_with_only_one_row
MINUS
SELECT 1, 'content', 'more', 123
FROM test
WHERE col2 = 'content' AND col3 = 'more'

col1 of test is the unique column. The statement tries to insert rows
into test. Therefore, it 'reads' all the rows from
my_table_with_only_one_rowl (that means: one), but does not use column
values from my_table_with_only_one_row but self-defined values (1,
'content', 'more' and 123). But from this set of rows (containing
exactly one row) it takes away those rows that already are in test.

Okay, this will not work in MySQL as you have no minus. And there is
another problem: If there already is a row with the same value in col1
OR in col2 and col3 (so the new row is different but conflicts with the
primary key or the unique constraint), there will be an error again.

Let's look at a another way to achieve it:

INSERT INTO test (col1, col2, col3, col4)
SELECT *
FROM (
SELECT 1 AS c1, 'content' AS c2, 'more' AS c3, 123 AS c4
FROM my_table_with_only_one_row
) d
WHERE NOT EXISTS (
SELECT t.*
FROM test t
WHERE t.col1 = d.c1 OR
(t.col2 = d.c2 AND t.col3 = d.c3)
)

So now we use the one row from my_table_with_only_one_row as a
subquery. We only want to have the row as a result in our query when
there is no row in test for which it either conflicts with the primary
key or the unique constraint. MySQL seams to supports this query.

The hole method I'm proposing seems awkward to me so please consider to
use the SQLException - which is thrown to avoid such evil workarounds.

And: You do use a SELECT query before inserting in this way - it's just
in the same statement you send to the database. Sending a SELECT
statement first and then inserting does not really keep you save and
warm: as soon as two threads try to do this at the same time, the error
is back.