Question about inserting records for an entity that has one-to-many relationship with another entity in Room

Question

I am currently using Android Room for my app's DB, and I have an existing entity called Transaction, here is a shortened version of it:

@Entity(tableName = "transactions", indices = {@Index(value = "id", unique = true)})
public class Transaction {

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "id")
    private int txnId;

    private String type;
}

I need to add an object called Product to the Transaction object. Each Transaction object can have multiple Products. Based on what I've read, I defined my Product to have a foreign key that corresponds to the txnId of my Transaction object:

@Entity(tableName = "products",
    foreignKeys = @ForeignKey(entity = Transaction.class,
    parentColumns = "id",
    childColumns = "txn_id",
    onDelete = CASCADE))
public class Product {

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "id")
    private int productId;

    private String productCode;

    private int quantity;

    @ColumnInfo(name = "txn_id")
    private int txnId;
}

I figured that in order to get all the Products associated with a Transaction record, I made the following method in my ProductDao interface:

@Query("SELECT * FROM products WHERE txn_id LIKE :txn_id")
Observable<List<Product>> getProductsByTxnId(int txn_id);

Now here comes my question: if for example I insert a Transaction to the db, how would I add multiple Products for that Transaction? Do I need to grab a hold of the id for that particular Transaction, set it to the corresponding txn_id in my Product object before inserting it, or does Room automatically insert things correctly if you have setup the Product entity to have a foreignKey referencing the Transactionclass?

Answer 1

how would I add multiple Products for that Transaction?

You can use a mapping/associative/relationship (and other names) table (entity) to reflect a many-many relationship (i.e Many Transcations could be used(related to) a single Product and many Products could be used by a single Transaction).

That is a table with primarily 2 columns one for the value that relates (uniquely identifies) to one part (transaction) and the other column for the value that relates to the other (product).

So say for Transaction 1 which has Products 2,3,4,5 & 6, and Transaction 2 that has Products 1,3,5,7,9

The mapping table would contain :-

1 2
1 3
1 4
1 5
1 6
2 1
2 3
2 9
2 7
2 5

Do I need to grab a hold of the id for that particular Transaction, set it to the corresponding txn_id in my Product object before inserting it, or does Room automatically insert things correctly if you have setup the Product entity to have a foreignKey referencing the Transactionclass?

With regard to a SQLite Foreign key (Room or not), it is only defining a constraint (rule) that says that the value in the child column value must be a value in the parent column.

There is no magic performed that determines the values (if you think about it, how could it know what is related to what). You have to programaticaly determine the values when inserting.

So the answer is you need to grad the id's

Example of mapping and foreign key usage

Perhaps consider the following :-

DROP TABLE IF EXISTS Map1;
DROP TABLE IF EXISTS Map2;
DROP TABLE IF EXISTS Transactions;
DROP TABLE IF EXISTS Products;

CREATE TABLE IF NOT EXISTS Transactions (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE IF NOT EXISTS Products (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO Transactions (name) VALUES('Take'),('Give'),('Buy'),('Sell');
INSERT INTO Products(name) VALUES('Tea'),('Coffee'),('Water'),('Beer');
/* Mapping table without foreign keys */
CREATE TABLE IF NOT EXISTS Map1 (
    txn_id INTEGER,
    prd_id INTEGER, 
    UNIQUE(txn_id,prd_id) /* <- prevents duplication */
);
/* Mapping Table with foreign keys and cascading deletes and updates */
CREATE TABLE IF NOT EXISTS Map2 (
    txn_id INTEGER 
        REFERENCES Transactions(id) 
            ON DELETE CASCADE /* if parent is deleted then all children that map to that parent are deleted (only the maps rows)  */
            ON UPDATE CASCADE, /* like wise for updates (only if the id is updated) */
    prd_id INTEGER 
        REFERENCES Products(id) 
            ON DELETE CASCADE 
            ON UPDATE CASCADE, 
    UNIQUE(txn_id,prd_id)
);
INSERT INTO Map1 VALUES(1,1),(1,4),(2,3),(2,2),(3,1),(3,2),(3,3),(3,4),(4,2);
INSERT INTO Map2 VALUES(1,1),(1,4),(2,3),(2,2),(3,1),(3,2),(3,3),(3,4),(4,2);

/*Result 1 Mapped via Map1 table without foreign keys */
SELECT Transactions.name AS TransactionName, Products.name AS ProductName, 
    'Transaction '||txn_id||' maps to Product '||prd_id AS mapping
FROM Transactions 
JOIN Map1 ON Transactions.id = Map1.txn_id
JOIN Products ON Map1.prd_id = Products.id
;

/* Result 2 Mapped via Map2 table that has Foreign keys (no difference) to Result1 */
SELECT Transactions.name AS TransactionName, Products.name AS ProductName,
    'Transaction '||txn_id||' maps to Product '||prd_id AS mapping
FROM Transactions 
JOIN Map2 ON Transactions.id = Map2.txn_id
JOIN Products ON Map2.prd_id = Products.id
;

/* Add a rouge mapping entry to Map1 */
INSERT INTO Map1 VALUES (5,6); /* oooops niether transaction or Product exists */

/* Result 3 no issues with useless rouge entry */
SELECT Transactions.name AS TransactionName, Products.name AS ProductName, 
    'Transaction '||txn_id||' maps to Product '||prd_id AS mapping
FROM Transactions 
JOIN Map1 ON Transactions.id = Map1.txn_id
JOIN Products ON Map1.prd_id = Products.id
;

/* Try adding rouge entry to Map 2. Does not work */
INSERT INTO Map2 VALUES (5,6); /* oooops niether transaction or Product exists */
;

the code :-

• Drops the existing tables if they if they exist (note the order due to FK's)
• Creates the Transactions and Products tables.
• Adds some data to the Transactions and products tables.
• Creates the Map1 mapping table without using Foreign keys.
  • i.e. They are not required.
• Creates the Map2 mapping table using Foreign keys.
• Loads the mapping tables with the same data.
• Extracts data according to/using the mapping table Map1 producing result 1.
• Extracts data according to/usinf the mapping table Map2 producing result 2.
  • Identical to result 1.
• Inserts a rouge row t0 Map1, trying to map the non-existing Transaction with an id of 5 to the non-existing Product with an id of 6
• Extracts the data, as before, from Map1
  • identical i.e. the rouge (useless) row is ignored
• Insert a rouge row to Map2, which fails due to the Foreign Key constraint.

Results

With the message :-

INSERT INTO Map2 VALUES (5,6)
> FOREIGN KEY constraint failed
> Time: 0s