CODEWITHSUNDEEP
javams-accessjdbchsqldbucanaccess
arsarc
arsarc

Reputation: 453

UCanAccess: Set required field on and off to prevent integrity constraint violation: NOT NULL error

I'm working on connecting Java with an old Access database because that's what's asked.

I keep getting the error:

integrity constraint violation: NOT NULL error

But when I went in the database, I found out a lot of the fields are actually null in one of the required fields. I'm guessing the required field was added later. When I set the field to not required, my code works fine.

enter image description here

It needs to be required so that the people entering data don't forget it, but at the same time, there are over 20000 records without this field as it is something recently added, I'm guessing.

Is there a way to turn on and turn off the required field for this table from java with UCanAccess? I'm guess it's probably not ideal, and it'd be best to turn it off, and create a check in one of the data entry forms in Access to make sure that field gets filled? What would be the best way to approach this? I don't have a lot of tools to work with besides Java and Access unfortunately where I am at. I'm not sure what the best practice would be.

This is my code (edited for public), which actually works:

    TimeEntry t = new TimeEntry();
    ArrayList<TimeEntry> tList = new ArrayList<TimeEntry>();
try{
            Connection conn=DriverManager.getConnection("jdbc:ucanaccess://\\\\server\\folder1\\folder2\\folder3\\folder4\\folder5\\Database21.mdb");
            Statement st = conn.createStatement();
            ResultSet rs = st.executeQuery("SELECT [PLANT], [DEPARTMENT], [WONum] FROM [Query1] where [Entry-dt] >= #2/26/2016# ");
        while (rs.next()) {

            t.setPlant(rs.getString(1));
            t.setDept(rs.getString(2));
            t.setWo(rs.getString(3));
            System.out.println(t.toString());
            tList.add(t);
            t = new TimeEntry();

            System.out.println(rs.getString(3));
            }

        } catch(Exception e){

        }

Upvotes: 1

Views: 1063

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123654

Microsoft Access allows existing records to retain NULL values after a field (column) is converted from "Required=No" (NULL constraint) to "Required=Yes" (NOT NULL constraint). Therefore it is possible for an Access database to have a NOT NULL column that actually contains some NULL values.

HSQLDB, which UCanAccess uses as a backing database, does not allow that. So, the UCanAccess development team needed to find a way to reconcile the two behaviours. They didn't want to simply ignore the NOT NULL constraint altogether (for the same reason you cite in your question), but they also didn't want to force users to change the data in that table if all they wanted to do is read it.

The compromise they implemented was that if a table was found to have NULL values in a NOT NULL column then a warning would be issued and the table would be made read-only (so UCanAccess could safely ignore the NOT NULL constraint):

WARNING:Detected Not Null constraint breach, table Table2, record ... : making the table Table2 readonly

If all you want to do is read from that table then you can just ignore the warning. If you need to write to that table then you need to fix the data in the NOT NULL column.

(UCanAccess is unable to support ALTER TABLE statements, so it cannot change the [NOT] NULL status of an existing column.)

According to your screenshot the field in question has "Allow Zero Length=Yes" so you could simply open the database in Access and update the column using

UPDATE TableName SET FieldName='' WHERE FieldName IS NULL

Once that change has been applied then UCanAccess will be able to update the table.

Upvotes: 3

Related Questions