Android SQLite 使用外键约束

               外键约束是很常用的一个数据约束方式。但是使用起来并不是那么简单。定义外键约束的方法，使用FOREIGN KEY，可以用于单列或者多列。FOREIGN KEY和REFERNCES经常联合使用。References表示该列引用哪个表的哪个列作为外键。单列时，FOREIGN KEY可以省略。

            在Android2.1以前的版本使用的SQLite版本是3.5.9，而SQLite在3.6.19版本才开始支持外键约束。在Android的2.2版本中使用了版本号3.6.22的SQLite，但是为了兼容以前的程序，默认并没有启用该功能。如果要启用该功能，就在SQLiteOpenHelper的onOpen方法里加入如下方法： 

           

	@Override
		public void onOpen(SQLiteDatabase db) {
			// TODO Auto-generated method stub
			super.onOpen(db);
			if(!db.isReadOnly()) { // Enable foreign key constraints
				db.execSQL("PRAGMA foreign_keys=ON;"); 
			}
		}

              然后就可以使用外键约束了。demo代码如下：

              

private static final String TABLE_THEME = "themeTable";
	private static final String THEME_TABLE_ID = "_id";
	private static final String THEME_TABLE_THEME_ID = "tid";
	private static final String THEME_TABLE_NAME = "name";
	private static final String THEME_TABLE_WATERMARK_ID = "wid";
	private static final String THEME_TABLE_MUSIC_ID = "mid";
	private static final String THEME_TABLE_ICON_PATH = "icon_path";//icon 路径
	private static final String THEME_TABLE_ICON_URL = "icon_url";//icon url
	
	private static final String TABLE_WATERMARK = "watermarkTable";
	private static final String WATERMARK_TABLE_ID = "_id";
	private static final String WATERMARK_TABLE_WATERMARK_ID = "wid";
	
	
	private static final String TABLE_MUSIC = "watermarkTable";
	private static final String MUSIC_TABLE_ID = "_id";
	private static final String MUSIC_TABLE_MUSIC_ID = "mid";

            

             

@Override
		public void onCreate(SQLiteDatabase db) {
			// TODO Auto-generated method stub

			//db.execSQL(CREATE_TABLE_CONTACT);
			
			db.execSQL( "CREATE TABLE IF NOT EXISTS "+
					    TABLE_WATERMARK+
			            "("+
			                WATERMARK_TABLE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + // rowID
			                WATERMARK_TABLE_WATERMARK_ID + "TEXT ,"+ //水印id
							
					    ")"
					   );
			db.execSQL( "CREATE TABLE IF NOT EXISTS "+
					    TABLE_MUSIC+
			            "("+
			                MUSIC_TABLE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + // rowID
			                MUSIC_TABLE_MUSIC_ID + "TEXT ,"+ //音乐id
							
					    ")"
					   );
			db.execSQL("CREATE TABLE IF NOT EXISTS "+
					    TABLE_THEME+
					    "("+
					    THEME_TABLE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + // rowID
						THEME_TABLE_THEME_ID + "TEXT ,"+ //主题id
						THEME_TABLE_NAME + " TEXT ," + // 主题名称
			            THEME_TABLE_WATERMARK_ID + " TEXT," + // 主题包含的水印id
			            THEME_TABLE_MUSIC_ID + " TEXT," + // 主题包含的配乐id
			            THEME_TABLE_ICON_URL + " TEXT ," + // 主题icon的url
			            THEME_TABLE_ICON_PATH + " TEXT " +   // 主题icon的下载路径
					    "FOREIGN KEY ("+THEME_TABLE_WATERMARK_ID+") REFERENCES"+TABLE_WATERMARK+" ("+WATERMARK_TABLE_WATERMARK_ID+")" +
					    "FOREIGN KEY ("+THEME_TABLE_MUSIC_ID+") REFERENCES"+TABLE_MUSIC+" ("+MUSIC_TABLE_MUSIC_ID+")" +
					    ");"
					  );
		}