Search code examples
androidforeign-keysandroid-room

How do I setup Android Room Foreign Keys in a Join table accept "zero" values?

Problem: With Android Room, I'm receiving an foreign key constraint error on a join table when entering zero (0). I'm still trying to learn Android Room so I'm sure I have overlooked something in the documentation and tutorials.

What I have tried: I do have this application in a desktop version using SQLite and I have no issue and have tried to set it up the same. I've read some posts here and elsewhere with examples, and even removed the foreign key declarations that would return zero, but then I had difficulty with the schema starting to trigger on a different table altogether. I tried default values but and setting up for null but can't seem to get it to work. I will still be re-reading some at the time of this post but I'm sure I'm just overlooking some thing - the old 80/20 rule.

What I'm trying to do: This join table captures ids from 6 tables. However, based on the user entries, there are 3 foreign keys which it is possible the user did not add anything to be referenced. Since Android Room is in essence wrapping the SQLite for ease of use, so far, I haven't been able to determine how to get it to accept zero values on foreign keys. I thought about try an OnConflict annotation but wanted to reach out for some thoughts and suggestions.

ENTITY

@Entity(tableName = "Notes", foreignKeys = {
        @ForeignKey(entity = Sources.class, parentColumns = "SourceID", childColumns = "SourceID"),
        @ForeignKey(entity = Comments.class, parentColumns = "CommentID", childColumns = "CommentID"),
        @ForeignKey(entity = Questions.class, parentColumns = "QuestionID", childColumns = "QuestionID"),
        @ForeignKey(entity = Quotes.class, parentColumns = "QuoteID", childColumns = "QuoteID"),
        @ForeignKey(entity = Terms.class, parentColumns = "TermID", childColumns = "TermID"),
        @ForeignKey(entity = Topics.class, parentColumns = "TopicID", childColumns = "TopicID")},
        indices = {@Index("SourceID"), @Index("CommentID"), @Index("QuestionID"), @Index("QuoteID"),
                @Index("TermID"), @Index("TopicID")})
public class Notes {

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "NoteID")
    private int noteID;
    @ColumnInfo(name = "SourceID")
    private int sourceID;
    @ColumnInfo(name = "CommentID")
    private int commentID;
    @ColumnInfo(name = "QuestionID", defaultValue = "0")
    private int questionID;
    @ColumnInfo(name = "QuoteID", defaultValue = "0")
    private int quoteID;
    @ColumnInfo(name = "TermID", defaultValue = "0")
    private int termID;
    @ColumnInfo(name = "TopicID")
    private int topicID;
    @ColumnInfo(name = "Deleted", defaultValue = "0")
    private int deleted;

public Notes(int noteID, int sourceID, int commentID, int questionID, int quoteID, int termID, int topicID, int deleted){
    this.noteID = noteID;
    this.sourceID = sourceID;
    this.commentID = commentID;
    this.questionID = questionID;
    this.quoteID = quoteID;
    this.termID = termID;
    this.topicID = topicID;
    this.deleted = deleted;
}

SCHEMA PORTION

    "tableName": "Notes",
    "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`NoteID` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `SourceID` INTEGER NOT NULL, `CommentID` INTEGER NOT NULL, `QuestionID` INTEGER NOT NULL DEFAULT 0, `QuoteID` INTEGER NOT NULL DEFAULT 0, `TermID` INTEGER NOT NULL DEFAULT 0, `TopicID` INTEGER NOT NULL, `Deleted` INTEGER NOT NULL DEFAULT 0, FOREIGN KEY(`SourceID`) REFERENCES `Sources`(`SourceID`) ON UPDATE NO ACTION ON DELETE NO ACTION , FOREIGN KEY(`CommentID`) REFERENCES `Comments`(`CommentID`) ON UPDATE NO ACTION ON DELETE NO ACTION , FOREIGN KEY(`QuestionID`) REFERENCES `Questions`(`QuestionID`) ON UPDATE NO ACTION ON DELETE NO ACTION , FOREIGN KEY(`QuoteID`) REFERENCES `Quotes`(`QuoteID`) ON UPDATE NO ACTION ON DELETE NO ACTION , FOREIGN KEY(`TermID`) REFERENCES `Terms`(`TermID`) ON UPDATE NO ACTION ON DELETE NO ACTION , FOREIGN KEY(`TopicID`) REFERENCES `Topics`(`TopicID`) ON UPDATE NO ACTION ON DELETE NO ACTION )",
    "fields": [
      {
        "fieldPath": "noteID",
        "columnName": "NoteID",
        "affinity": "INTEGER",
        "notNull": true
      },
      {
        "fieldPath": "sourceID",
        "columnName": "SourceID",
        "affinity": "INTEGER",
        "notNull": true
      },
      {
        "fieldPath": "commentID",
        "columnName": "CommentID",
        "affinity": "INTEGER",
        "notNull": true
      },
      {
        "fieldPath": "questionID",
        "columnName": "QuestionID",
        "affinity": "INTEGER",
        "notNull": true,
        "defaultValue": "0"
      },
      {
        "fieldPath": "quoteID",
        "columnName": "QuoteID",
        "affinity": "INTEGER",
        "notNull": true,
        "defaultValue": "0"
      },
      {
        "fieldPath": "termID",
        "columnName": "TermID",
        "affinity": "INTEGER",
        "notNull": true,
        "defaultValue": "0"
      },
      {
        "fieldPath": "topicID",
        "columnName": "TopicID",
        "affinity": "INTEGER",
        "notNull": true
      },
      {
        "fieldPath": "deleted",
        "columnName": "Deleted",
        "affinity": "INTEGER",
        "notNull": true,
        "defaultValue": "0"
      }
    ],
    "primaryKey": {
      "columnNames": [
        "NoteID"
      ],
      "autoGenerate": true
    },
    "indices": [
      {
        "name": "index_Notes_SourceID",
        "unique": false,
        "columnNames": [
          "SourceID"
        ],
        "createSql": "CREATE INDEX IF NOT EXISTS `index_Notes_SourceID` ON `${TABLE_NAME}` (`SourceID`)"
      },
      {
        "name": "index_Notes_CommentID",
        "unique": false,
        "columnNames": [
          "CommentID"
        ],
        "createSql": "CREATE INDEX IF NOT EXISTS `index_Notes_CommentID` ON `${TABLE_NAME}` (`CommentID`)"
      },
      {
        "name": "index_Notes_QuestionID",
        "unique": false,
        "columnNames": [
          "QuestionID"
        ],
        "createSql": "CREATE INDEX IF NOT EXISTS `index_Notes_QuestionID` ON `${TABLE_NAME}` (`QuestionID`)"
      },
      {
        "name": "index_Notes_QuoteID",
        "unique": false,
        "columnNames": [
          "QuoteID"
        ],
        "createSql": "CREATE INDEX IF NOT EXISTS `index_Notes_QuoteID` ON `${TABLE_NAME}` (`QuoteID`)"
      },
      {
        "name": "index_Notes_TermID",
        "unique": false,
        "columnNames": [
          "TermID"
        ],
        "createSql": "CREATE INDEX IF NOT EXISTS `index_Notes_TermID` ON `${TABLE_NAME}` (`TermID`)"
      },
      {
        "name": "index_Notes_TopicID",
        "unique": false,
        "columnNames": [
          "TopicID"
        ],
        "createSql": "CREATE INDEX IF NOT EXISTS `index_Notes_TopicID` ON `${TABLE_NAME}` (`TopicID`)"
      }
    ],
    "foreignKeys": [
      {
        "table": "Sources",
        "onDelete": "NO ACTION",
        "onUpdate": "NO ACTION",
        "columns": [
          "SourceID"
        ],
        "referencedColumns": [
          "SourceID"
        ]
      },
      {
        "table": "Comments",
        "onDelete": "NO ACTION",
        "onUpdate": "NO ACTION",
        "columns": [
          "CommentID"
        ],
        "referencedColumns": [
          "CommentID"
        ]
      },
      {
        "table": "Questions",
        "onDelete": "NO ACTION",
        "onUpdate": "NO ACTION",
        "columns": [
          "QuestionID"
        ],
        "referencedColumns": [
          "QuestionID"
        ]
      },
      {
        "table": "Quotes",
        "onDelete": "NO ACTION",
        "onUpdate": "NO ACTION",
        "columns": [
          "QuoteID"
        ],
        "referencedColumns": [
          "QuoteID"
        ]
      },
      {
        "table": "Terms",
        "onDelete": "NO ACTION",
        "onUpdate": "NO ACTION",
        "columns": [
          "TermID"
        ],
        "referencedColumns": [
          "TermID"
        ]
      },
      {
        "table": "Topics",
        "onDelete": "NO ACTION",
        "onUpdate": "NO ACTION",
        "columns": [
          "TopicID"
        ],
        "referencedColumns": [
          "TopicID"
        ]
      }
    ]
  },
Solution

  • @ForeginKey is annotation that adds the SQLite FOREIGN KEY clause. The SQLite FOREIGN KEY clause is a rule (constraint) that says that the value of the column MUST be an existing value in the table/column(s) specified. If there is no 0 value in the related table then the row cannot be inserted but instead a conflict occurs.

    FOREIGN KEY isn't required to form a relationship rather it is used to ensure referential integrity, that is that there are no orphaned rows.

    If you MUST have 0 as an indication that there are no related items (not really required), then you have two options.

    • by not really required see the example/demo, the output includes 0 relations.

    The options are:-

    1. Do not use foreign keys and hence no rule exists preventing 0 (or null being used), or
    2. alternately have a row in the related table that has 0 which is treated accordingly.

    I thought about try an OnConflict annotation but wanted to reach out for some thoughts and suggestions.

    Using @OnConflict does not apply to Foreign Key conflicts but to conflicts caused by unique, not null, check and primary key constraints being violated.

    I tried default values but and setting up for null but can't seem to get it to work.

    Furthermore using @ColumnInfo's defaultValue does not, using an @Insert, insert the default value as a value is always provided for all the columns (bar the primary key under some conditions). To have the defaultValue applied you need to use an @Query that uses the INSERT INTO the_table (csv_list_of_columns) VALUES(csv_list_of_values_for_each_column) where the column that is to be defaulted is NOT in the list of columns and thus there is no corresponding value (so the default will be used).

    Working example/demo (just the one relationship for brevity Note > Source)

    The example uses two solutions the first (entities NotesV1/SourceV1) without using Foreign Keys, the second with (NotesV2/SourceV2). Both solutions allow null's, the latter taking advantage of the null handling of Foreign Keys in SQLite as per :-

    There is one exception: if the foreign key column in the track table is NULL, then no corresponding entry in the artist table is required. https://sqlite.org/foreignkeys.html

    It should also be noted the use of objects rather than primitives so Integer instead of int. However, I have used Long/long due to id's being up to 64bit signed. Using Long/Integer allows null, the primitives cannot be null.

    I'd also added a name column to allow a descriptive to be presented.

    So the two sets of Entities :-

    NotesV1 :-

    @Entity(
        indices = {
                @Index("SourceIDMap")
        }
)
class NotesV1 {
    @PrimaryKey
    @ColumnInfo(name = "NoteID")
    Long noteId = null;
    @ColumnInfo(name = "SourceIDMap",defaultValue = "0")
    Long sourceId = null;
    @ColumnInfo(name = "SourceName",defaultValue = "Not Given")
    String noteName;

    NotesV1(){}
    @Ignore
    NotesV1(Long noteId, Long sourceId, String noteName) {
        this.noteId = noteId;
        this.sourceId = sourceId;
        this.noteName = noteName;
    }
    @Ignore
    NotesV1(long sourceId, String noteName) {
        this(null,sourceId,noteName);
    }
    @Ignore
    NotesV1(String noteName) {
        this(null,null,noteName);
    }
}
    • the ignore'd constructors offer more flexibility

    NotesV2 (with Foreign keys) :-

    @Entity(
        foreignKeys = {
                @ForeignKey(entity = SourceV2.class,parentColumns = {"SourceID"}, childColumns = {"SourceIDMap"})
        },
        indices = {
                @Index("SourceIDMap")
        }
)
class NotesV2 {
    @PrimaryKey
    @ColumnInfo(name = "NoteID")
    Long noteId = null;
    // suggest to always have unique column names so SourceIDMap will map to SourceID
    @ColumnInfo(name = "SourceIDMap",defaultValue = "0")
    Long sourceId = null;
    @ColumnInfo(name = "SourceName",defaultValue = "Not Given")
    String noteName;
}
    • haven't added additional ignored constructors

    SourceV1

    @Entity
class SourceV1 {
    @PrimaryKey
    @ColumnInfo(name = "SourceID")
    Long sourceId = null;
    String sourceName;
}

    SourceV2 (bar the class name identical to SourceV1) :-

    @Entity
class SourceV2 {
    @PrimaryKey
    @ColumnInfo(name = "SourceID")
    Long sourceId = null;
    String sourceName;
}

    To show results via the relationships two POJOS that cater for the Join (@Relation) between Notes and Source :-

    NotesV1WithSources

    class NotesV1WithSources {
    @Embedded
    NotesV1 notesV1;
    @Relation(
            entity = SourceV1.class,parentColumn = "SourceIDMap",entityColumn = "SourceID"
    )
    List<SourceV1> sourceV1List;
}

    and the basically identical NotesV2WithSources :-

    class NotesV2WithSources {
    @Embedded
    NotesV2 notesV2;
    @Relation(
            entity = SourceV2.class,parentColumn = "SourceIDMap",entityColumn = "SourceID"
    )
    List<SourceV2> sourceV2List;
}

    A single @DAO class AllDao (note includes queries for inserts using defaultValue just for NotesV1) :-

    @Dao
abstract class AllDao {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract long insert(NotesV1 notesV1);
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract long insert(NotesV2 notesV2);
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract long insert(SourceV1 sourceV1);
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    abstract long insert(SourceV2 sourceV2);

    /* Using @ColumnInfo's defaultValue examples */
    @Query("INSERT INTO notesV1 (SourceIdMap) VALUES(:sourceId)")
    abstract void insertNotesV1OnlyBySourceId(long sourceId);
    @Query("INSERT INTO notesV1 (SourceName) VALUES(:sourceName)")
    abstract void insertNotesV1OnlyBySourceName(String sourceName);

    @Transaction
    @Query("SELECT * FROM notesv1")
    abstract List<NotesV1WithSources> getAllNotesV1WithSources();
    @Transaction
    @Query("SELECT * FROM notesv2")
    abstract List<NotesV2WithSources> getAllNotesV2WithSources();
}

    A pretty standard/simple @Database class TheDatabase :-

    @Database(entities = {NotesV1.class,NotesV2.class,SourceV1.class,SourceV2.class},version = 1)
abstract class TheDatabase extends RoomDatabase {
    abstract AllDao getAllDao();

    private static volatile TheDatabase instance = null;

    public static TheDatabase getInstance(Context context) {
        if (instance == null) {
            instance = Room.databaseBuilder(context,TheDatabase.class,"notes.db")
                    .allowMainThreadQueries()
                    .build();
        }
        return instance;
    }
}

    Finally an Activity that uses/demonstrates the above, MainActivity :-

    public class MainActivity extends AppCompatActivity {

    TheDatabase db;
    AllDao dao;
    private static final String TAGV1 = "NOTESV1INFO";
    private static final String TAGV2 = "NOTESV2INFO";

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        db = TheDatabase.getInstance(this);
        dao = db.getAllDao();

        /* Without Foreign keys  and shows defaultValue */
        SourceV1 s1 = new SourceV1();
        s1.sourceName = "Source 1";
        s1.sourceId = dao.insert(s1);
        dao.insert(new NotesV1()); // All defaults
        dao.insert(new NotesV1()); /// and again
        dao.insert(new NotesV1("Just the Note Name")); // Via @Ignore'd constructor
        dao.insert(new NotesV1(1000L,s1.sourceId,"All provided")); // Via @Ignore'd constrcutor
        dao.insertNotesV1OnlyBySourceId(0); // Via @Query with 0 passed as SourceId (so name is default)
        dao.insertNotesV1OnlyBySourceName("Again just the note name"); //Via @Query with name passed (so SourceId is 0)

        for(NotesV1WithSources nv1: dao.getAllNotesV1WithSources()) {
            Log.d(TAGV1,
                    "Note ID is " + nv1.notesV1.noteId +
                            " Name is " + nv1.notesV1.noteName +
                            " SourceID is " + nv1.notesV1.sourceId +
                            "\nSources(" + nv1.sourceV1List.size() + ") Are:-");
            for (SourceV1 s: nv1.sourceV1List) {
                Log.d(TAGV1,"\tSource ID is " + s.sourceId + " Source Name is " + s.sourceName );
            }
        }

        /* With Foreign Keys */
        // SPECIAL ROW 0  INDICATIVE OF NO RELATION
        SourceV2 s2 = new SourceV2();
        s2.sourceId = 0L;
        s2.sourceName = "I SHOULD BE IGNORED";
        Log.d(TAGV2,"1. Special Source row Inserted with ID = " + dao.insert(s2));
        Log.d(TAGV2,"2. NotesV2 insert ID was " + dao.insert(new NotesV2()) + " (note -1 then not inserted)");
        NotesV2 n2 = new NotesV2();
        n2.sourceId = 0L;
        n2.noteName ="references special source";
        Log.d(TAGV2,"3. NotesV2 insert ID was " + dao.insert(n2) + " (note -1 then not inserted)");
        /*
        //OUCH NO such Source2  so Foreign Key conflict NOT trapped by IGNORE so abends
        e.g. android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY)
        n2.sourceId = 100L;
        Log.d(TAGV2,"4. NotesV2 insert ID was " + dao.insert(n2) + " (note -1 then not inserted)");
         */
        s2 = new SourceV2();
        s2.sourceName = "I am a normal source";
        long s2Id = -99;
        Log.d(TAGV2,"5. Normal Source row Inserted with ID = " + (s2Id = dao.insert(s2)));
        n2.sourceId = s2Id;

        for(NotesV2WithSources nv2: dao.getAllNotesV2WithSources()) {
            Log.d(TAGV2,
                    "Note ID is " + nv2.notesV2.noteId +
                            " Name is " + nv2.notesV2.noteName +
                            " SourceID is " + nv2.notesV2.sourceId +
                            "\nSources(" + nv2.sourceV2List.size() + ") Are:-");
            for (SourceV2 s: nv2.sourceV2List) {
                Log.d(TAGV2,"\tSource ID is " + s.sourceId + " Source Name is " + s.sourceName );
            }
        }
    }
}
    • Note the above is designed to be run once only.
    • run on the main thread for brevity
    • first half uses V1's second half uses V2's
    • note the commented out insert that would crash as IGNORE does not ignore Foreign Key constraint conflict.
      • comment includes extract from such a crash

    When run the output is :-

    D/NOTESV1INFO: Note ID is 1 Name is null SourceID is null
    Sources(0) Are:-
D/NOTESV1INFO: Note ID is 2 Name is null SourceID is null
    Sources(0) Are:-
D/NOTESV1INFO: Note ID is 3 Name is Just the Note Name SourceID is null
    Sources(0) Are:-
D/NOTESV1INFO: Note ID is 1000 Name is All provided SourceID is 1
    Sources(1) Are:-
D/NOTESV1INFO:  Source ID is 1 Source Name is Source 1
D/NOTESV1INFO: Note ID is 1001 Name is Not Given SourceID is 0
    Sources(0) Are:-
D/NOTESV1INFO: Note ID is 1002 Name is Again just the note name SourceID is 0
    Sources(0) Are:-
D/NOTESV2INFO: 1. Special Source row Inserted with ID = 0
D/NOTESV2INFO: 2. NotesV2 insert ID was 1 (note -1 then not inserted)
D/NOTESV2INFO: 3. NotesV2 insert ID was 2 (note -1 then not inserted)
D/NOTESV2INFO: 5. Normal Source row Inserted with ID = 1
D/NOTESV2INFO: Note ID is 1 Name is null SourceID is null
    Sources(0) Are:-
D/NOTESV2INFO: Note ID is 2 Name is references special source SourceID is 0
    Sources(1) Are:-
D/NOTESV2INFO:  Source ID is 0 Source Name is I SHOULD BE IGNORED
    • As can be seen Foreign Keys are not required for relationships to exist (i.e. V1 doesn't have FK's defined at all).
    • As can also be seen nulls exist and don't cause any issues.

    Using Application Inspector (previously Database Inspector) Then :-

    enter image description here

    enter image description here

    enter image description here

    enter image description here