JDBC: Checking if a unique column already exists during insertion

Discussion in 'Java' started by priyom, Oct 5, 2006.

  1. priyom

    priyom Guest


    Say I have the following table:


    COL2 VARCHAR(50),
    COL3 VARCHAR(50,
    COL4 INT,



    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
    I do not want to execute a select query before insertion.

    Note: I am using MySql.

    Thanks in advance,
    priyom, Oct 5, 2006
    1. Advertisements

  2. priyom

    timoi Guest

    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

    INSERT INTO test (col1, col2, col3, col4)
    SELECT 1, 'content', 'more', 123
    FROM my_table_with_only_one_row
    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
    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.
    timoi, Oct 5, 2006
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.