Foreign key relations for Android

As shown in the first example a foreign key in the order referencing the customer can be defined like this:

/**
 * The foreign key field 'fkField' refers to {@link #customer},
 * it will be set via {@link #setCustomer(Customer)}.
 */
@SeifeField(foreignKey = @ForeignkeyDef(fkField = "customer", refKeyField="id", refClass=Customer.class))
private Long customerId;

private Customer customer;

The foreignKey = @ForeignkeyDef( /* .. */ ) specifies how the reference should be defined.

• fkField
  refers to the name of the instance reference private Customer customer that the generated code uses when the reference is resolved
• refKeyField
  refers to the primary key that this field refers to, the datatypes must match
• refClass
  specifies what type of instance should be handled

The reference mechanism is kept as plain and simple as possible. No intermediate library is required, also no generic types or special collections are used. What you get is plain android code based on templates that is easy to read since the foreign key definitions are similar and self-explaining.

The generated schema peer has the sql foreign key references defined correctly, but no index is created unless it is specified explicitly. (see the SQLite documentation or e.g. this StackOverflow question)

/**
 * Table creation script
 */
private static final String SQL_CREATE_TABLE_ORDER =
    "create table " + TBL_ORDER + " (" + 
        COL_ID + " integer primary key autoincrement," +
        COL_FK_CUSTOMER_CUSTOMER_ID + " integer," +
        COL_ORDER_DATE + " integer not null" +
        ", " +
        " FOREIGN KEY(" + COL_FK_CUSTOMER_CUSTOMER_ID  + ")" +
        " REFERENCES " + CustomerSchema.TBL_CUSTOMER 
        +"(" + CustomerSchema.COL_ID  + ")" +			 		
        ")";

Referencing combined primary keys

If the foreign key is referring to a unique tuple of columns, hence technically correct this is also possible. It is accomplished by defining all technical key columns with the fkField pointing to the same instance field.

If we have e.g. a class that has a combined primary key

@SeifeClass(sqlTablename="locale", version=2, generatorOptions={GeneratorOption.BOCLASS, GeneratorOption.SCHEMA_PEER})
public class Language {

  @SeifeField(isPrimaryKey=true, sqlOptions=@SqlFieldOptions(sqlAutoIncrement=false, sqlColumn="_country"))
  private String countryId;
  @SeifeField(isPrimaryKey=true, sqlOptions=@SqlFieldOptions(sqlAutoIncrement=false, sqlColumn="_language"))
  private String languageId;

  // ..
}

You can refer to the instance of it by adding the technical references  and define the foreign key like in the following example which also defines a combined index ‘locale’ for it.

@SeifeClass(generatorOptions={GeneratorOption.BOCLASS_PARCELABLE, GeneratorOption.SCHEMA_PEER, GeneratorOption.DB_HELPER+"=LocaleOpenHelper", 
    GeneratorOption.DATA_PROVIDER+"=LocaleProvider"})
public class LocaleData {

  private Language language;

  @SeifeField(
      foreignKey = @ForeignkeyDef(fkField="language", refKeyField="countryId", refClass=Language.class),
          sqlOptions=@SqlFieldOptions(sqlIndex="locale"))
  private String countryId;
  
  @SeifeField(
      foreignKey = @ForeignkeyDef(fkField="language", refKeyField="languageId", refClass=Language.class),
          sqlOptions=@SqlFieldOptions(sqlIndex="locale"))
  private String languageId;
  
  @Id
  @GeneratedValue(strategy=GenerationType.AUTO)
  @SeifeField(isPrimaryKey=true, sqlOptions=@SqlFieldOptions(sqlColumn="_id"))
  private int id;
  // ..
}

The generated table script in the LocaleOpenHelper will have the well defined foreign key reference and there will be an SQL index definition as well.

private static final String SQL_CREATE_TABLE_LOCALE_DATA =
    "create table " + TBL_LOCALE_DATA + " (" + 
        COL_ID + " integer primary key autoincrement," +
        COL_FK_LANGUAGE_COUNTRY_ID + " text," +
        COL_FK_LANGUAGE_LANGUAGE_ID + " text" +
        ", " +
        " FOREIGN KEY(" + COL_FK_LANGUAGE_COUNTRY_ID + "," + COL_FK_LANGUAGE_LANGUAGE_ID  + ")" +
        " REFERENCES " + LanguageSchema.TBL_LANGUAGE 
        +"(" + LanguageSchema.COL_COUNTRY_ID + "," + LanguageSchema.COL_LANGUAGE_ID  + ")" +			 		
        ")";

  /**
   * Index for locale
   */
  public static final String SQL_CREATE_IDX_LOCALE_DATA_LOCALE = 
      "create index if not exists "
          + TBL_LOCALE_DATA
          + "_idx_locale ON " + TBL_LOCALE_DATA
          + "(" + COL_FK_LANGUAGE_COUNTRY_ID + "," + COL_FK_LANGUAGE_LANGUAGE_ID + ")";

The system also uses the foreign key definitions to create SQL join correlations as a string constant, see e.g. a sample on GitHub.

Versioning

As is described in the section about database version handling the table index is also considered during upgrade, if the above foreign key was added in version 2

private Language language;

@SeifeField(
    foreignKey = @ForeignkeyDef(fkField="language", refKeyField="countryId", refClass=Language.class),
        sqlOptions=@SqlFieldOptions(sqlIndex="locale"), version=2)
private String countryId;

@SeifeField(
    foreignKey = @ForeignkeyDef(fkField="language", refKeyField="languageId", refClass=Language.class),
        sqlOptions=@SqlFieldOptions(sqlIndex="locale"), version=2)
private String languageId;

The code will also execute the SQL_CREATE_IDX_LOCALE_DATA_LOCALE script.

if (oldVersion < 2 && newVersion >= 2) { 
  db.execSQL("ALTER TABLE " + LocaleDataSchema.TBL_LOCALE_DATA + " ADD COLUMN " + LocaleDataSchema.COL_FK_LANGUAGE_COUNTRY_ID + " text");
  db.execSQL("ALTER TABLE " + LocaleDataSchema.TBL_LOCALE_DATA + " ADD COLUMN " + LocaleDataSchema.COL_FK_LANGUAGE_LANGUAGE_ID + " text");
  db.execSQL(LocaleDataSchema.SQL_CREATE_IDX_LOCALE_DATA_LOCALE);
}