android - FOREIGN KEY 约束失败(代码 787)[NO ANDROID ROOM]

Question

我在 Android Studio 上有一个 Android 项目，它有一个包含 3 个表的数据库，其中一个有两个 FK。我想我看到了每个代码的解决方案和适合我的工作。

这是错误:

E/SQLiteDatabase: Error inserting idTipoFK=TYP-20f4c0ba-8aa7-47c9-9cd8-19861f93f2dd idUserFK=US-c8a20dae-5492-4243-9560-aaf458f3f2d5 adjuntoImg=imagen titulo=titulo idEntrada=ENT-13a62bd7-28ef-46f6-b9b3-b534a0c813ae texto=texto fecha=09-12-2019.12:27:01 adjuntoAudio=audio
android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787)
    at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
    at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:782)
    at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)
    at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:86)
    at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1474)
    at android.database.sqlite.SQLiteDatabase.insert(SQLiteDatabase.java:1343)
    at dam.proyecto.nasciturus.Database.OperacionesDataBase.insertarEntry(OperacionesDataBase.java:102)
    at dam.proyecto.nasciturus.Actividades.ListarActivity$4.onClick(ListarActivity.java:116)
    at android.view.View.performClick(View.java:5637)
    at android.view.View$PerformClick.run(View.java:22429)
    at android.os.Handler.handleCallback(Handler.java:751)
    at android.os.Handler.dispatchMessage(Handler.java:95)
    at android.os.Looper.loop(Looper.java:154)
    at android.app.ActivityThread.main(ActivityThread.java:6119)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:886)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:776)

好的，我的数据库:

public class DataBase extends SQLiteOpenHelper
{
    //VARIABLES DE CONEXION
      private static final String BASE_DATOS = "nascitur.db";
      private static final int VERSION_ACTUAL = 4;
      private Context contexto;

    //METODOS PARA CONECTAR Y CERRAR A LA BBDD



    public DataBase(Context contexto)
    {
        super(contexto, BASE_DATOS, null, VERSION_ACTUAL);
        this.contexto = contexto;
    }

    @Override
    public void onOpen(SQLiteDatabase db)
    {
        super.onOpen(db);
        if(!db.isReadOnly())
        {
            if(Build.VERSION.SDK_INT >= Build.VERSION_CODES.JELLY_BEAN)
            {
                db.setForeignKeyConstraintsEnabled(true);
            }
            else
            {
                db.execSQL("PRAGMA foreign_keys=ON");
            }
        }
    }
    public void onCreate(SQLiteDatabase db)
    {

        db.execSQL("CREATE TABLE "+ConstantesInterfaces.UserID.TABLA_USER+"("+BaseColumns._ID+" text primary key autoincrement,"
                +ConstantesInterfaces.UserID.ID+" text not null unique, "+ConstantesInterfaces.UserID.USUARIO+" text not null, "+ConstantesInterfaces.UserID.PASSWORD+" text not null)");
        db.execSQL("CREATE TABLE "+ConstantesInterfaces.TypeID.TABLA_TYPE+" ("+BaseColumns._ID+" text primary key autoincrement,"
                +ConstantesInterfaces.TypeID.ID+" text not null unique, "+ConstantesInterfaces.TypeID.TIPO+" text not null)");
        db.execSQL("CREATE TABLE "+ConstantesInterfaces.EntradasID.TABLA_ENTRY+" ("+BaseColumns._ID+" text primary key autoincrement,"+ConstantesInterfaces.EntradasID.ID+" text not null unique, "
                +ConstantesInterfaces.EntradasID.FECHA+" date not null, "+ConstantesInterfaces.EntradasID.TITULO+" text, "+ConstantesInterfaces.EntradasID.TEXTO+" text, "
                +ConstantesInterfaces.EntradasID.ADJUNTOIMG+" text,"+ConstantesInterfaces.EntradasID.ADJUNTOAUDIO+" text,"
                +ConstantesInterfaces.EntradasID.ID_USER+" text not null, "+ConstantesInterfaces.EntradasID.ID_TIPO+" text not null, FOREIGN KEY("
                +ConstantesInterfaces.EntradasID.ID_USER+") REFERENCES "+ConstantesInterfaces.UserID.TABLA_USER+
                " ("+ConstantesInterfaces.UserID.ID+") ON DELETE CASCADE, FOREIGN KEY("+ConstantesInterfaces.EntradasID.ID_TIPO+") REFERENCES "
                +ConstantesInterfaces.TypeID.TABLA_TYPE+" ("+ConstantesInterfaces.TypeID.ID+") ON DELETE CASCADE)");

    }
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
        db.execSQL("DROP TABLE IF EXISTS "+ ConstantesInterfaces.UserID.TABLA_USER);
        db.execSQL("DROP TABLE IF EXISTS "+ ConstantesInterfaces.TypeID.TABLA_TYPE);
        db.execSQL("DROP TABLE IF EXISTS "+ ConstantesInterfaces.EntradasID.TABLA_ENTRY);
        onCreate(db);
    }
}

和插入方法:

public void insertarEntry(Entry entrada) {
        SQLiteDatabase db = baseDatos.getWritableDatabase();

        // Generar Pk
        String idEntrada = ConstantesInterfaces.EntradasID.generarIdEntrada();

        ContentValues valores = new ContentValues();
        valores.put(ConstantesInterfaces.EntradasID.ID, idEntrada);
        valores.put(ConstantesInterfaces.EntradasID.FECHA, entrada.fecha);
        valores.put(ConstantesInterfaces.EntradasID.TITULO, entrada.titulo);
        valores.put(ConstantesInterfaces.EntradasID.TEXTO, entrada.texto);
        valores.put(ConstantesInterfaces.EntradasID.ADJUNTOIMG, entrada.adjuntoImg);
        valores.put(ConstantesInterfaces.EntradasID.ADJUNTOAUDIO, entrada.adjuntoAudio);
        valores.put(ConstantesInterfaces.EntradasID.ID_USER, entrada.idUsuario);
        valores.put(ConstantesInterfaces.EntradasID.ID_TIPO, entrada.idTipo);

        // Insertar entrada
        db.insert(ConstantesInterfaces.EntradasID.TABLA_ENTRY, null, valores);

    }

最后，这是我作为参数发送给方法的对象:

Entry entrada = new Entry(ConstantesInterfaces.EntradasID.generarIdEntrada(),fecha, "titulo","texto",adjuntoImg, "audio"  ,usuario , tipo);
OperacionesDataBase db = OperacionesDataBase.obtenerInstancia(getApplicationContext());
db.insertarEntry(entrada);

Answer 1

通过使用 FORIEGN KEYS 定义一个或多个列，您定义了一个规则(约束)，该规则表明该列只能包含一个或多个值，该值是父表的一个或多个列中的一个或多个值。

那是使用:-

...... FOREIGN KEY("
                +ConstantesInterfaces.EntradasID.ID_USER+") REFERENCES "+ConstantesInterfaces.UserID.TABLA_USER+
                " ("+ConstantesInterfaces.UserID.ID+") ON DELETE CASCADE, FOREIGN KEY("+ConstantesInterfaces.EntradasID.ID_TIPO+") REFERENCES "
                +ConstantesInterfaces.TypeID.TABLA_TYPE+" ("+ConstantesInterfaces.TypeID.ID+") ON DELETE CASCADE)");

你说

• a) 根据 ConstantesInterfaces.EntradasID.ID_USER，该列有一条规则表明 ConstantesInterfaces.EntradasID.ID_USER 列的值必须 是ConstantesInterfaces.UserID.TABLA_USER 表中任何行的ConstantesInterfaces.UserID.ID 列中存在的值。 和

• b) 根据 ConstantesInterfaces.EntradasID.ID_TIPO，该列有一条规则表明 ConstantesInterfaces.EntradasID.ID_TIPO 列的值必须是存在于 ConstantesInterfaces.TypeID.TABLA_TYPE 表的任何行的 ConstantesInterfaces.TypeID.ID 列中的值。

如果不遵守上述规则，则结果将是FOREIGN KEY constraint failed。

因此，您需要在父表中插入行，以便在插入 ConstantesInterfaces.EntradasID.TABLA_ENTRY 表时可以引用它们BEFORE。

ConstantesInterfaces.UserID.TABLA_USER 表中需要有一行值 US-c8a20dae-5492-4243-9560-aaf458f3f2d5 ConstantesInterfaces.UserID.ID列

和

ConstantesInterfaces.TypeID.TABLA_TYPE 表中需要有一行值 TYP-20f4c0ba-8aa7-47c9-9cd8-19861f93f2dd ConstantesInterfaces.TypeID.TIPO 列。

注意事项

您提供的代码似乎存在重大问题。所有三个表定义都有/使用:-

 text primary key autoincrement

这是无效的 SQL，因为您可以仅将AUTOINCREMENT 与整数主键 一起使用。使用text primary key autoincrement会导致错误。

示例/测试

假设一个 DataBase.java 包含所有三个表的插入方法:-

public long insertEntry(String id,String fecha,String title, String to, String image,String audio, String userIdReference, String typeIdReference) {
    long rv = -1;
    try {
        ContentValues cv = new ContentValues();
        cv.put(ConstantesInterfaces.EntradasID.ID, id);
        cv.put(ConstantesInterfaces.EntradasID.FECHA, fecha);
        cv.put(ConstantesInterfaces.EntradasID.TITULO, title);
        cv.put(ConstantesInterfaces.EntradasID.TEXTO, to);
        cv.put(ConstantesInterfaces.EntradasID.ADJUNTOIMG, image);
        cv.put(ConstantesInterfaces.EntradasID.ADJUNTOAUDIO, audio);
        cv.put(ConstantesInterfaces.EntradasID.ID_USER, userIdReference);
        cv.put(ConstantesInterfaces.EntradasID.ID_TIPO, typeIdReference);
        rv = this.getWritableDatabase().insertOrThrow(ConstantesInterfaces.EntradasID.TABLA_ENTRY, null, cv);
    } catch (SQLiteException e) {
        e.printStackTrace();
    }
    return rv;
}

public  long insertType(String id, String type) {
    ContentValues cv = new ContentValues();
    cv.put(ConstantesInterfaces.TypeID.ID,id);
    cv.put(ConstantesInterfaces.TypeID.TIPO,type);
    return this.getWritableDatabase().insert(ConstantesInterfaces.TypeID.TABLA_TYPE,null,cv);
}

public long insertUser(String id, String userName, String password) {
    ContentValues cv = new ContentValues();
    cv.put(ConstantesInterfaces.UserID.ID,id);
    cv.put(ConstantesInterfaces.UserID.USUARIO,userName);
    cv.put(ConstantesInterfaces.UserID.PASSWORD,password);
    return this.getWritableDatabase().insert(ConstantesInterfaces.UserID.TABLA_USER,null,cv);
} 

• 注意 insertEntry 如何使用 insertOrThrow，以便捕获错误并尝试后续尝试。

并在 Activity 中添加以下代码:-

public class MainActivity extends AppCompatActivity {

    DataBase mDB;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        String userid = "US-c8a20dae-5492-4243-9560-aaf458f3f2d5";
        String typeid = "TYP-20f4c0ba-8aa7-47c9-9cd8-19861f93f2dd";

        Log.d("FKTESTINFO","Stage 1 - Instantiating database instance");
        mDB = new DataBase(this);
        //DatabaseUtils.dumpCursor(mDB.getWritableDatabase().query("sqlite_master",null,null,null,null,null,null));

        Log.d("FKTESTINFO","Stage 2 - INSERT attempt 1");
        mDB.insertEntry("w","x","y","x","img","audio",userid,typeid); // FAIL as Type nor User exist

        Log.d("FKTESTINFO","Stage 3 - INSERT attempt 2");
        mDB.insertUser(userid,"Fred","password");
        mDB.insertEntry("w","x","y","x","img","audio",userid,typeid); // FAIL as Type does not exist

        Log.d("FKTESTINFO","Stage 4 - INSERT attempt 3");
        mDB.insertType(typeid,"My Type");
        mDB.insertEntry("w","x","y","x","img","audio",userid,typeid); // FAIL as Type does not exist
        Log.d("FKTESTINFO","Stage 5 - All Completed");
    }
}

结果显示，直到添加了引用的类型和引用的用户，条目插入才起作用，按照:-

2019-12-10 13:42:47.547 D/FKTESTINFO: Stage 1 - Instantiating database instance
2019-12-10 13:42:47.547 D/FKTESTINFO: Stage 2 - INSERT attempt 1
2019-12-10 13:42:47.567 W/System.err: android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY)
2019-12-10 13:42:47.567 W/System.err:     at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
2019-12-10 13:42:47.567 W/System.err:     at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:879)
2019-12-10 13:42:47.567 W/System.err:     at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:790)
2019-12-10 13:42:47.567 W/System.err:     at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:88)
2019-12-10 13:42:47.567 W/System.err:     at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1599)
2019-12-10 13:42:47.567 W/System.err:     at android.database.sqlite.SQLiteDatabase.insertOrThrow(SQLiteDatabase.java:1494)
2019-12-10 13:42:47.567 W/System.err:     at a.so59250186foreignkeys.DataBase.insertEntry(DataBase.java:135)
2019-12-10 13:42:47.567 W/System.err:     at a.so59250186foreignkeys.MainActivity.onCreate(MainActivity.java:26)
2019-12-10 13:42:47.567 W/System.err:     at android.app.Activity.performCreate(Activity.java:7802)
2019-12-10 13:42:47.567 W/System.err:     at android.app.Activity.performCreate(Activity.java:7791)
2019-12-10 13:42:47.567 W/System.err:     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1299)
2019-12-10 13:42:47.567 W/System.err:     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:3245)
2019-12-10 13:42:47.568 W/System.err:     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3409)
2019-12-10 13:42:47.568 W/System.err:     at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:83)
2019-12-10 13:42:47.568 W/System.err:     at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:135)
2019-12-10 13:42:47.568 W/System.err:     at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:95)
2019-12-10 13:42:47.568 W/System.err:     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2016)
2019-12-10 13:42:47.568 W/System.err:     at android.os.Handler.dispatchMessage(Handler.java:107)
2019-12-10 13:42:47.568 W/System.err:     at android.os.Looper.loop(Looper.java:214)
2019-12-10 13:42:47.568 W/System.err:     at android.app.ActivityThread.main(ActivityThread.java:7356)
2019-12-10 13:42:47.568 W/System.err:     at java.lang.reflect.Method.invoke(Native Method)
2019-12-10 13:42:47.568 W/System.err:     at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:492)
2019-12-10 13:42:47.568 W/System.err:     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:930)
2019-12-10 13:42:47.568 D/FKTESTINFO: Stage 3 - INSERT attempt 2
2019-12-10 13:42:47.572 W/System.err: android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY)
2019-12-10 13:42:47.572 W/System.err:     at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
2019-12-10 13:42:47.572 W/System.err:     at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:879)
2019-12-10 13:42:47.572 W/System.err:     at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:790)
2019-12-10 13:42:47.572 W/System.err:     at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:88)
2019-12-10 13:42:47.572 W/System.err:     at android.database.sqlite.SQLiteDatabase.insertWithOnConflict(SQLiteDatabase.java:1599)
2019-12-10 13:42:47.572 W/System.err:     at android.database.sqlite.SQLiteDatabase.insertOrThrow(SQLiteDatabase.java:1494)
2019-12-10 13:42:47.573 W/System.err:     at a.so59250186foreignkeys.DataBase.insertEntry(DataBase.java:135)
2019-12-10 13:42:47.573 W/System.err:     at a.so59250186foreignkeys.MainActivity.onCreate(MainActivity.java:30)
2019-12-10 13:42:47.573 W/System.err:     at android.app.Activity.performCreate(Activity.java:7802)
2019-12-10 13:42:47.573 W/System.err:     at android.app.Activity.performCreate(Activity.java:7791)
2019-12-10 13:42:47.573 W/System.err:     at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1299)
2019-12-10 13:42:47.573 W/System.err:     at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:3245)
2019-12-10 13:42:47.573 W/System.err:     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3409)
2019-12-10 13:42:47.573 W/System.err:     at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:83)
2019-12-10 13:42:47.573 W/System.err:     at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:135)
2019-12-10 13:42:47.573 W/System.err:     at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:95)
2019-12-10 13:42:47.573 W/System.err:     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2016)
2019-12-10 13:42:47.574 W/System.err:     at android.os.Handler.dispatchMessage(Handler.java:107)
2019-12-10 13:42:47.574 W/System.err:     at android.os.Looper.loop(Looper.java:214)
2019-12-10 13:42:47.574 W/System.err:     at android.app.ActivityThread.main(ActivityThread.java:7356)
2019-12-10 13:42:47.574 W/System.err:     at java.lang.reflect.Method.invoke(Native Method)
2019-12-10 13:42:47.574 W/System.err:     at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:492)
2019-12-10 13:42:47.574 W/System.err:     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:930)
2019-12-10 13:42:47.574 D/FKTESTINFO: Stage 4 - INSERT attempt 3
2019-12-10 13:42:47.585 D/FKTESTINFO: Stage 5 - All Completed