会议室数据库-外键约束失败，错误代码787 [英] Room database - Foreign Key Constraint Failed Error code 787
问题描述
我遇到了上述问题，即使在查看其他类似问题及其解决方案以及外键上的sqlite文档时也无法解决.
I am getting the above problem, and am unable to solve even while looking at other similar questions and their solutions, as well as the sqlite document on foreign keys.
我知道外键必须先存在于父表中，然后才能在子表中创建.但是，即使先完成了，问题仍然存在.
I understand that a foreign key must exist in the parent table first before it can be created in the child table. However, even though that was done first the problem is still there.
这是我的程序流向崩溃点的方式: MainActivity->创建行程->显示为RecyclerView->单击以进入另一个活动(通过其trip_id)->创建位置->选择保存(trip_id，locationName，latLng)时崩溃
This is how my program flows until the point where it crashes: MainActivity -> Create Trip -> Shows up as a RecyclerView -> Click on it to enter another activity (passes its trip_id) -> Create a Location -> crashes when save is selected (trip_id, locationName, latLng)
在这种情况下，Trip拥有一个PK:trip_id，而Location则将其视为FK.
In this case, Trip has a PK: trip_id, while Location takes it as a FK.
Process: com.example.TravelPlanner, PID: 4701
java.lang.RuntimeException: An error occurred while executing doInBackground()
at android.os.AsyncTask$3.done(AsyncTask.java:354)
at java.util.concurrent.FutureTask.finishCompletion(FutureTask.java:383)
at java.util.concurrent.FutureTask.setException(FutureTask.java:252)
at java.util.concurrent.FutureTask.run(FutureTask.java:271)
at android.os.AsyncTask$SerialExecutor$1.run(AsyncTask.java:245)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1167)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:641)
at java.lang.Thread.run(Thread.java:764)
Caused by: android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY[787])
at android.database.sqlite.SQLiteConnection.nativeExecuteForLastInsertedRowId(Native Method)
at android.database.sqlite.SQLiteConnection.executeForLastInsertedRowId(SQLiteConnection.java:995)
at android.database.sqlite.SQLiteSession.executeForLastInsertedRowId(SQLiteSession.java:788)
at android.database.sqlite.SQLiteStatement.executeInsert(SQLiteStatement.java:86)
at androidx.sqlite.db.framework.FrameworkSQLiteStatement.executeInsert(FrameworkSQLiteStatement.java:51)
at androidx.room.EntityInsertionAdapter.insert(EntityInsertionAdapter.java:64)
at com.example.travelplanner.LocationDao_Impl.insert(LocationDao_Impl.java:110)
at com.example.travelplanner.LocationRepository$InsertLocationAsyncTask.doInBackground(LocationRepository.java:48)
at com.example.travelplanner.LocationRepository$InsertLocationAsyncTask.doInBackground(LocationRepository.java:39)
at android.os.AsyncTask$2.call(AsyncTask.java:333)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
这是在onActivityResult方法的第二个活动中创建Location对象的行
This is the line to create the Location object in the 2nd activity in the onActivityResult method
String locationName = data.getStringExtra("locationName");
String latLng = data.getStringExtra("latLng");
Intent intent = getIntent();
Bundle bundle = intent.getExtras();
int tripId = (int)bundle.get("tripId"); // D/TRIPID: Trip id is 1
Log.d("TRIPID", "Trip id is " + tripId);
String[] latlong = latLng.split(",");
double latitude = Double.parseDouble(latlong[0]);
double longitude = Double.parseDouble(latlong[1]);
LatLng locationLatLng = new LatLng(latitude,longitude);
Location location = new Location(tripId, locationName, locationLatLng);
Log.d("LOCATIONID", "Trip id is " + location.getTripId()); // D/LOCATIONID: Trip id is 1
locationViewModel.insert(location); //crashes here
Location.class
Location.class
@Entity(tableName = "location_table",foreignKeys = @ForeignKey(entity = Trip.class, parentColumns = "location_Id", childColumns = "trip_Id"),
indices = {@Index(value = {"trip_Id"})})
public class Location {
@PrimaryKey(autoGenerate = true)
private int locationId;
@ColumnInfo (name = "trip_Id")
private int tripId;
private String locationName;
@TypeConverters(LatLngConverter.class)
private LatLng latLng;
public Location(int tripId, String locationName, LatLng latLng) {
this.tripId = tripId;
this.locationName = locationName;
this.latLng = latLng;
}
Trip.class
Trip.class
@Entity(tableName = "trip_table",
indices = {@Index(value = {"location_Id"},
unique = true)})
public class Trip {
@PrimaryKey(autoGenerate = true)
@ColumnInfo (name = "location_Id")
private int id;
private String title;
private String description;
private int priority;
//id will be auto generated so it need not be inside constructor
public Trip(String title, String description, int priority) {
this.title = title;
this.description = description;
this.priority = priority;
}
在将其添加到父表之前，我看不到它是如何添加外键的.
I fail to see how this is adding a foreign key before it is added in the parent table...
推荐答案
所显示的代码似乎没有问题，即使用您的代码(为方便起见进行了少许修改(对于latLng来说很长，并且在主线程上运行)
There appears to be nothing wrong with the code shown, that is using your code (slightly modified for convenience (just a long for latLng and run on main thread).
然后使用:-
appDatabase = Room.databaseBuilder(this,AppDatabase.class,"travel_planner")
.allowMainThreadQueries()
.build();
Trip trip1 = new Trip("A Trip","My first trip",1);
int tripID = (int) appDatabase.tripDao().insertTrip(trip1);
appDatabase.locationDao().insertLocation(new Location(tripID,"Here",100));
appDatabase.locationDao().insertLocation(new Location(tripID,"Here again",200));
List<Trip> tripList = appDatabase.tripDao().getAllTrips();
for (Trip t: tripList) {
Log.d("TRIPINFO","Trip is " + t.getDescription() + " ID is " + t.getId());
}
List<Location> locationList = appDatabase.locationDao().getAllLocations();
for (Location l: locationList) {
Log.d("LOCINFO","Location is " + l.getLocationName() + " ID is " + l.getLocationId() + " REFERENCES Trip " + l.getTripId());
}
(预期的)结果:-
2019-10-31 06:28:09.591 27335-27335/? D/TRIPINFO: Trip is My first trip ID is 1
2019- 10-31 06:28:09.593 27335-27335/? D/LOCINFO: Location is Here ID is 1 REFERENCES Trip 1
2019-10-31 06:28:09.593 27335-27335/? D/LOCINFO: Location is Here again ID is 2 REFERENCES Trip 1
即TripId 1 存在，并且已添加2个引用该Trip的位置.
i.e TripId 1 exists and 2 locations have been added referencing that Trip.
就这样
- //在日志中显示为1 不正确或
- 尝试添加位置时，旅程不存在.
我建议添加以下内容以进行调试:-
I'd suggest adding the following for debugging purposes :-
- 将以下内容添加到适当的Dao 中(以下内容使用TripDao，并且
abstract TripDao tripDao();在@Database类中编码(以下使用AppDatabase))
- add the following to the appropriate Dao (the following uses TripDao and that
abstract TripDao tripDao();is coded in the @Database class (AppDatabasewas used below))
:-
@Query("SELECT * FROM trip_table")
List<Trip> getAllTrips();
- 在
Location location = new Location(tripId, locationName, locationLatLng);行之后和插入之前添加
- after Line
Location location = new Location(tripId, locationName, locationLatLng);and before the insert add
:-
List<Trip> tripList = appDatabase.tripDao().getAllTrips();
for (Trip t: tripList) {
Log.d("TRIPINFO","Trip is " + t.getDescription() + " ID is " + t.getId());
}
Log.d("LOCINFO","Location is " + location.getLocationName() + " ID is " + location.getLocationId() + " REFERENCES Trip " + location.getTripId());
这将列出当前行程和要添加的位置.假设失败将是Trip ID与REFERENCES跳闸值之间的差异.如果Trip不如预期的那样存在，则由于某种原因，它不会被添加，否则将传递不正确的TripId.以上应该有助于确定哪个.
This will the list the current Trips and the location that is to be added. Assuming a failure then would be a discrepancy between the Trip id's and the REFERENCES trip value. If the Trip doesn't exist as expected then for some reason it is not being added otherwise the incorrect TripId is being passed. The above should help to establish which.
这篇关于会议室数据库-外键约束失败，错误代码787的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
