jeudi 26 mars 2015

Android - SQLite and Foreign Keys

I know that in order to use foreign keys in android there is needed something like this:



// Enable foreign key constraints
db.execSQL("PRAGMA foreign_keys=ON;");


This also only works from Android 2.2 ? So I have been using triggers to simulate foreign keys behaviour INSERT_TRIGGER, UPDATE_TRIGGER, PARENT_DELETE_TRIGGER, PARENT_UPDATE_TRIGGER


And I consider now what happens if this Foreign Keys are set:



+ " foreign key (" + COLUMN_PROFILE_ID + ") references "
+ ProfileProvider.ProfileTable.TABLE_PROFILE
+ "(" + ProfileProvider.ProfileTable.COLUMN_PROFILE_ID + ")"
+ " on update cascade on delete cascade "


I'm a bit confused cause this trigger means only that: 1. on update parent rows my child rows will be updated too and on delete parent rows my child rows will be deleted too or 2. that when i try to insert new child row that doesn't have corresponding primary key for inserted foreign key there will be ERROR and when updating child row foreign key to new value not existing in parent table there also will be ERROR ?


Corresponding triggers looks like this:



private static final String PROFILE_INSERT_TRIGGER_CREATE = "create trigger fki_"
+ TABLE_LEARNING_HISTORY + "_" + COLUMN_PROFILE_ID + " "
+ "before insert on " + TABLE_LEARNING_HISTORY + " "
+ "for each row begin "
+ "select raise(rollback, 'insert on table " + TABLE_LEARNING_HISTORY
+ " violates foreign key constraint') "
+ "where new." + COLUMN_PROFILE_ID + "!= 0 AND (select " // 0 - Anonymous user
+ ProfileProvider.ProfileTable.COLUMN_PROFILE_ID
+ " from " + ProfileProvider.ProfileTable.TABLE_PROFILE
+ " where " + ProfileProvider.ProfileTable.COLUMN_PROFILE_ID
+ " = new." + COLUMN_PROFILE_ID + ") is null;"
+ " end;";


But as this is android application and there is possibility that there isn't user logged in to app the history will be stored also for anonymous user... (and when he will log in this data can be ported to created profile) So in parent PROFILE TABLE there isn't row with profile id 0 but in CHILD HISTORY TABLE i want the ability to store rows with 0 profile id (foreign key).


So to sum up I would like to know the behaviour enforced by such Foreign Key as above on inserting, updating child rows (there is integrity enforced? if so can i turn it of like 'on insert no action' ?)


Aucun commentaire:

Enregistrer un commentaire