android的sqlite的外键不能正常工作 [英] android sqlite foreign key not working

问题描述

我想要创建2个表之间的关系，但FK不填充。它是剩余空。下面是我的code

i'm trying to create a relationship between 2 tables but the FK is not populating. it is remaining null. below is my code

 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）不被填充。即时消息没有得到任何错误，并一直在试图解决这个问题了几个小时。

here you can see that the FK column (team_id) is not being populated. im not getting any error and have been trying to fix this for hours.

推荐答案

外键是一个mechnism，以确保数据库保持一致;他们这样做的不的其他表自动查找值。

Foreign keys are a mechnism to ensure that the database stays consistent; they do not automatically look up values from other tables.

您还是得自己做所有的工作。
唯一的区别是，该数据库将prevent你进行不一致变化。

You still have to do all the work yourself. The only difference is that the database will prevent you from making inconsistent changes.

您的数据库无法正常规范化;队名在所有选手的记录​​复制。
刚刚从球员表中删除KEY_TEAM。

Your database is not properly normalized; the team name is duplicated in all player records. Just drop KEY_TEAM from the player table.

要插入与正确的团队ID的新球员，你会用code是这样的：

To insert a new player with the correct team ID, you would use code like this:

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 但在<一个做到这一点href=\"http://developer.android.com/reference/android/database/sqlite/SQLiteOpenHelper.html#onConfigure%28android.database.sqlite.SQLiteDatabase%29\"相对=nofollow> onConfigure （如果你使用的API级别16）：

---

Please note that the PRAGMA foreign_keys must be executed again for every connection that opens the database. So you should not do this in onCreate but in onConfigure (if you're using API level 16):

@Override
public void onConfigure(SQLiteDatabase db) {
    db.setForeignKeyConstraintsEnabled(true);
}

这篇关于android的sqlite的外键不能正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！