Android SQLite外键约束实现失败且无表创建，寻求解决方法

Hey there! Let's break down and solve the two problems you're facing with your SQLite setup—missing tables and non-functional foreign key constraints. I've worked through similar issues before, so here's a step-by-step guide to get things working:

1. Why No Tables Are Being Created? #

First, let's rule out the most common reasons your tables aren't showing up:

• Existing Database Cache: SQLiteOpenHelper only runs onCreate() when the database file doesn't exist yet. If you've run the app before, the old database is still there. Fix this by:

  • Uninstalling the app from your emulator/device and reinstalling it.
  • Or, in your onUpgrade() method, add code to drop old tables and re-run onCreate() (great for testing changes):

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop existing tables to force recreation
        db.execSQL("DROP TABLE IF EXISTS Appointment");
        db.execSQL("DROP TABLE IF EXISTS Patient");
        onCreate(db);
    }
    

  • Bump your DATABASE_VERSION number to trigger onUpgrade().

• Syntax Errors in Create Table Statements: A tiny typo (like missing commas, wrong column names, or invalid data types) will fail table creation silently. Double-check your CREATE TABLE SQL:

  • Ensure all fields are properly separated with commas.
  • Verify data types (e.g., INTEGER, TEXT) and constraints (like NOT NULL, PRIMARY KEY) are correctly written.

• Forgot to Access the Database: SQLiteOpenHelper won't initialize the database until you call getWritableDatabase() or getReadableDatabase() somewhere in your app. Make sure you're instantiating DatabaseHelper and calling one of these methods to trigger table creation.

2. Fixing Foreign Key Constraints Not Working #

Even if you enabled foreign keys, there are a few gaps that might be causing them to fail:

• Enable Foreign Keys Every Time the Database Opens: SQLite disables foreign keys by default, and enabling them once isn't enough—it needs to be set on every connection. The most reliable way is to use onConfigure() (available in API 16+):

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

  For older API versions, add this line in onCreate() and onOpen():

  db.execSQL("PRAGMA foreign_keys = ON;");
  

• Check Your Foreign Key Syntax: Make sure your FOREIGN KEY clause references the correct table and column. For example, if your Patient table has a primary key patient_id, your Appointment table's foreign key should look like this:

  CREATE TABLE Appointment (
      appointment_id INTEGER PRIMARY KEY AUTOINCREMENT,
      patient_id INTEGER NOT NULL,
      appointment_date TEXT NOT NULL,
      FOREIGN KEY (patient_id) REFERENCES Patient(patient_id) ON DELETE CASCADE
  );
  

  • Ensure the referenced table name (Patient) matches exactly what you used in its CREATE TABLE statement.
  • Include an action like ON DELETE CASCADE or ON UPDATE RESTRICT to define behavior when the parent record is modified.

• Test for Constraint Violations: To confirm foreign keys are working, try inserting an appointment with a patient_id that doesn't exist in the Patient table. If foreign keys are active, this should throw a SQLiteConstraintException. If it doesn't, double-check your enablement steps and table syntax.

• Update Existing Databases: If you added foreign keys after the initial database was created, the old tables won't have the constraint. Use the onUpgrade() method to drop and recreate the tables (as shown earlier) to apply the new constraints.

Example Working DatabaseHelper Snippet #

Here's a trimmed-down version of what your DatabaseHelper should look like with all fixes applied:

public class DatabaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "appointment_scheduler.db";
    private static final int DATABASE_VERSION = 2; // Bumped version to trigger upgrade

    // Patient Table
    private static final String TABLE_PATIENT = "Patient";
    private static final String COL_PATIENT_ID = "patient_id";
    private static final String COL_PATIENT_NAME = "patient_name";

    // Appointment Table
    private static final String TABLE_APPOINTMENT = "Appointment";
    private static final String COL_APPT_ID = "appointment_id";
    private static final String COL_APPT_PATIENT_ID = "patient_id";
    private static final String COL_APPT_DATE = "appointment_date";

    private final String CREATE_PATIENT_TABLE = "CREATE TABLE " + TABLE_PATIENT + "("
            + COL_PATIENT_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + COL_PATIENT_NAME + " TEXT NOT NULL"
            + ")";

    private final String CREATE_APPT_TABLE = "CREATE TABLE " + TABLE_APPOINTMENT + "("
            + COL_APPT_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + COL_APPT_PATIENT_ID + " INTEGER NOT NULL,"
            + COL_APPT_DATE + " TEXT NOT NULL,"
            + "FOREIGN KEY (" + COL_APPT_PATIENT_ID + ") REFERENCES " + TABLE_PATIENT + "(" + COL_PATIENT_ID + ") ON DELETE CASCADE"
            + ")";

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_PATIENT_TABLE);
        db.execSQL(CREATE_APPT_TABLE);
        // Fallback for older APIs
        db.execSQL("PRAGMA foreign_keys = ON;");
    }

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

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Clean up old tables
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_APPOINTMENT);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_PATIENT);
        onCreate(db);
    }
}

Quick Testing Tips #

• Use adb shell to inspect your database: Run sqlite3 /data/data/your.package.name/databases/appointment_scheduler.db, then type .tables to list existing tables.
• Try inserting invalid data (like an appointment with a non-existent patient ID) to see if the constraint is enforced.

内容的提问来源于stack exchange，提问作者Remmie Sithole