android kotlin foreign-keys android-room

Android - What is ForeignKey in crossref table used to?

My app defines two entities CardEntity and TagEntity, these entities have a many-to-many relationship. I have implemented the following cross ref table to model the many-to-many relationship:

@Entity(
    primaryKeys = ["card_id", "tag_id"],
    foreignKeys = [
        ForeignKey(
            entity = CardEntity::class,
            parentColumns = ["card_id"],// id in CardEntity
            childColumns = ["card_id"],// id in TagCardCrossRefEntity
            onDelete = ForeignKey.CASCADE,
        ),
        ForeignKey(
            entity = TagEntity::class,
            parentColumns = ["tag_id"],// id in TagEntity
            childColumns = ["tag_id"],// id in TagCardCrossRefEntity
            onDelete = ForeignKey.CASCADE,
        )
    ]
)
data class TagCardCrossRefEntity(
    @ColumnInfo(name = "card_id")
    val cardId: Long,
    @ColumnInfo(name = "tag_id")
    val tagId: Long
)

I have alse implemented all the other required methods (as explain here). All my tests pass successfully. I can insert, retrieve and delete cards with tags.

I am not sure what is the need of the ForeignKey in the implementation of the cross ref entity. I thought that one of the reason to add ForeignKey was to define the onDelete strategy, but it appears that if I delete the declaration of the foreignkey in the cross ref entity, and delete a CardEntity in the card table then the relationship for this card is also deleted.

So why would I need to define foreignKeys in a cross ref table ?

Solution

ForeignKey adds a constraint(rule) to enforce referential integrity. The rule is that the value of the column in the child that references the parent MUST be an existing value in the column that is referenced in one of the rows of the parent table. In simple terms orphans are not allowed.

see https://database.guide/what-is-referential-integrity/ (or do a serach using something like what is referential integrity)
It should be noted that a foreign key conflict will trigger an exception irrespective of OR ... (IGNORE, REPLACE ....).
- The documentation says
  - The ON CONFLICT clause applies to UNIQUE, NOT NULL, CHECK, and PRIMARY KEY constraints. The ON CONFLICT algorithm does not apply to FOREIGN KEY constraints. There are five conflict resolution algorithm choices: ROLLBACK, ABORT, FAIL, IGNORE, and REPLACE. The default conflict resolution algorithm is ABORT. This is what they mean: See https://www.sqlite.org/lang_conflict.html

In addition to the rule ACTIONS that help to maintain referential integrity can be set when a violation (conflict) would occur when deleting or updating the referenced column in a parent. The most common/useful ACTION is to CASCADE, hence the onDelete and onUpdate parameters.

You may well wish to refer to https://www.sqlite.org/foreignkeys.html

Demo

Rather than use Room the topic is very much how SQLite works. Room is a wrapper around SQLite and basically the annotations result in generating SQL.

e.g. look in the generated java for the @Database annotated class suffixed with _Impl and find the createAllTables method (function in Kotlin terms) and you will see the SQL for creating the tables.

The demo uses SQLite SQL and can be dropped into an SQLite tool, Navicat for SQLite was used:-

/* Make sure that the demo environment is tidy*/
DROP TABLE IF EXISTS a_xref_b;
DROP TABLE IF EXISTS a_xref_b_NO_RI;
DROP TABLE IF EXISTS tablea;
DROP TABLE IF EXISTS tableb;

/* Create the core tables a and b */
CREATE TABLE IF NOT EXISTS tablea (id INTEGER PRIMARY KEY /*<<<< PARENT */, a_value TEXT /* etc */); 
CREATE TABLE IF NOT EXISTS tableb (id INTEGER PRIMARY KEY /*<<<< PARENT */, b_value TEXT /* etc */); 

/* Create the cross reference table WITHOUT foreign key constraints */
CREATE TABLE IF NOT EXISTS a_xref_b_NO_RI (a_ref INTEGER, b_ref INTEGER, PRIMARY KEY (a_ref,b_ref));

/* Load some data into the core tables */
INSERT INTO tablea VALUES (1,'Aa'),(2,'Ba'),(3,'Ca'),(4,'Da');
INSERT INTO tableb VALUES (10,'Ab'),(11,'Bb'),(12,'Cb'),(13,'Db');
/* add some cross references including a row where referential integrity does not exist (9999,7777) */
INSERT INTO a_xref_b_NO_RI VALUES (1,11),(1,13),(3,10),(3,12),(9999,7777)/*<<<< CHILDREN THAT DONT EXIST */;

/* RESULT 1 (not a little complicated to show ALL cross references)*/
SELECT ALL 
    *, 
    (SELECT group_concat(a_value)||' : '||group_concat(id) FROM tablea WHERE tablea.id = a_ref) AS from_a,
    (SELECT group_concat(b_value)||' : '||group_concat(id) FROM tableb WHERE tableb.id = b_ref) AS from_b
FROM a_xref_b_NO_RI;
/* DELETE some rows from b*/
DELETE FROM tableb WHERE id > 11 AND id < 1000;
/* RESULT 2 (now what happens) */
SELECT ALL 
    *, 
    (SELECT group_concat(a_value)||' : '||group_concat(id) FROM tablea WHERE tablea.id = a_ref) AS from_a,
    (SELECT group_concat(b_value)||' : '||group_concat(id) FROM tableb WHERE tableb.id = b_ref) AS from_b
FROM a_xref_b_NO_RI;

/*========= FOREIGN KEYS ==========*/
/* Empty table b* */
DELETE FROM tableb;
/* Create the xref table WITH FK constraints and actions */
CREATE TABLE IF NOT EXISTS a_xref_b (
    a_ref INTEGER REFERENCES tablea(id) ON DELETE CASCADE ON UPDATE CASCADE , 
    b_ref INTEGER REFERENCES tableb(id) ON DELETE CASCADE ON UPDATE CASCADE, 
    PRIMARY KEY (a_ref,b_ref)
);
/* Re load table b */
INSERT INTO tableb VALUES (10,'Ab'),(11,'Bb'),(12,'Cb'),(13,'Db');
/* Add the xref rows (NOTE not 9999,7777 as an exception occurr) */
INSERT INTO a_xref_b VALUES (1,11),(1,13),(3,10),(3,12);
/* RESULT 3 (equiv of RESULT 1) */
SELECT ALL 
    *, 
    (SELECT group_concat(a_value)||' : '||group_concat(id) FROM tablea WHERE tablea.id = a_ref) AS from_a,
    (SELECT group_concat(b_value)||' : '||group_concat(id) FROM tableb WHERE tableb.id = b_ref) AS from_b
FROM a_xref_b;
/* do the same delete as above */
DELETE FROM tableb WHERE id > 11 AND id < 1000;
/* update some referenced columns */
UPDATE tablea SET id = id * 10;
/* RESULT 4 (equiv of RESULT 3) */
SELECT ALL 
    *, 
    (SELECT group_concat(a_value)||' : '||group_concat(id) FROM tablea WHERE tablea.id = a_ref) AS from_a,
    (SELECT group_concat(b_value)||' : '||group_concat(id) FROM tableb WHERE tableb.id = b_ref) AS from_b
FROM a_xref_b;
/* TRY TO INSERT orphans */
INSERT OR IGNORE INTO a_xref_b VALUES(9999,7777);
/* CLEAN UP DEMO ENVIRONMENT */
DROP TABLE IF EXISTS a_xref_b;
DROP TABLE IF EXISTS a_xref_b_NO_RI;
DROP TABLE IF EXISTS tablea;
DROP TABLE IF EXISTS tableb;

refer to the comments

RESULTS (output of the 4 SELECTs):-

RESULT 1
- the highlights showing where RI does not exist
- i.e. 9999 and 7777 are orphans (children without a parent)
RESULT 2
- even more orphans due to the deleteion of rows from tableb
RESULT 3 (using FKEYS orphans not allowed)
- no orphans (note that inserting 9999,7777 would fail (see below))
RESULT 4 (after equivalent deletions and update to change referenced parent values for tablea)
as can be seen the changes (UPDATEs) to the id in tablea (multiplied by 10) have been CASCADEd to the children, the a_ref column in the a_xref_b table, so the relationship remains intact.
ATTEMPT TO ADD ORPHANS
Message (fail) =

/* TRY TO INSERT orphans */
INSERT OR IGNORE INTO a_xref_b VALUES(9999,7777)
> FOREIGN KEY constraint failed
> Time: 0s

Note that the clean up does not run due to the failure