How is the duplicate check implemented technically — as a separate verification process before insertion or something else?

Duplicate checks are usually implemented at the database level using unique constraints or unique indexes. This ensures data uniqueness without the need for a separate process before insertion.

If the check is implemented at the application level, it is often done within a transaction: first, a query is executed to find an existing record with the same keys, then an insert is performed if no duplicate is found. However, this approach is susceptible to race conditions in a multi-threaded environment.

Therefore, the optimal approach is:

• Use unique indexes in the database for automatic checking.
• Handle uniqueness errors (for example, SQLException with a violation code of a unique constraint) in the application.

Example in Java using JDBC:

try {
    // attempt to insert
    insertIntoTable(data);
} catch (SQLException e) {
    if (e.getSQLState().equals("23505")) { // error code for unique constraint in PostgreSQL
        System.out.println("Duplicate found, insertion canceled");
    } else {
        throw e;
    }
}