JDBC: Checking if a unique column already exists during insertion

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

  1. priyom

    priyom Guest

    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
     
    priyom, Oct 5, 2006
    #1
    1. Advertising

  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
    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.
     
    timoi, Oct 5, 2006
    #2
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Chad Crowder
    Replies:
    7
    Views:
    21,553
    hemant.yadav
    Oct 24, 2009
  2. Alvin Bruney [MVP]

    Re: Class already exists ... help please!!

    Alvin Bruney [MVP], Apr 6, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    2,509
    Alvin Bruney [MVP]
    Apr 6, 2004
  3. George
    Replies:
    1
    Views:
    6,201
    Jamie
    Feb 25, 2005
  4. Alan Silver
    Replies:
    2
    Views:
    403
    Alan Silver
    Sep 29, 2005
  5. PetroTiburcio

    checking if id and reg already exists

    PetroTiburcio, Jun 5, 2013, in forum: ASP .Net Web Controls
    Replies:
    0
    Views:
    355
    PetroTiburcio
    Jun 5, 2013
Loading...

Share This Page