@DatabaseField(foreign = true) doesn't generate foreign key constraints

[RichardForsey89]

Hi, this is my first git bug report so please bear with me.

For a project I have been trying to use the annotations as per the documentation. However, when I have:

@DatabaseTable(tableName = "Profiles")
public class Profile {
@DatabaseField(generatedId = true)
    private int profile_id;
}

@DatabaseTable(tableName = "Workspaces")
public class Workspace {
@DatabaseField(generatedId = true)
    private int workspace_id;
@DatabaseField(foreign = true, foreignAutoCreate = true, foreignAutoRefresh = true, canBeNull = false)
    private Profile profile;
}

public DatabaseManager() throws SQLException, IOException {
this.databaseUrl = "jdbc:sqlite:ORMLiteTest.db";
this.connectionSource = new JdbcConnectionSource(databaseUrl);

this.profileDAO = DaoManager.createDao(connectionSource, Profile.class);
this.workspaceDAO = DaoManager.createDao(connectionSource, Workspace.class);

TableUtils.createTableIfNotExists(connectionSource, Workspace.class);
TableUtils.createTableIfNotExists(connectionSource, Profile.class);

connectionSource.close();

The resulting two tables created have their primary key, and for Workspaces, it will have the profile_id field as NOT NULL, but it won't have the Foreign Key tag set.

[j256]

Welcome @RichardForsey89 . I don't quite understand what you are saying. Are you saying that the SQL generated doesn't add in some SQLite specific Foreign Key tag? ORMLite's SQL generation certainly does have some holes. Can you create your schema by hand (yes a pain) and then use ORMLite just for the entities?

In the future, be careful of statements like "doesn't work" and be more specific. Thanks.

[RichardForsey89]

Thank you for the quick reply Gray. The generated schema would be missing the Foreign Key constraint, which would then cause issues with INSERTs to Profiles where a child Workspace would be generated, but the child workspace wouldn't have the profile_id filled in, but the Profile entity would have a field of workspace_id filled with the child's. So at first glace it would seem that using ORMlite for the entities would be troublesome for my situation.

I admit that I could be incorrect with my expectations/application of ORMlite. Am I to understand that the Foreign Key constraint is only enforced at the software layer, rather than in the schema?

Also, I'll be more careful of my language in future.

[j256]

Supporting perfect schema generation on all of the supported database types is hard. Right now its doesn't setup foreign key constraints unfortunately. I would recommend adding them afterwards by hand. You can use the Dao.executeRaw(...) method for this.