UNIQUE constraint does not exist on referenced columns

[Anne]

I am trying to create a very simple connection to an access database (.accdb) in my jave maven project using ucanaccess. The database is an external database and I just need to read the contect of some tables for a migration task. That means, I shouldn't change the database.
The java code, that I used is totally simple:*

try (Connection connection = DriverManager.getConnection(databaseURL);) {
} catch (SQLException ex) {
    ex.printStackTrace();
}

But the connection fails right at the beginning with the following exception:

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::5.0.0 a UNIQUE constraint does not exist on referenced columns: T1 in statement [ALTER TABLE T2 ADD CONSTRAINT "T2{2EB41B92-C3AB-4A64-A53C-B83095D76202}" FOREIGN KEY  (C2)  REFERENCES T1 (C1) ]
    at net.ucanaccess.jdbc.UcanaccessDriver.connect(UcanaccessDriver.java:231)
    at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
    at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:251)
    at myproject.Application.main(Application.java:42)
Caused by: java.sql.SQLSyntaxErrorException: a UNIQUE constraint does not exist on referenced columns: T1 in statement [ALTER TABLE T2 ADD CONSTRAINT "T2{2EB41B92-C3AB-4A64-A53C-B83095D76202}" FOREIGN KEY  (C2)  REFERENCES T1 (C1) ]
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.executeUpdate(Unknown Source)
    at net.ucanaccess.converters.LoadJet.exec(LoadJet.java:1510)
    at net.ucanaccess.converters.LoadJet.access$000(LoadJet.java:74)
    at net.ucanaccess.converters.LoadJet$TablesLoader.loadForeignKey(LoadJet.java:695)
    at net.ucanaccess.converters.LoadJet$TablesLoader.loadTableFKs(LoadJet.java:918)
    at net.ucanaccess.converters.LoadJet$TablesLoader.recreate(LoadJet.java:807)
    at net.ucanaccess.converters.LoadJet$TablesLoader.loadTableData(LoadJet.java:877)
    at net.ucanaccess.converters.LoadJet$TablesLoader.loadTableData(LoadJet.java:871)
    at net.ucanaccess.converters.LoadJet$TablesLoader.loadTableData(LoadJet.java:837)
    at net.ucanaccess.converters.LoadJet$TablesLoader.loadTablesData(LoadJet.java:1029)
    at net.ucanaccess.converters.LoadJet$TablesLoader.loadTables(LoadJet.java:1077)
    at net.ucanaccess.converters.LoadJet$TablesLoader.access$3200(LoadJet.java:264)
    at net.ucanaccess.converters.LoadJet.loadDB(LoadJet.java:1579)
    at net.ucanaccess.jdbc.UcanaccessDriver.connect(UcanaccessDriver.java:218)
    ... 3 more
Caused by: org.hsqldb.HsqlException: a UNIQUE constraint does not exist on referenced columns: T1
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.error.Error.error(Unknown Source)
    at org.hsqldb.TableWorks.checkCreateForeignKey(Unknown Source)
    at org.hsqldb.TableWorks.addForeignKey(Unknown Source)
    at org.hsqldb.StatementSchema.getResult(Unknown Source)
    at org.hsqldb.StatementSchema.execute(Unknown Source)
    at org.hsqldb.Session.executeCompiledStatement(Unknown Source)
    at org.hsqldb.Session.executeDirectStatement(Unknown Source)
    at org.hsqldb.Session.execute(Unknown Source)
    ... 18 more

I tried to make my connection "readonly", so that it doesn't check for this constraint and doesn't throw an exception. But I didn't have any success.

try (Connection connection = DriverManager.getConnection(databaseURL + ";readonly=true");) {
} catch (SQLException ex) {
    ex.printStackTrace();
}

Is there any way to turn off this constraint check, while creating the connection for reading some tables?

[Marco Amadei]

Hi Anne,
please post along in attachment the database (without data) so that I can reproduce the issue

[Anne]

Hi Marco,
I can't post the database, because it is a very complex database with many tables and relationships and it doesn't belonge to me. I am not allowed to share it (even without data) because of General Data Protection Regulation.

Furthermore I didn't want to change anything in the database, therefore I wanted to turn off the constraint checks to be able to just connect to the database and just READ the rows of some tables.

I also made a copy of the database and delete all the relationships between the tables and the code could then connect to the db, BUT IT TOOK 30 MINUTES TO CONNECT!!!! So, even if, I would have managed to make a copy from the database and delete all relationships between all tables each time I want to run my code (which actually isn't possible in my task), I should have waited 30 minutes to just open a connection!!!
I find it really ridiculous, that there is no possibility to turn off the constraint checks and open the connection for reading data, when some constraint aren't satisfied!!!

But anyway, at the end (after two days searching!) I found a solution for my work. I don't use any kind of Connection of DriverManager of this type:

Connection connection = DriverManager.getConnection(databaseURL)

Instead I use a DatabaseBuilder to open my database and get the tables and read the rows!
For anyone else, who have the same problem and just wants to connect to a MS-Access database in order to READ some data, without changing anything, I suggest this way. It is very easy and fast!

String fileName = "my_database.accdb";
File file = new File(fileName);
Database db = null;
DatabaseBuilder databaseBuilder = new DatabaseBuilder(file);
try {
     db = databaseBuilder.open();
} catch(IOException e) {
     e.printStackTrace();
}
Table myTable = db.getTable("myAccessTableName");
for(Row row : myTable) {
     String firstName = row.getString("first_name");
     String lastName = row.getString("last_name");
}

The Database, DatabaseBuilder, Row and Table are from "jackcess".

import com.healthmarketscience.jackcess.Database;
import com.healthmarketscience.jackcess.DatabaseBuilder;
import com.healthmarketscience.jackcess.Row;
import com.healthmarketscience.jackcess.Table;

Here are some examples which use this way:
https://www.programcreek.com/java-api-examples/?api=com.healthmarketscience.jackcess.Database

http://ucanaccess.sourceforge.net/site.html#examples