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.

Advertisements

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:

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.

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.

Share This Page

Welcome to The Coding Forums!

Welcome to the Coding Forums, the place to chat about anything related to programming and coding languages.

Please join our friendly community by clicking the button below - it only takes a few seconds and is totally free. You'll be able to ask questions about coding or chat with the community and help others.
Sign up now!