存在外键语法错误。

Question

我发现这句话有个错误：

CREATE TABLE trip (
   tid INTEGER PRIMARY KEY,
   from_trip TEXT,
   to_trip TEXT,
   seat_cost INTEGER,
   pickup TEXT,
   date INTEGER,
   time INTEGER,
   tmid INTEGER,
   FOREIGN KEY (tmid) REFERENCES member(KEY_MID),
   tcid INTEGER,
   FOREIGN KEY (tcid) REFERENCES carinfo(KEY_CID)),
   seats INTEGER
);

致命例外:主进程: com.example.user.apple，PID: 3547 android.database.sqlite.SQLiteException:近" tcid "：语法错误(代码1)：编译时:创建表trip(tid整数主键、from_trip文本、to_trip文本、seat_cost整数、拾取文本、日期整数、时间整数、tmid整数、外键(tmid)引用成员(KEY_MID)、透明整数、外键引用(KEY_CID) )，席位整数)；

Answer 1

//在数据助手类//中使用游标从另一个表中获取

   public Cursor getId(String tableName)
    {

    String query = ("insert into trip tmid select mid from member");
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor c = db.rawQuery(query,null);
    return  c;
     }
//this in my main activity
//calling cursor
            public void onClick(View v) {
            long isInserted = myDb.createride(etfrom.getText().toString(),
                    etto.getText().toString(),
                    etdate.getText().toString(),
                    ettime.getText().toString(),
                    etpick.getText().toString(),
                    etcost.getText().toString(),
                    etseat.getText().toString());
            if (isInserted > 0){

                Cursor cursor = myDb.getId(myDb.TABLE_MEMBER);
                startManagingCursor(cursor);

                while(cursor.move(0)) {
                    int id = cursor.getInt(0);
                }
     //this is how i using to get value to table
        public long createride(String from_trip,String to_trip,String date,String time,String pickup,String cost,String seats) {

    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(KEY_FROM,from_trip);
    values.put(KEY_TO,to_trip);
    values.put(KEY_DATE,date);
    values.put(KEY_TIME,time);
    values.put(KEY_PICKUP,pickup);
    values.put(KEY_SEATS,seats);
    values.put(KEY_SEAT_COST,cost);

    long insertId = db.insert(TABLE_TRIP, null, values);

    return insertId;
}

Answer 2

不要将FK约束与列约束交叉；要么使用内联版本：

mycolumn int references othertable

或所有列之后的显式约束：

mycol1 int,
mycol2 int,
foreign key (mycol1) references othertable,
foreign ket (mycol2) references othertable2 (somecol)

如果引用的列是主键(无论如何都应该定义该列)，则可以选择提及引用表的列。

因此，改变：

CREATE TABLE trip (
   tid INTEGER PRIMARY KEY,
   ...
   tmid INTEGER,
   FOREIGN KEY (tmid) REFERENCES member(KEY_MID),
   tcid INTEGER,
   FOREIGN KEY (tcid) REFERENCES carinfo(KEY_CID)),
   seats INTEGER
);

它有另一个语法错误和不匹配的括号，以便：

CREATE TABLE trip (
   tid INTEGER PRIMARY KEY,
   ...
   tmid INTEGER REFERENCES member,
   tcid INTEGER REFERENCES carinfo,
   seats INTEGER
);

Answer 3

    public long createride(String from_trip,String to_trip,String date,String time,String pickup,String cost,String seats) {

    SQLiteDatabase db = this.getWritableDatabase();

    ContentValues values = new ContentValues();
    values.put(KEY_FROM,from_trip);
    values.put(KEY_TO,to_trip);
    values.put(KEY_DATE,date);
    values.put(KEY_TIME,time);
    values.put(KEY_PICKUP,pickup);
    values.put(KEY_SEATS,seats);
    values.put(KEY_SEAT_COST,cost);
    long insertId = db.insert(TABLE_TRIP, null, values);

    return insertId;
}