relationship tables in sqlite on android

android, database, sqlite

Solution

To establish relationship between two tables, you can use Foreign keys. A foreign key is a field in a relational table that matches a Candidate Key of another table.

For example, say we have two tables, a CUSTOMER table that includes all customer data, and an ORDER table that includes all customer orders. The intention here is that all orders must be associated with a customer that is already in the CUSTOMER table. To do this, we will place a foreign key in the ORDER table and have it relate to the primary key of the CUSTOMER table.

In SQLite Foreign Key Constraints can be added in following way ::

edit:: you can design item_order table like ::

CREATE TABLE customer(
         id INTEGER,
         firstName TEXT,
         middleName TEXT,
         lastName   TEXT,
         address TEXT,
         contactNum TEXT
);

 CREATE TABLE item(
        id INTEGER,
        name TEXT,
        description TEXT
 );

 CREATE TABLE order(
        id INTEGER,
        customerID INTEGER,
        date TEXT,
        FOREIGN KEY(customerId) REFERENCES customer(id)
 );

 CREATE TABLE item_order(
        id INTEGER,
        orderID INTEGER,
        itemId  INTEGER,
        quantity INTEGER,
        FOREIGN KEY(orderId) REFERENCES order(Id),
        FOREIGN KEY(itemId) REFERENCES item(Id)
 );

Problem

Can you help me about relationship between two tables in sqlite. I do insert,delete and update steps but I have to support relationship between two tables now. I guess all of code steps which are done before will be changed Am I right? Have you got any link or example which explains tables relationships and any activities after relationship?

Original source