javasqlms-accessjdbcucanaccess

"Missing columns in relationship" when creating table

I have tried to create three tables(CUSTOMERS, VEHICLES and RENTALS), the third table (RENTALS) has foreign keys referring to the two primary keys of the first two tables (CUSTOMERS and RENTALS). When creating this third table I get an error Missing columns in relationship(Rel=CUSTOMERS[[]] -> RENTALS[[]])

Here's my codes 

     private void createTables() throws SQLException {
        Statement statement = conn.createStatement();
        statement.executeUpdate("CREATE TABLE CUSTOMERS(custNumber AUTOINCREMENT PRIMARY KEY, " +
            "firstName VARCHAR(155) NOT NULL, surname VARCHAR(155) NOT NULL, idNum INTEGER NOT NULL, phoneNum INTEGER NOT NULL, canRent BIT NOT NULL)");
        statement.executeUpdate("CREATE TABLE VEHICLES(vehNumber AUTOINCREMENT PRIMARY KEY, make VARCHAR(155) NOT NULL, " +
            "category VARCHAR(155) NOT NULL, rentalPrice FLOAT NOT NULL, availableForRent BIT NOT NULL)");
        statement.executeUpdate("CREATE TABLE RENTALS(rentalNumber AUTOINCREMENT PRIMARY KEY, dateRental VARCHAR(155) NOT NULL, dateReturned VARCHAR(155) NOT NULL, " +
            "pricePerDay FLOAT NOT NULL, totalRental FLOAT NOT NULL, custNumber INTEGER FOREIGN KEY REFERENCES CUSTOMERS(custNumber), " +
            "vehNumber INTEGER FOREIGN KEY REFERENCES VEHICLES(vehNumber))");

        System.out.println("Database populated");
     }

and here's the error enter image description here

Your help will be very much appreciated, I have looked around but found nothing that helps.

Solution

  • In Access, an AutoNumber field (DDL: AUTOINCREMENT or COUNTER) is a "Long Integer".

    In UCanAccess DDL, INTEGER creates an "Integer" (16-bit) field and LONG creates a "Long Integer" (32-bit) field.

    You need to declare your foreign key columns as LONG, not INTEGER.