Relationship Tables In Sqlite On Android

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

Solution 1:

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 ::

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

 CREATETABLE item(
        id INTEGER,
        name TEXT,
        description TEXT
 );

 CREATETABLEorder(
        id INTEGER,
        customerID INTEGER,
        date TEXT,
        FOREIGN KEY(customerId) REFERENCES customer(id)
 );

 CREATETABLE item_order(
        id INTEGER,
        orderID INTEGER,
        itemId  INTEGER,
        quantity INTEGER,
        FOREIGN KEY(orderId) REFERENCESorder(Id),
        FOREIGN KEY(itemId) REFERENCES item(Id)
 );

Solution 2:

Good sample http://www.androidhive.info/2013/09/android-sqlite-database-with-multiple-tables/

// Table Create Statements// Todo table create statementprivatestaticfinalStringCREATE_TABLE_TODO="CREATE TABLE "
        + TABLE_TODO + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_TODO
        + " TEXT," + KEY_STATUS + " INTEGER," + KEY_CREATED_AT
        + " DATETIME" + ")";

// Tag table create statementprivatestaticfinalStringCREATE_TABLE_TAG="CREATE TABLE " + TABLE_TAG
        + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_TAG_NAME + " TEXT,"
        + KEY_CREATED_AT + " DATETIME" + ")";

// todo_tag table create statementprivatestaticfinalStringCREATE_TABLE_TODO_TAG="CREATE TABLE "
        + TABLE_TODO_TAG + "(" + KEY_ID + " INTEGER PRIMARY KEY,"
        + KEY_TODO_ID + " INTEGER," + KEY_TAG_ID + " INTEGER,"
        + KEY_CREATED_AT + " DATETIME" + ")";

SELECT * FROM todos td, tags tg, todo_tags tt WHERE tg.tag_name = ‘Watchlist’ AND tg.id = tt.tag_id AND td.id = tt.todo_id;

/*
 * getting all todos under single tag
 * */public List<Todo> getAllToDosByTag(String tag_name) {
    List<Todo> todos = newArrayList<Todo>();

    StringselectQuery="SELECT  * FROM " + TABLE_TODO + " td, "
            + TABLE_TAG + " tg, " + TABLE_TODO_TAG + " tt WHERE tg."
            + KEY_TAG_NAME + " = '" + tag_name + "'" + " AND tg." + KEY_ID
            + " = " + "tt." + KEY_TAG_ID + " AND td." + KEY_ID + " = "
            + "tt." + KEY_TODO_ID;

    Log.e(LOG, selectQuery);

    SQLiteDatabasedb=this.getReadableDatabase();
    Cursorc= db.rawQuery(selectQuery, null);

    // looping through all rows and adding to listif (c.moveToFirst()) {
        do {
            Todotd=newTodo();
            td.setId(c.getInt((c.getColumnIndex(KEY_ID))));
            td.setNote((c.getString(c.getColumnIndex(KEY_TODO))));
            td.setCreatedAt(c.getString(c.getColumnIndex(KEY_CREATED_AT)));

            // adding to todo list
            todos.add(td);
        } while (c.moveToNext());
    }

    return todos;
}

Solution 3:

I guess you should read a book or web site on basic sql database programming. You create a relationship between two tables by adding the key (or a field) of one table into the other table. But really, you should get familiar with sql first. After you have done that, you can create relationships, or you'll find it convenient to use Ormlite.