Relations tables in sqlite on android

Question

Can you help me regarding the relationship between two tables in sqlite.

I insert, delete and update steps, but now I need to maintain the relationship between the two tables. I think all the code steps that are executed before this will be changed. I'm right? Do you have any link or example explaining table relationships and any actions after the relationship?

Answer 1

To establish a relationship between two tables, you can use Foreign keys . A foreign key is a field in a relational table that corresponds to the candidate key of another table.

For example, let's say that we have two tables, the CUSTOMER table, which includes all customer data, and the ORDER table, which includes all customer orders. It is assumed that all orders must be associated with a customer who is already in the CUSTOMER table. To do this, we put the foreign key in the ORDER table and bind it to the primary key of the CUSTOMER table.

In SQLite, External Key Constraints can be added as follows:

edit :: you can create the item_order table as ::

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) ); 

Answer 2

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

 // Table Create Statements // Todo table create statement private static final String CREATE_TABLE_TODO = "CREATE TABLE " + TABLE_TODO + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_TODO + " TEXT," + KEY_STATUS + " INTEGER," + KEY_CREATED_AT + " DATETIME" + ")"; // Tag table create statement private static final String CREATE_TABLE_TAG = "CREATE TABLE " + TABLE_TAG + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_TAG_NAME + " TEXT," + KEY_CREATED_AT + " DATETIME" + ")"; // todo_tag table create statement private static final String CREATE_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 = new ArrayList<Todo>(); String selectQuery = "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); SQLiteDatabase db = this.getReadableDatabase(); Cursor c = db.rawQuery(selectQuery, null); // looping through all rows and adding to list if (c.moveToFirst()) { do { Todo td = new Todo(); 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; } 

Answer 3

I think you should read a sql database programming book or website. You create a relationship between two tables by adding the key (or field) of one table to another table. But in fact, you should first get to know sql. After you have done this, you can create relationships, or it will be convenient for you to use Ormlite.