android - FOREIGN KEY 约束失败(代码 787)[NO ANDROID ROOM]
问题描述
我在 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);
解决方案
通过使用 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.ID
的任何行的列中的值ConstantesInterfaces.UserID.TABLA_USER
。和b) 该列,按照
ConstantesInterfaces.EntradasID.ID_TIPO
,有一条规则,该ConstantesInterfaces.EntradasID.ID_TIPO
列的值必须是存在于表ConstantesInterfaces.TypeID.ID
的任何行的列中的值ConstantesInterfaces.TypeID.TABLA_TYPE
。
如果不遵守上述规则,则结果将是FOREIGN KEY 约束失败。
因此,您需要在父表中插入行,以便在插入表之前可以引用它们ConstantesInterfaces.EntradasID.TABLA_ENTRY
。
表中需要有一行在列中的值为ConstantesInterfaces.UserID.TABLA_USER
US -c8a20dae-5492-4243-9560-aaf458f3f2d5ConstantesInterfaces.UserID.ID
和
表中需要有一行在列中ConstantesInterfaces.TypeID.TABLA_TYPE
具有TYP-20f4c0ba-8aa7-47c9-9cd8-19861f93f2ddConstantesInterfaces.TypeID.TIPO
的值。
笔记
您提供的代码似乎存在重大问题。所有三个表定义都有/使用:-
text primary key autoincrement
这是无效的 SQL,因为您只能将AUTOINCREMENT与. 使用会导致错误。integer primary key
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
推荐阅读
- python - 光标 fastexecutemany 错误: ('HY000', '[HY000] [Microsoft][SQL Server Native Client 11.0]Unicode 转换失败 (0) (SQLExecute)')
- react-native - 反应本机图表套件响应不起作用
- database - 有没有人可以帮我解决在oracle 19c上导入dmp文件的问题
- javascript - 我如何检查两个数字是否以相同的数字开头Javascript
- r - r ggplot如何调整bar的列宽
- r - 如果 dplyr 的参数不为空,则使用函数参数有条件地添加管道
- java - Java:关于定位最小位置的矩阵的一些错误
- amazon-dynamodb - 出现错误无法实例化类 com.amazonaws.services.dynamodbv2.datamodeling.DynamoDBGeneratedUuid$Generator",
- php - jquery International Telephone Input,获取国家代码+邮局电话号码
- visual-studio-code - VS Code Dev Containers 是否可以使用基于 Windows 的容器?