Android Studio中SQLite报错：期望约束/括号/逗号却得到'TEXT'

我帮你找出了几个导致SQLite语法错误的问题，咱们一步步来修复：

1. 外键定义中的列名错误 #

在创建Student表的SQL语句里，你直接写了KEY_COURSEID字符串作为外键列名，而没有使用Student.KEY_COURSEID和Course.KEY_COURSEID这些常量。SQLite会把这个字符串当成列名，但你的表中根本没有名为KEY_COURSEID的列，这直接触发了语法错误。同样，Staff表的外键定义也犯了同样的错误。

2. Student实体类缺少必要的静态常量 #

你的数据库类中大量使用了Student.TABLE、Student.KEY_STUDENTID这类静态常量，但你提供的Student类代码里完全没有定义这些常量。这不仅会导致编译报错，也是SQL语句逻辑混乱的根源。

3. onUpgrade方法中的无效死循环 #

你的onUpgrade方法末尾有个while (newVersion < oldVersion);的死循环，这完全没有意义，还会导致数据库升级时程序卡死，必须删掉。

修正后的代码 #

首先补充Student类的静态常量和缺失的方法

package com.example.ajhillie.btc_ucs_ma.Entities;

import android.content.Context;
import android.widget.Toast;

public class Student {
    // 补充表名和列名常量
    public static final String TABLE = "Students";
    public static final String KEY_STUDENTID = "StudentID";
    public static final String KEY_PASSWORD = "Password";
    public static final String KEY_FIRSTNAME = "FirstName";
    public static final String KEY_SURNAME = "Surname";
    public static final String KEY_ADDRESSONE = "Address1";
    public static final String KEY_ADDRESSTWO = "Address2";
    public static final String KEY_TOWNCITY = "Town_City";
    public static final String KEY_POSTCODE = "Postcode";
    public static final String KEY_MOBILENO = "MobileNo";
    public static final String KEY_LANDLINENO = "LandlineNo";
    public static final String KEY_PEMAIL = "pEmail";
    public static final String KEY_CEMAIL = "cEmail";
    public static final String KEY_COURSEID = "CourseID";

    private String StudentID;
    private String Password;
    private String FirstName;
    private String Surname;
    private String Address1;
    private String Address2;
    private String Town_City;
    private String Postcode;
    private long MobileNo;
    private long LandlineNo;
    private String pEmail;
    private String cEmail;
    private String CourseID;

    // 你的原有getter/setter保持不变
    public String getStudentID() { return StudentID; }
    public void setStudentID(String studentID) { StudentID = studentID; }
    public String getPassword() { return Password; }
    public void setPassword(String password) { Password = password; }
    public String getFirstName() { return FirstName; }
    public void setFirstName(String firstName) { FirstName = firstName; }
    public String getSurname() { return Surname; }
    public void setSurname(String surname) { Surname = surname; }
    public String getAddress1() { return Address1; }
    public void setAddress1(String address1) { Address1 = address1; }
    public String getAddress2() { return Address2; }
    public void setAddress2(String address2) { Address2 = address2; }
    public String getTown_City() { return Town_City; }
    public void setTown_City(String town_City) { Town_City = town_City; }
    public String getPostcode() { return Postcode; }
    public void setPostcode(String postcode) { Postcode = postcode; }
    public long getMobileNo() { return MobileNo; }
    public void setMobileNo(long mobileNo) { MobileNo = mobileNo; }
    public long getLandlineNo() { return LandlineNo; }
    public void setLandlineNo(long landlineNo) { LandlineNo = landlineNo; }
    public String getpEmail() { return pEmail; }
    public void setpEmail(String PEmail) { this.pEmail = PEmail; }

    // 补充CourseID的getter/setter
    public String getCourseID() { return CourseID; }
    public void setCourseID(String courseID) { CourseID = courseID; }
}

然后修正数据库类的SQL语句和onUpgrade方法

package com.example.ajhillie.btc_ucs_ma.Database;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import com.example.ajhillie.btc_ucs_ma.Entities.Course;
import com.example.ajhillie.btc_ucs_ma.Entities.Staff;
import com.example.ajhillie.btc_ucs_ma.Entities.Student;

public class BTC_UCS_Database extends SQLiteOpenHelper {
    private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "BTCUCSdatabase";
    private Context c;

    public BTC_UCS_Database(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.c = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // 先创建Course表（因为其他表依赖它）
        String CREATE_TABLE_COURSES = "CREATE TABLE " + Course.TABLE + "("
                + Course.KEY_COURSEID + " TEXT PRIMARY KEY, "
                + Course.KEY_COURSENAME + " TEXT, "
                + Course.KEY_COURSEDES + " TEXT, "
                + Course.KEY_COURSECONTENTONE + " TEXT, "
                + Course.KEY_COURSECONTENTTWO + " TEXT, "
                + Course.KEY_COURSECONTENTTHREE + " TEXT, "
                + Course.KEY_COURSECONTENTFOUR + " TEXT, "
                + Course.KEY_COURSEPRICE + " TEXT, "
                + Course.KEY_COURSELENGTH + " TEXT)";

        // 修正Student表的外键定义
        String CREATE_TABLE_STUDENTS = "CREATE TABLE " + Student.TABLE + "("
                + Student.KEY_STUDENTID + " TEXT PRIMARY KEY, "
                + Student.KEY_PASSWORD + " TEXT, "
                + Student.KEY_FIRSTNAME + " TEXT, "
                + Student.KEY_SURNAME + " TEXT, "
                + Student.KEY_ADDRESSONE + " TEXT, "
                + Student.KEY_ADDRESSTWO + " TEXT, "
                + Student.KEY_TOWNCITY + " TEXT, "
                + Student.KEY_POSTCODE + " TEXT, "
                + Student.KEY_MOBILENO + " BIGINT, "
                + Student.KEY_LANDLINENO + " BIGINT, "
                + Student.KEY_PEMAIL + " TEXT, "
                + Student.KEY_CEMAIL + " TEXT, "
                + Student.KEY_COURSEID + " TEXT, "
                + " FOREIGN KEY (" + Student.KEY_COURSEID + ") REFERENCES " + Course.TABLE + " (" + Course.KEY_COURSEID + "))";

        // 修正Staff表的外键定义
        String CREATE_TABLE_STAFF = "CREATE TABLE " + Staff.TABLE + "("
                + Staff.KEY_STAFFID + " TEXT PRIMARY KEY, "
                + Staff.KEY_PASSWORD + " TEXT, "
                + Staff.KEY_FIRSTNAME + " TEXT, "
                + Staff.KEY_SURNAME + " TEXT, "
                + Staff.KEY_ADDRESSONE + " TEXT, "
                + Staff.KEY_ADDRESSTWO + " TEXT, "
                + Staff.KEY_TOWNCITY + " TEXT, "
                + Staff.KEY_POSTCODE + " TEXT, "
                + Staff.KEY_MOBILENO + " BIGINT, "
                + Staff.KEY_LANDLINENO + " BIGINT, "
                + Staff.KEY_PEMAIL + " TEXT, "
                + Staff.KEY_CEMAIL + " TEXT, "
                + Staff.KEY_COURSEID + " TEXT REFERENCES " + Course.TABLE + " (" + Course.KEY_COURSEID + "))";

        // 按依赖顺序执行创建语句
        db.execSQL(CREATE_TABLE_COURSES);
        db.execSQL(CREATE_TABLE_STUDENTS);
        db.execSQL(CREATE_TABLE_STAFF);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion < newVersion) {
            // 删除表的顺序要和创建顺序相反
            db.execSQL("DROP TABLE IF EXISTS " + Student.TABLE);
            db.execSQL("DROP TABLE IF EXISTS " + Staff.TABLE);
            db.execSQL("DROP TABLE IF EXISTS " + Course.TABLE);
            onCreate(db);
        }
        // 移除无效的死循环
    }
}

额外注意事项 #

• 确保Course和Staff类也定义了对应的TABLE和KEY_*静态常量，格式和Student类一致，否则还是会出现编译或运行错误。
• SQLite中外键约束默认是开启的（从3.6.19版本开始），如果你的环境版本特殊，可以在创建数据库时执行db.execSQL("PRAGMA foreign_keys = ON;")来确保外键约束生效。

内容的提问来源于stack exchange，提问作者Jordan Southey