2

I use: sqlite-jdbc-3.21.0.jar

Error received:

SQLite_CONSTRAINT_UNIQUE] A UNIQUE constraint failed: companies.ncf

I ask a few things to the user, there is two fields that are optional to complete but, if those fields have data, they need to be unique on the entire database. If the user leaves both empty, finishes completing the form, and clicks the Save button, this is when I receive the error mentioned above.

Data to insert:

company_id: (empty) (auto increment)
name:       WebServices Inc.
postal:     56585
rnc:        (null)
ncf:        (null)

DDL:

CREATE TABLE companies (
    company_id          INTEGER PRIMARY KEY,
    name                TEXT    NOT NULL,
    postal              INTEGER,
    rnc                 TEXT    UNIQUE,
    ncf                 TEXT    UNIQUE
);

Code to insert (I receive the error with this):

String sql =    "INSERT INTO companies "
                + "(name, postal, rnc, ncf) "
                + "VALUES (?, ?, ?, ?)";

        // Java 7 try-with-resources
        try (Connection conn = DatabaseConnection.getConnection();
                PreparedStatement pstm = conn.prepareStatement(sql)) {
            pstm.setString(1, company.getName());
            pstm.setInt(2, company.getPostal());
            pstm.setString(3, company.getRnc());
            pstm.setString(4, company.getNcf());
            pstm.executeUpdate();
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, e, "SQLException", JOptionPane.ERROR_MESSAGE);
            }

I tried (I receive the error with this too):

    // Java 7 try-with-resources
    try (Connection conn = DatabaseConnection.getConnection();
            PreparedStatement pstm = conn.prepareStatement(sql)) {
        pstm.setString(1, company.getNombre());
        pstm.setInt(2, company.getPostal());

        if (company.getRnc() != "") {
            pstm.setString(3, company.getRnc());
        } else {
            pstm.setNull(3, java.sql.Types.VARCHAR);
        }

        if (company.getNcf() != "") {
            pstm.setString(4, company.getNcf());
        } else {
            pstm.setNull(4, java.sql.Types.VARCHAR);
        }

        pstm.executeUpdate();
        } catch (SQLException e) {
            JOptionPane.showMessageDialog(null, e, "SQLException", JOptionPane.ERROR_MESSAGE);
            }

A document from SQLite about NULL Handling in SQLite says that:

This involved making NULLs indistinct for the purposes of the SELECT DISTINCT statement and for the UNION operator in a SELECT. NULLs are still distinct in a UNIQUE column.

This means that we can insert multiple values to a UNIQUE column in a table because NULLs are distinct. So, why I do get the error?

StackTrace:

org.sqlite.SQLiteException: [SQLITE_CONSTRAINT_UNIQUE]  A UNIQUE constraint failed (UNIQUE constraint failed: empresas.ncf)
    at org.sqlite.core.DB.newSQLException(DB.java:909)
    at org.sqlite.core.DB.newSQLException(DB.java:921)
    at org.sqlite.core.DB.execute(DB.java:825)
    at org.sqlite.core.DB.executeUpdate(DB.java:863)
    at org.sqlite.jdbc3.JDBC3PreparedStatement.executeUpdate(JDBC3PreparedStatement.java:99)
    at databaseTier.TransactionEmpresa.insert(TransactionEmpresa.java:210)
    at presentationTier.FormularioEmpresa$1.actionPerformed(FormularioEmpresa.java:199)
    at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
    at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
    at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
    at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
    at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
    at java.awt.Component.processMouseEvent(Unknown Source)
    at javax.swing.JComponent.processMouseEvent(Unknown Source)
    at java.awt.Component.processEvent(Unknown Source)
    at java.awt.Container.processEvent(Unknown Source)
    at java.awt.Component.dispatchEventImpl(Unknown Source)
    at java.awt.Container.dispatchEventImpl(Unknown Source)
    at java.awt.Component.dispatchEvent(Unknown Source)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
    at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
    at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
    at java.awt.Container.dispatchEventImpl(Unknown Source)
    at java.awt.Window.dispatchEventImpl(Unknown Source)
    at java.awt.Component.dispatchEvent(Unknown Source)
    at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
    at java.awt.EventQueue.access$500(Unknown Source)
    at java.awt.EventQueue$3.run(Unknown Source)
    at java.awt.EventQueue$3.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
    at java.awt.EventQueue$4.run(Unknown Source)
    at java.awt.EventQueue$4.run(Unknown Source)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
    at java.awt.EventQueue.dispatchEvent(Unknown Source)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
    at java.awt.EventDispatchThread.run(Unknown Source)
  • 2
    I would suggest that empty is not the same as null. Are you absolutely sure that null was trying to be inserted into the column when the user left the data empty? Can you set up a test scenario with a table that does not have the unique constraint and have the user leave the data empty and see what winds up in the table? – Scott Hodgin Mar 2 '18 at 13:08
  • @ypercubeᵀᴹ I used Statement and the query VALUES ('example', ' ', null, null) and it worked!. I tried to print the SQL but, I didn't get nothing printed. I used SQLiteStudio and I tried the query and also worked. If I change the query to VALUES ('example', ' ', ' ', ' ') doesn't work anymore because there is two empty values and on a column only must exist 1 empty value, another empty value will be a repeated value. So, this brings us to the way that I'm using to insert a NULL value to the database, which is not working. Is not entering the NULL value at all, apparently. – RRGT19 Mar 2 '18 at 19:06
  • Let us continue this discussion in chat. – ypercubeᵀᴹ Mar 2 '18 at 19:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.