How do I set up a Foreign Key

Question

What I have setup are two tables, one for a user created account, and the other that lets the user buy a product.

I have both tables set up like so

Customer Table

@PrimaryKey(autoGenerate = true)
    private int custId;

    @ColumnInfo(name = "user_name")
    private String userName;

    @ColumnInfo(name = "password")
    private String password;

    @ColumnInfo(name = "first_name")
    private String firstName;

    @ColumnInfo(name = "last_name")
    private String lastName;

    @ColumnInfo(name = "address")
    private String address;

    @ColumnInfo(name = "city")
    private String city;

    @ColumnInfo(name = "postal_code")
    private String postalCode;

    @ColumnInfo(name = "country")
    private String country;

Phone Table

    @PrimaryKey(autoGenerate = true)
    private int productId;
    private String phoneMake;
    private String phoneModel;
    private String phoneColor;
    private String storageCapacity;
    private Float  price;

What I have set up are two foreign keys, one in each table. My last table is for ordering the phones, which requires using both Primary Keys from each table. What I feel like I need is a ForeignKey, similar in vein to the PrimaryKey already created. The problem is that I am unsure how to implement that into the program. Everything I try doing is not working. I have looked at the documentation, but nothing clicks. I hope you can help me with the correct screenshot. If more is needed let me know (This code is written in Java code)

Answer 1

If you simply want a Customer to have 1 phone, then you have have a single column (member variable) for the relationship that will store the phone's product id.

e.g.

private int mapToPhone; //<<<<< ADDED no need for @ColumnInfo the column name will be as per the variable name.

Obviously you set the value to an appropriate value.

To then get the Customer with the phone's details then you have a POJO that embeds the parent (Customer) using the @Embedded annotation has the child (Phone) using the @Relation annotation.

e.g. :-

class CustomerWithPhoneDetails {
   @Embedded
   Customer customer;
   @Relation(
           entity = Phone.class,
           parentColumn = "mapToPhone",
           entityColumn = "productId"
   )
   Phone phoneDetails;
}

You can then have a method in the @Dao annotated interface/abstract class which queries the parent table BUT returns the POJO or list/array of the POJO e.g. :-

@Query("SELECT * FROM Customer")
abstract List<CustomerWithPhoneDetails> getAllCustomersWithPhoneDeytails();

Example

Based upon your code, and the additional example code along with an @Database annotated abstract class :-

@Database(entities = {Customer.class,Phone.class}, version = 1, exportSchema = false)
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,"the_database.db")
                    .allowMainThreadQueries()
                    .build();
        }
        return instance;
    }
}

and an Activity e.g. :-

public class MainActivity extends AppCompatActivity {

    TheDatabase db;
    AllDao dao;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        db = TheDatabase.getInstance(this);
        dao = db.getAllDao();

        long phone01ProductId = dao.insert(new Phone("PhoneMaker001","Model001","Color001","100Mb",111.11F));
        long phone02ProductId = dao.insert(new Phone("PhoneMaker002","Model002","Color002","200Mb",222.22F));

        dao.insert(new Customer("c001","password001","firstname001","lastname001","address001","city001","country001","postcode001",(int) phone01ProductId));
        dao.insert(new Customer("c002","password002","firstname002","lastname002","address002","city002","country002","postcode002",(int) phone02ProductId));

        for(CustomerWithPhoneDetails cwpd: dao.getAllCustomersWithPhoneDeytails()) {
            Log.d("DBINFO","Customer is " + cwpd.customer.getUserName() + " etc.  Phone is " + cwpd.phoneDetails.getProductId() + " etc." );
        }
    }
}

• Note that suitable constructors have been coded in both the Phone and Customer class (default/empty constructor and one, annotated with @Ignore annotation that allows all values bar the id to be passed as used in the example below)

• Note that ideally long rather than int should be used for the id columns.

Results

The Log :-

D/DBINFO: Customer is c001 etc.  Phone is 1 etc.
D/DBINFO: Customer is c002 etc.  Phone is 2 etc.

App Inspection :-

and :-