greenDAO not generating FOREIGN KEY(...) constraint in table

Question

When I create a bidirectional 1:n relationship as shown below, the generator does not use any FOREIGN KEY(...) constraints on the table.

entity customer = schema.addEntity("Customer");
customer.addIdProperty();
customer.addStringProperty("name").notNull();

Entity order = schema.addEntity("Order");
order.setTableName("ORDERS"); // "ORDER" is a reserved keyword
order.addIdProperty();
Property orderDate = order.addDateProperty("date").getProperty();
Property customerId = order.addLongProperty("customerId").notNull().getProperty();
order.addToOne(customer, customerId);

customer.addToMany(order, customerId);

Is this normal? Is it supposed to generate FOREIGN KEY(...) constraints in the table or is it only enforced at runtime through code?

Did you find out about it? – android developer Feb 18 '14 at 08:41 — android developer, Feb 18 '14 at 08:41
No. I wish @greenrobot would respond. – Monstieur Feb 18 '14 at 08:48 — Monstieur, Feb 18 '14 at 08:48

DroidBoyJr · Answer 1 · 2016-07-10T17:51:31.787

I was stuck at the same issue while working on a project.

Going through the generated code by DaoGenerator, foreign key constraints are not generated even with the use of ToMany relation.

I tried appending the foreign key constraint manually in the query in each entity DAO, and yet it didn't solve the issue.

Referring the sqlite documentation, I found that the foreign key is not enforced by-default. You have to run query PRAGMA foreign_keys = ON; for every connection created to database. I verified it from adb shell. Foreign key was enforced after running PRAGMA query.

Last problem was to find a place for this code in project so that it will execute for every session.

Solution is in DaoSession class generated by the DaoGenerator project

insert

 if(!db.isReadOnly()){
     db.execSQL("PRAGMA foreign_keys = ON;");
 }

at the end of the constructor.

Don't forget to manually add foreign key constraint in create table queries for each DAO having foreign key property.

score 0 · Answer 2 · answered Feb 03 '15 at 11:11

order.addToOne(customer, customerId);

is correct and it creats fk relationship with customer table. but the next statement

customer.addToMany(order, customerId);

is not valid because there is no property (called customerId) is added to customer table

so in this case if you want to create to-many relation to customer table use like

addToMany(sourceProperty, target, targetProperty). But in my view there is no need of customer.addToMany(order, customerId)

score -1 · Answer 3 · edited Jul 28 '16 at 08:09

you can write directly to createTable(){} in XXXXDao,

public static void createTable(Database db, boolean ifNotExists) {
    String constraint = ifNotExists? "IF NOT EXISTS ": "";
    String sql = "CREATE TABLE " + constraint + "\"PERSONGROUPS\" (" + //
                "\"_id\" INTEGER PRIMARY KEY ," + // 0: id
                "\"PID\" TEXT," + // 1: pid
                "\"GROUP_ID\" INTEGER," +
                "FOREIGN KEY(\"PID\") REFERENCES PERSONS(\"PID\") ON DELETE CASCADE," +
                "FOREIGN KEY(\"GROUP_ID\") REFERENCES GROUPS(\"_id\") ON DELETE CASCADE)";
    Log.v("PersonGroupDao", sql);
    db.execSQL(sql); // 2: group_id
}

and you must run db.execSQL("PRAGMA foreign_keys = ON;");

greenDAO not generating FOREIGN KEY(...) constraint in table

3 Answers3