我正在尝试在2个表之间创建一个关系,但FK没有填充。它保持为空。下面是我的代码
public static final int DATABASE_VERSION = 1; // Database Name public static final String DATABASE_NAME = "LocalRugbyDB.db"; //table names public static final String TABLE_PLAYER_INFO = "PLAYER_Local"; public static final String TABLE_TEAM_INFO = "TEAM_local"; //add fields to player table public static final String KEY_PLAYER_ID = "_id"; public static final String KEY_FNAME = "first_name"; public static final String KEY_LNAME = "last_name"; public static final String KEY_AGE = "age"; public static final String KEY_HEIGHT = "height"; public static final String KEY_WEIGHT = "weight"; public static final String KEY_POSITION = "position"; public static final String KEY_TEAM = "team"; public static final String TEAM_ID = "team_id"; // add field to team table public static final String KEY_TEAM_ID = "_id"; public static final String KEY_TEAMNAME = "team_name"; public MySQLiteHelper(Context context, String name, CursorFactory factory, int version) { super(context, DATABASE_NAME, null, DATABASE_VERSION); Log.i("onCreateMaybe", "Created"); } @Override public void onCreate(SQLiteDatabase db) { // SQL statement to create book table String CREATE_PLAYER_TABLE = "CREATE TABLE " + TABLE_PLAYER_INFO + "( " + KEY_PLAYER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_FNAME + " TEXT," + KEY_LNAME + " TEXT," + KEY_POSITION + " TEXT," + KEY_HEIGHT + " TEXT," + KEY_AGE + " TEXT," + KEY_WEIGHT + " TEXT," + KEY_TEAM + " TEXT," + TEAM_ID + " integer," + " FOREIGN KEY ("+TEAM_ID+") REFERENCES "+TABLE_TEAM_INFO+" ("+KEY_TEAM_ID+"));"; String CREATE_TEAM_TABLE = "CREATE TABLE " + TABLE_TEAM_INFO + "( " + KEY_TEAM_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + KEY_TEAMNAME + " TEXT" + ");"; // create books table db.execSQL("PRAGMA foreign_keys = ON;"); db.execSQL(CREATE_TEAM_TABLE); db.execSQL(CREATE_PLAYER_TABLE); }
在这里您可以看到未填充FK列(team_id)。即时消息没有收到任何错误,并且已经尝试修复了几个小时。
外键是一种确保数据库保持一致的机制。它们不会自动从其他表中查找值。
您仍然必须自己完成所有工作。唯一的区别是数据库将防止您进行不一致的更改。
您的数据库未正确规范化;团队名称在所有球员记录中重复。只需从玩家表中删除KEY_TEAM。
要插入具有正确球队ID的新球员,您可以使用以下代码:
long lookupOrCreateTeamID(String name) {
Cursor c = db.query(TABLE_TEAM_INFO, new String[] { KEY_TEAM_ID },
KEY_TEAMNAME + " = ?", new String[] { name },
null, null, null);
if (c.moveToFirst())
return c.getLong(0);
else {
ContentValues cv = new ContentValues();
cv.put(KEY_TEAMNAME, name);
return db.insert(TABLE_TEAM_INFO, null, cv);
}
}
long createPlayer(String firstName, ..., String teamName) {
ContentValues cv = new ContentValues();
cv.put(KEY_FNAME, firstName);
...
cv.put(TEAM_ID, lookupOrCreateTeamID(teamName));
return db.insert(TABLE_PLAYER_INFO, null, cv);
}
请注意,PRAGMA foreign_keys
对于打开数据库的每个连接,都必须再次执行。因此,您不应onCreate
在onConfigure中执行此操作(如果您使用的是API级别16):
@Override
public void onConfigure(SQLiteDatabase db) {
db.setForeignKeyConstraintsEnabled(true);
}