Search code examples
javaucanaccess

How can I create a Table with two Foreign Key References to one other Table via UCanAccess?

To build the References direct in MS-Access is no Problem. To do it with UCanAccess results in a "net.ucanaccess.jdbc.UcanaccessSQLException:...".

Class.forName("net.ucanaccess.jdbc.UcanaccessDriver");
Connection connection = DriverManager.getConnection("jdbc:ucanaccess://e:/TestDB.accdb;memory=true");
Statement statement = connection.createStatement();
//
String tableToBeReferenced = "PersonsTable";
String tableWithTheReferences = "RelationShipsTable";

try {// Tidy up
 statement.execute("DROP TABLE " + tableWithTheReferences);
} catch (Exception exeption) {}
try {// Tidy up
 statement.execute("DROP TABLE " + tableToBeReferenced);
} catch (Exception exeption) {}

statement.execute("CREATE TABLE " + tableToBeReferenced + "(ID autoincrement NOT NULL PRIMARY KEY,"//
    + "Name VARCHAR(255)"//
    + ")");

statement.execute("CREATE TABLE " + tableWithTheReferences + "(ID LONG NOT NULL PRIMARY KEY,"//
    + "RelationShip VARCHAR(255) NOT NULL DEFAULT 'FRIENDS',"//
    + "Person1Id LONG NOT NULL,"//
    + "Person2Id LONG NOT NULL)");

// reference #1
statement.execute("ALTER TABLE " + tableWithTheReferences + //
    " ADD CONSTRAINT FOREIGN_KEY_1 FOREIGN KEY (Person1Id) REFERENCES " //
    + tableToBeReferenced + "(ID) ON DELETE CASCADE");

// reference #2
statement.execute("ALTER TABLE " + tableWithTheReferences + //
    " ADD CONSTRAINT FOREIGN_KEY_2 FOREIGN KEY (Person2Id) REFERENCES " //
    + tableToBeReferenced + "(ID) ON DELETE CASCADE");

If I create only the first Reference it works. If I create only the second Reference it works.

But when I try to build both References it fails.

Solution

  • I am able to reproduce the issue under UCanAccess 4.0.3. Neither HSQLDB nor Jackcess has a problem with creating two independent FK relationships between the same two tables, so it looks like it might be a bug in UCanAccess. I will report the issue to the UCanAccess development team and update this answer with any news.

    Update:

    A fix for this issue has been implemented and will be included in the UCanAccess 4.0.4 release.