java - 使用 ListView 检索数据的 SQLite 外键语法出现问题

Question

这是上下文。我有一个数据库助手，可以在一个数据库中创建 3 个表。其中两个表有外键。一切工作都很好，但考虑到我想做的事情，我需要使用 ListView 而不是 TextView 来显示我的数据，并能够编辑、删除等。出于某种原因， ListView 在 Android 上与其他编程语言不同。我弄清楚了适配器和所有这些废话。我的新问题是我的应用程序不会显示数据，当我检查 logcat 时，我收到此消息

 Caused by: java.lang.IllegalArgumentException: column '_id' does not exist. Available columns: [id, termName, termStart, termEnd]

这是一个名为“term_table”的表。所以我将 id 从“id”更改为“_id”，这样就成功了。

所以一切都很好，对吗？不，因为另外两个表有自己的 ID 和外键。当我对名为“assess_table”的不同表执行相同的查询时，出现了相同的错误，因此我将“id”更改为“_id”。问题是“courses_table”(第三个表)使用“_id”作为外键来引用名为“term_table”的表。因此，我收到一条错误消息，告诉我正在创建重复的列 - 主键“_id”和外键“_id”来引用 term_table。

我将“courses_table”的 id 从“_id”重命名为“courses_id”。现在我又回到了第一个地方，遇到了同样的错误:

 Caused by: java.lang.IllegalArgumentException: column '_id' does not exist. Available columns: [assess_id, assessName, assessDueDate, assessType, courses_id]

所以基本上，如果所有三个表都必须命名为_id，如果外键与引用表的主键具有相同的名称，那么在创建外键时我应该使用什么语法，所以我总是会得到创建重复列错误。我读到这个 ​​_id 命名方案仅适用于游标适配器。适合使用什么语法？

这是我的数据库助手中的 3 个表

//terms table
   public static final String TERM_TABLE_NAME = "term_table";
   public static final String COL_TERM1 = "_id";
   public static final String COL_TERM2 = "termName";
   public static final String COL_TERM3 = "termStart";
   public static final String COL_TERM4 = "termEnd";
   public static final String TERM_DATABASE_NAME = "term.db";
   //courses table
   public static final String COURSES_TABLE_NAME = "courses_table";
   public static final String COL_COURSE1 = "courses_id";
   public static final String COL_COURSE2 = "courseTitle";
   public static final String COL_COURSE3 = "courseStartDate";
   public static final String COL_COURSE4 = "courseEndDate";
   public static final String COL_COURSE5 = "courseStatus";
   public static final String COL_COURSE6 = "optionalNote";
   public static final String COL_COURSE7 = "mentorName";
   public static final String COL_COURSE8 = "mentorPhone";
   public static final String COL_COURSE9 = "mentorEmail";
   public static final String COL_COURSE_TERMID = "_id";
   //assess table
   public static final String ASSESS_TABLE_NAME = "assess_table";
   public static final String COL_ASSESS1 = "assess_id";
   public static final String COL_ASSESS2 = "assessName";
   public static final String COL_ASSESS3 = "assessDueDate";
   public static final String COL_ASSESS4 = "assessType";
   public static final String COL_ASSESS_COURSEID = "courses_id";

这是创建语法

public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + TERM_TABLE_NAME + " (_id INTEGER PRIMARY KEY AUTOINCREMENT, termName TEXT, termStart TEXT, termEnd TEXT)");
        db.execSQL("CREATE TABLE " + COURSES_TABLE_NAME + " (courses_id INTEGER PRIMARY KEY AUTOINCREMENT, courseTitle TEXT, courseStartDate TEXT, courseEndDate TEXT, courseStatus INTEGER, optionalNote TEXT, mentorName TEXT, mentorPhone TEXT, mentorEmail TEXT, _id INTEGER, FOREIGN KEY (_id) REFERENCES term_table(_id))");
        db.execSQL("CREATE TABLE " + ASSESS_TABLE_NAME + " (assess_id INTEGER PRIMARY KEY AUTOINCREMENT, assessName TEXT, assessDueDate TEXT, assessType INTEGER, courses_id INTEGER, FOREIGN KEY (courses_id) REFERENCES courses_table(courses_id))");
    }

Answer 1

像这样重命名主键和外键:

public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + TERM_TABLE_NAME + " (_id INTEGER PRIMARY KEY AUTOINCREMENT, termName TEXT, termStart TEXT, termEnd TEXT)");
        db.execSQL("CREATE TABLE " + COURSES_TABLE_NAME + " (_id INTEGER PRIMARY KEY AUTOINCREMENT, courseTitle TEXT, courseStartDate TEXT, courseEndDate TEXT, courseStatus INTEGER, optionalNote TEXT, mentorName TEXT, mentorPhone TEXT, mentorEmail TEXT, for_cors_term INTEGER, FOREIGN KEY (for_cors_term) REFERENCES term_table(_id))");
        db.execSQL("CREATE TABLE " + ASSESS_TABLE_NAME + " (_id INTEGER PRIMARY KEY AUTOINCREMENT, assessName TEXT, assessDueDate TEXT, assessType INTEGER, for_ases_cors INTEGER, FOREIGN KEY (for_ases_cors) REFERENCES courses_table(_id))");
    }

<小时/>

   public static final String TERM_DATABASE_NAME = "term.db";

   //terms table
   public static final String TERM_TABLE_NAME = "term_table";
   public static final String COL_TERM1 = "_id"; //primary key
   public static final String COL_TERM2 = "termName";
   public static final String COL_TERM3 = "termStart";
   public static final String COL_TERM4 = "termEnd";

   //courses table
   public static final String COURSES_TABLE_NAME = "courses_table";
   public static final String COL_COURSE1 = "_id"; //primary key
   public static final String COL_COURSE2 = "courseTitle";
   public static final String COL_COURSE3 = "courseStartDate";
   public static final String COL_COURSE4 = "courseEndDate";
   public static final String COL_COURSE5 = "courseStatus";
   public static final String COL_COURSE6 = "optionalNote";
   public static final String COL_COURSE7 = "mentorName";
   public static final String COL_COURSE8 = "mentorPhone";
   public static final String COL_COURSE9 = "mentorEmail";
   public static final String COL_COURSE_TERMID = "for_cors_term"; //foreign key

   //assess table
   public static final String ASSESS_TABLE_NAME = "assess_table";
   public static final String COL_ASSESS1 = "_id"; //primary key
   public static final String COL_ASSESS2 = "assessName";
   public static final String COL_ASSESS3 = "assessDueDate";
   public static final String COL_ASSESS4 = "assessType";
   public static final String COL_ASSESS_COURSEID = "for_ases_cors"; //foreign key

<小时/>

或者，如果您不想使用_id字段作为主键，您可以使用RecyclerView而不是ListView