Wednesday, March 21, 2012

What All query does Android Sqlite supports?


Introduction

Android default Database engine is Lite. SQLite is a lightweight transactional database engine that occupies a small amount of disk storage and memory, so it's a perfect choice for creating databases on many mobile operating systems such as Android, iOS.
Things to consider when dealing with SQLite:
    1.Data type integrity is not maintained in SQLite, you can put a value of a certain data type in a column of another datatype (put string in an integer and vice versa).
    2.Referential integrity is not maintained in SQLite, there is no FOREIGN KEY constraints or JOIN statements.
    3.SQLite Full Unicode support is optional and not installed by default.

Managing Foreign-Key Constraints

We mentioned before that SQLite 3 by default does not support foreign key constraint, however we can force such a constraint using TRIGGERS: we will create a trigger that ensures that when a new Employee is inserted, his/herDept value is present in the original Dept table. The SQL statement to create such a trigger would be like this:


CREATE TRIGGER fk_empdept_deptid Before INSERT ON Employees 
FOR EACH ROW BEGIN
    SELECT CASE WHEN ((SELECT DeptID FROM Dept WHERE DeptID =new.Dept ) IS NULL)
    THEN RAISE (ABORT,'Foreign Key Violation') END;
    END

You can Create the table with help of triggers.........

db.execSQL("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;");

You can also create the view if needed....
db.execSQL("DROPVIEW IF EXISTS "+viewEmps);








No comments:

Post a Comment