android sqlite 外键不工作

Question

我正在尝试在 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) 没有被填充。我没有收到任何错误，并且已经尝试修复此问题数小时。

Answer 1

外键是一种确保数据库保持一致的机制；它们不会自动从其他表中查找值。

您仍然必须自己完成所有工作。唯一的区别是数据库会阻止您进行不一致的更改。

---

您的数据库未正确规范化；球队名称在所有球员记录中重复。只需从玩家表中删除 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);
}