dbre mysql 'Foreign key table for foreign key' 'must not be null'

[rbon]

Given the following mysql database definition for user 'sa' and no password:

-- DROP SCHEMA IF EXISTS rootest;

-- CREATE SCHEMA rootest DEFAULT CHARACTER SET utf8;

-- USE rootest;

DROP TABLE IF EXISTS TeamPlayer;
DROP TABLE IF EXISTS Player;
DROP TABLE IF EXISTS Team;
DROP TABLE IF EXISTS PlayerStatusType;
DROP TABLE IF EXISTS User;

-- =================================

CREATE TABLE User
  (
    id 	bigint auto_increment PRIMARY KEY
  )
 ENGINE = InnoDB;

-- ================================

CREATE TABLE PlayerStatusType
  (
    type  varchar(63) PRIMARY KEY
  )
 ENGINE = InnoDB;

-- ===============================

CREATE TABLE Team
  (
    id       bigint auto_increment,
    city     varchar(127)   not null,
    name     varchar(127)   not null,
    version  int,

    primary key(id),
    unique(city, name)
  )
 ENGINE = InnoDB;

-- ================================

CREATE TABLE Player
  (
    id         bigint auto_increment,
    firstName  varchar(63),
    lastName   varchar(63)   not null,
    birthdate  date          not null,
    statusType varchar(63)   not null,
    version    int,

    primary key(id),
    
    foreign key(statusType) references PlayerStatusType(type)
  )
 ENGINE = InnoDB;

-- ===================================

CREATE TABLE TeamPlayer
  (
    teamId    bigint,
    playerId  bigint,
    fromDate  date,
    toDate    date,
    version   int,

    primary key(teamId, playerId, fromDate),

    foreign key(teamId) references Team(id),
    foreign key(playerId) references Player(id)
  )
 ENGINE = InnoDB;

-- ===================================

and the following roo project scripts:

// Create project

project --topLevelPackage com.roodbtest

// Setup database

persistence setup --provider HIBERNATE --database MYSQL
database properties set --key database.url --value jdbc:mysql://localhost:3306/rootest
database properties set --key database.username --value sa

database properties list

edit persistence.xml changing hibernate.hbm2ddl.auto value to 'validate'

// database reverse engineer

///////////

database reverse engineer --schema rootest --package ~.domain --testAutomatically

the following output results:

Created SRC_MAIN_RESOURCES/dbre.xml
Updated ROOT/pom.xml
Updated SRC_MAIN_RESOURCES/META-INF/persistence.xml
Created SRC_MAIN_JAVA/com/roodbtest/domain
Created SRC_MAIN_JAVA/com/roodbtest/domain/Player.java
Created SRC_MAIN_JAVA/com/roodbtest/domain/PlayerStatusType.java
Created SRC_MAIN_JAVA/com/roodbtest/domain/Team.java
Created SRC_MAIN_JAVA/com/roodbtest/domain/TeamPlayerPK.java
Created SRC_MAIN_JAVA/com/roodbtest/domain/TeamPlayer.java
Created SRC_MAIN_JAVA/com/roodbtest/domain/User.java
Undo create SRC_MAIN_JAVA/com/roodbtest/domain/User.java
Undo create SRC_MAIN_JAVA/com/roodbtest/domain/TeamPlayer.java
Undo create SRC_MAIN_JAVA/com/roodbtest/domain/TeamPlayerPK.java
Undo create SRC_MAIN_JAVA/com/roodbtest/domain/Team.java
Undo create SRC_MAIN_JAVA/com/roodbtest/domain/PlayerStatusType.java
Undo create SRC_MAIN_JAVA/com/roodbtest/domain/Player.java
Undo create SRC_MAIN_JAVA/com/roodbtest/domain
Undo manage SRC_MAIN_RESOURCES/META-INF/persistence.xml
Undo manage ROOT/pom.xml
Undo create SRC_MAIN_RESOURCES/dbre.xml
Foreign key table for foreign key 'player_ibfk_1' in table 'Player' must not be null in determining a one-to-one relationship
Script required 2 second(s) to execute
Script execution aborted
Created SRC_MAIN_JAVA/com/roodbtest/domain
Created SRC_MAIN_JAVA/com/roodbtest/domain/Player_Roo_Entity.aj
Deleted SRC_MAIN_JAVA/com/roodbtest/domain/Player_Roo_Entity.aj

This is a very simple database structure.
Why does roo have a problem?
Is this a roo defect?
Am I missing a step or doing something incorrect?

I am using roo 1.1.4.

[Alan Stewart]

Your schema worked OK for me. No errors in the generation:

roo> database reverse engineer --schema no-schema-required --package ~.domain --testAutomatically 
Created SRC_MAIN_RESOURCES/dbre.xml
Created SRC_MAIN_JAVA/com/foo/empty/domain
Created SRC_MAIN_JAVA/com/foo/empty/domain/Player.java                                                                         
Created SRC_MAIN_JAVA/com/foo/empty/domain/PlayerStatusType.java
Created SRC_MAIN_JAVA/com/foo/empty/domain/Team.java
Created SRC_MAIN_JAVA/com/foo/empty/domain/TeamPlayerPK.java
Created SRC_MAIN_JAVA/com/foo/empty/domain/TeamPlayer.java
Created SRC_MAIN_JAVA/com/foo/empty/domain/User.java
Created SRC_TEST_JAVA/com/foo/empty/domain
Created SRC_TEST_JAVA/com/foo/empty/domain/PlayerDataOnDemand.java
Created SRC_TEST_JAVA/com/foo/empty/domain/PlayerIntegrationTest.java
Created SRC_TEST_JAVA/com/foo/empty/domain/PlayerStatusTypeDataOnDemand.java
Created SRC_TEST_JAVA/com/foo/empty/domain/PlayerStatusTypeIntegrationTest.java
Created SRC_TEST_JAVA/com/foo/empty/domain/TeamDataOnDemand.java
Created SRC_TEST_JAVA/com/foo/empty/domain/TeamIntegrationTest.java
Created SRC_TEST_JAVA/com/foo/empty/domain/TeamPlayerDataOnDemand.java
Created SRC_TEST_JAVA/com/foo/empty/domain/TeamPlayerIntegrationTest.java
Created SRC_TEST_JAVA/com/foo/empty/domain/UserDataOnDemand.java
Created SRC_TEST_JAVA/com/foo/empty/domain/UserIntegrationTest.java
Created SRC_MAIN_JAVA/com/foo/empty/domain/Player_Roo_Configurable.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/Player_Roo_Entity.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/Player_Roo_DbManaged.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/Player_Roo_ToString.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/PlayerStatusType_Roo_Configurable.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/PlayerStatusType_Roo_Entity.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/PlayerStatusType_Roo_ToString.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/Team_Roo_Configurable.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/Team_Roo_Entity.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/Team_Roo_DbManaged.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/Team_Roo_ToString.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/TeamPlayer_Roo_Configurable.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/TeamPlayer_Roo_Entity.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/TeamPlayerPK_Roo_Configurable.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/TeamPlayerPK_Roo_Identifier.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/TeamPlayerPK_Roo_Json.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/TeamPlayerPK_Roo_Serializable.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/TeamPlayer_Roo_DbManaged.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/TeamPlayer_Roo_ToString.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/User_Roo_Configurable.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/User_Roo_Entity.aj
Created SRC_MAIN_JAVA/com/foo/empty/domain/User_Roo_ToString.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/UserDataOnDemand_Roo_Configurable.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/UserDataOnDemand_Roo_DataOnDemand.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/TeamPlayerIntegrationTest_Roo_Configurable.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/TeamDataOnDemand_Roo_DataOnDemand.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/PlayerStatusTypeDataOnDemand_Roo_DataOnDemand.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/PlayerDataOnDemand_Roo_DataOnDemand.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/TeamPlayerDataOnDemand_Roo_DataOnDemand.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/TeamPlayerIntegrationTest_Roo_IntegrationTest.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/PlayerStatusTypeDataOnDemand_Roo_Configurable.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/TeamPlayerDataOnDemand_Roo_Configurable.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/TeamIntegrationTest_Roo_Configurable.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/TeamIntegrationTest_Roo_IntegrationTest.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/TeamDataOnDemand_Roo_Configurable.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/PlayerIntegrationTest_Roo_Configurable.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/PlayerIntegrationTest_Roo_IntegrationTest.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/PlayerDataOnDemand_Roo_Configurable.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/PlayerStatusTypeIntegrationTest_Roo_Configurable.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/PlayerStatusTypeIntegrationTest_Roo_IntegrationTest.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/UserIntegrationTest_Roo_Configurable.aj
Created SRC_TEST_JAVA/com/foo/empty/domain/UserIntegrationTest_Roo_IntegrationTest.aj

Can you reproduce this every time? Can you start again with a clean empty project?

[rbon]

I have tried numerous times with no success.
Each time I exit roo and clean the directory of all files.
I have run the commands from within a script as well as individually on the command line.
I even changed the primary keys to be all BigInt. Same result every time.

I am using OS X 10.5.8, java 1.6.0_24.

[Alan Stewart]

What happens when you run the database introspect command? Do you see the XML of the schema? If so, use the --file option on this command and attach the output here

[rbon]

roo> database introspect --schema rootest

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!--WARNING: DO NOT EDIT THIS FILE. THIS FILE IS MANAGED BY SPRING ROO.-->
<database name="rootest">
    <option key="testAutomatically" value="false"/>
    <option key="includeNonPortableAttributes" value="true"/>
    <table name="Particpant">
        <column name="id" primaryKey="true" required="true" scale="0" size="19" type="-5,BIGINT"/>
        <unique name="PRIMARY">
            <unique-column name="id"/>
        </unique>
    </table>
    <table name="Player">
        <column name="id" primaryKey="true" required="true" scale="0" size="19" type="-5,BIGINT"/>
        <column name="firstName" primaryKey="false" required="false" scale="0" size="63" type="12,VARCHAR"/>
        <column name="lastName" primaryKey="false" required="true" scale="0" size="63" type="12,VARCHAR"/>
        <column name="birthdate" primaryKey="false" required="true" scale="0" size="10" type="91,DATE"/>
        <column name="statusType" primaryKey="false" required="true" scale="0" size="63" type="12,VARCHAR"/>
        <column name="version" primaryKey="false" required="false" scale="0" size="10" type="4,INT"/>
        <foreign-key foreignTable="playerstatustype" name="player_ibfk_1" onDelete="none" onUpdate="none">
            <option key="exported" value="false"/>
            <reference foreign="type" local="statusType"/>
        </foreign-key>
        <foreign-key foreignTable="TeamPlayer" name="teamplayer_ibfk_2" onDelete="none" onUpdate="none">
            <option key="exported" value="true"/>
            <reference foreign="playerId" local="id"/>
        </foreign-key>
        <unique name="PRIMARY">
            <unique-column name="id"/>
        </unique>
        <index name="statusType">
            <index-column name="statusType"/>
        </index>
    </table>
    <table name="PlayerStatusType">
        <column name="type" primaryKey="true" required="true" scale="0" size="63" type="12,VARCHAR"/>
        <foreign-key foreignTable="Player" name="player_ibfk_1" onDelete="none" onUpdate="none">
            <option key="exported" value="true"/>
            <reference foreign="statusType" local="type"/>
        </foreign-key>
        <unique name="PRIMARY">
            <unique-column name="type"/>
        </unique>
    </table>
    <table name="Team">
        <column name="id" primaryKey="true" required="true" scale="0" size="19" type="-5,BIGINT"/>
        <column name="city" primaryKey="false" required="true" scale="0" size="127" type="12,VARCHAR"/>
        <column name="name" primaryKey="false" required="true" scale="0" size="127" type="12,VARCHAR"/>
        <column name="version" primaryKey="false" required="false" scale="0" size="10" type="4,INT"/>
        <foreign-key foreignTable="TeamPlayer" name="teamplayer_ibfk_1" onDelete="none" onUpdate="none">
            <option key="exported" value="true"/>
            <reference foreign="teamId" local="id"/>
        </foreign-key>
        <unique name="PRIMARY">
            <unique-column name="id"/>
        </unique>
        <unique name="city">
            <unique-column name="city"/>
            <unique-column name="name"/>
        </unique>
    </table>
    <table name="TeamPlayer">
        <column name="teamId" primaryKey="true" required="true" scale="0" size="19" type="-5,BIGINT"/>
        <column name="playerId" primaryKey="true" required="true" scale="0" size="19" type="-5,BIGINT"/>
        <column name="fromDate" primaryKey="true" required="true" scale="0" size="10" type="91,DATE"/>
        <column name="toDate" primaryKey="false" required="false" scale="0" size="10" type="91,DATE"/>
        <column name="version" primaryKey="false" required="false" scale="0" size="10" type="4,INT"/>
        <foreign-key foreignTable="team" name="teamplayer_ibfk_1" onDelete="none" onUpdate="none">
            <option key="exported" value="false"/>
            <reference foreign="id" local="teamId"/>
        </foreign-key>
        <foreign-key foreignTable="player" name="teamplayer_ibfk_2" onDelete="none" onUpdate="none">
            <option key="exported" value="false"/>
            <reference foreign="id" local="playerId"/>
        </foreign-key>
        <unique name="PRIMARY">
            <unique-column name="teamId"/>
            <unique-column name="playerId"/>
            <unique-column name="fromDate"/>
        </unique>
        <index name="playerId">
            <index-column name="playerId"/>
        </index>
    </table>
</database>

I should have looked at this output earlier. The status table is 'PlayerStatusType' and the foreign key table in player is pointing to 'playerstatustype'. According to MySQL, on os x, tables are stored with case sensitivity but compared as lower case. However, one must not mix case within a single statement.
From mysql command line, I can query a table in upper or lower case.

I will convert all table names to lower case and try again.
If that does not work, I will convert all field names to lower case.

[rbon]

Conversion of table names to lower case results in correct output.

However, I should be able to have my table names in the case of my choosing.
Another downside to lower case table names is the resulting class names. For example, table 'teamplayer' becomes Teamplayer.java whereas table 'TeamPlayer' becomes TeamPlayer.java.

For reasons that may be shallow and superficial, I would like to have my table names the same as my pojo names. Maybe this is just aesthetics, but I prefer to avoid the underscore separator, 'team_player' in table names. It makes searching through documentation simpler as one has a single name for all aspects of an entity.

Should this roo shortcoming be entered as bug?

[Alan Stewart]

Underscores in table names is very common and unless your table has one of these characters, or a hyphen, back or forward slash, or period, DBRE has no way of determining where to split the word. Also DBRE uses the java.sql.DatabaseMetaData.storesLowerCaseIdentifie rs() and java.sql.DatabaseMetaData.storesUpperCaseIdentifie rs() methods to determine how to handle generation of table names, however, MySQL stores mixed case identifiers so I don't understand your problem as it generated the entities for me with no errors from the outset. My dbre.xml has the correct case for the foreign key PlayerStatusType (in mixed case) where as yours in is in lowercase, hence your error.

What version of MySQL are you using?

[rbon]

mysql> status;
--------------
mysql Ver 14.14 Distrib 5.1.49, for apple-darwin9.8.0 (i386) using readline 5.1

Connection id: 71
Current database: rootest
Current user: sa@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.1.49-log MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: utf8
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /tmp/mysql.sock
Uptime: 37 days 1 hour 42 min 26 sec

[Cedric.Vidrequin]

Hi there,


I have the same kind of error but I didn't find the solution ... do anyone has an idea of the source of the trouble ?

java.lang.IllegalStateException: java.lang.IllegalArgumentException: Foreign key table for foreign key 'FK_T_ASSO_C_ASSO_CLI__T_CLIENT' in table 'T_ASSO_CLI_FAC' must not be null in determining a one-to-one relationship

On my side, all tables and columns names are upper-case ...
I work with an Oracle data base.

I use :
- ojdbc14_g.jar version 10.2.0.5.0
- SpringSource Tool Suite Version: 2.6.1.RELEASE
- Roo Suport : Roo 1.2.0.BUILD-SNAPSHOT


Thanks for helping.


Cédric.

[Alan Stewart]

Please attach your Oracle schema DDL if possible