Android [advanced tutorial] setting foreign keys in the SQLite table of the android Database

Introduction:

The default data for Android is SQLite, but before sqlite3.6.19, foreign keys were not supported. If two tables need to be joined, using foreign keys is the most convenient, but not supported. What should I do? Here is a solution, that is, to associate two tables with transactions and generate a view at the end.

Two existing tables

1. Employees
2. Dept

View

1. ViewEmps: Display employee information and his department

Create a database

Customize a helper class to inherit from the sqliteopenhelper class

1.onCreate(SQLiteDatabase db): When a database is created, tables can be generated and views and triggers can be created. 2.onUpgrade(SQLiteDatabse db, int oldVersion, int newVersion): You can delete tables and create new tables during update. The Code is as follows:

public class DatabaseHelper extends SQLiteOpenHelper {static final String dbName="demoDB";static final String employeeTable="Employees";static final String colID="EmployeeID";static final String colName="EmployeeName";static final String colAge="Age";static final String colDept="Dept";static final String deptTable="Dept";static final String colDeptID="DeptID";static final String colDeptName="DeptName";static final String viewEmps="ViewEmps";

Constructor

public DatabaseHelper(Context context) {  super(context, dbName, null,33);   }

Create tables, views, and triggers in the database

Public void oncreate (sqlitedatabase dB) {// todo auto-generated method stub db.exe csql ("create table" + depttable + "(" + coldeptid + "integer primary key, "+ coldeptname +" text) "); db.exe csql (" create table "+ employeetable +" ("+ colid +" integer primary key autoincrement, "+ colname +" text, "+ colage +" integer, "+ coldept +" integer not null, foreign key ("+ coldept +") References "+ depttable +" ("+ coldeptid + ")); "); // create the trigger db.exe csql ("create trigger fk_empdept_deptid" + "before Insert" + "on" + employeetable + "for each row begin" + "select case when (select" + coldeptid + "from" + depttable + "where" + coldeptid + "= new. "+ coldept +") is null) "+" then raise (abort, 'foreign key violation ') end; "+" end ;"); // create a view db.exe csql ("create View" + viewemps + "as select" + employeetable + ". "+ colid +" AS _ id, "+" "+ employeetable + ". "+ colname +", "+" "+ employeetable + ". "+ colage +", "+" "+ depttable + ". "+ coldeptname +" "+" from "+ employeetable +" join "+ depttable +" on "+ employeetable + ". "+ coldept +" = "+ depttable + ". "+ coldeptid );}

Update tables in the database

public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {  // TODO Auto-generated method stub    db.execSQL("DROP TABLE IF EXISTS "+employeeTable);  db.execSQL("DROP TABLE IF EXISTS "+deptTable);  db.execSQL("DROP TRIGGER IF EXISTS fk_empdept_deptid");  db.execSQL("DROP VIEW IF EXISTS "+viewEmps);  onCreate(db); }

Add data

SQLiteDatabase db=this.getWritableDatabase(); ContentValues cv=new ContentValues();   cv.put(colDeptID, 1);   cv.put(colDeptName, "Sales");   db.insert(deptTable, colDeptID, cv);   cv.put(colDeptID, 2);   cv.put(colDeptName, "IT");   db.insert(deptTable, colDeptID, cv);                     db.close();

Update Data

public int UpdateEmp(Employee emp)  {   SQLiteDatabase db=this.getWritableDatabase();   ContentValues cv=new ContentValues();   cv.put(colName, emp.getName());   cv.put(colAge, emp.getAge());   cv.put(colDept, emp.getDept());   return db.update(employeeTable, cv, colID+"=?",     new String []{String.valueOf(emp.getID())});     }

Delete data

public void DeleteEmp(Employee emp)  {   SQLiteDatabase db=this.getWritableDatabase();   db.delete(employeeTable,colID+"=?", new String [] {String.valueOf(emp.getID())});   db.close();  }

Obtain information about all Departments

Cursor getAllDepts()  {   SQLiteDatabase db=this.getReadableDatabase();   Cursor cur=db.rawQuery("SELECT "+colDeptID+" as _id, "+colDeptName+" from "+deptTable,new String [] {});      return cur;  }

Obtain information about employees in a department

public Cursor getEmpByDept(String Dept)  {   SQLiteDatabase db=this.getReadableDatabase();   String [] columns=new String[]{"_id",colName,colAge,colDeptName};   Cursor c=db.query(viewEmps, columns, colDeptName+"=?", new String[]{Dept}, null, null, null);   return c;  }

Obtain the Department ID

public int GetDeptID(String Dept)  {   SQLiteDatabase db=this.getReadableDatabase();   Cursor c=db.query(deptTable, new String[]{colDeptID+" as _id",colDeptName},colDeptName+"=?", new String[]{Dept}, null, null, null);   //Cursor c=db.rawQuery("SELECT "+colDeptID+" as _id FROM "+deptTable+"    //WHERE "+colDeptName+"=?", new String []{Dept});   c.moveToFirst();   return c.getInt(c.getColumnIndex("_id"));    }

The table of Department and employee information above is associated with each other, so the update and delete operations will update the corresponding information.

Original webpage: http://www.codeproject.com/KB/android/AndroidSQLite.aspx #