android - 如何在连接表中设置 Android 房间外键接受“零”值？
问题描述
问题：使用 Android Room，我在输入零 (0) 时在连接表上收到外键约束错误。我仍在尝试学习 Android Room，所以我确定我忽略了文档和教程中的某些内容。
我尝试过的：我确实在使用 SQLite 的桌面版本中有这个应用程序，我没有问题，并尝试将它设置为相同。我在这里和其他地方阅读了一些带有示例的帖子，甚至删除了将返回零的外键声明，但后来我遇到了模式开始完全在不同表上触发的困难。我尝试了默认值，但设置为 null 但似乎无法使其正常工作。在发这篇文章时，我仍然会重新阅读一些内容，但我确信我只是忽略了一些东西——旧的 80/20 规则。
我正在尝试做的事情：这个连接表从 6 个表中捕获 id。但是，根据用户条目，有 3 个外键，用户可能没有添加任何要引用的内容。由于 Android Room 本质上是为了方便使用而包装了 SQLite，所以到目前为止，我还无法确定如何让它接受外键上的零值。我想过尝试OnConflict注释，但想寻求一些想法和建议。
实体
@Entity(tableName = "Notes", foreignKeys = {
@ForeignKey(entity = Sources.class, parentColumns = "SourceID", childColumns = "SourceID"),
@ForeignKey(entity = Comments.class, parentColumns = "CommentID", childColumns = "CommentID"),
@ForeignKey(entity = Questions.class, parentColumns = "QuestionID", childColumns = "QuestionID"),
@ForeignKey(entity = Quotes.class, parentColumns = "QuoteID", childColumns = "QuoteID"),
@ForeignKey(entity = Terms.class, parentColumns = "TermID", childColumns = "TermID"),
@ForeignKey(entity = Topics.class, parentColumns = "TopicID", childColumns = "TopicID")},
indices = {@Index("SourceID"), @Index("CommentID"), @Index("QuestionID"), @Index("QuoteID"),
@Index("TermID"), @Index("TopicID")})
public class Notes {
@PrimaryKey(autoGenerate = true)
@ColumnInfo(name = "NoteID")
private int noteID;
@ColumnInfo(name = "SourceID")
private int sourceID;
@ColumnInfo(name = "CommentID")
private int commentID;
@ColumnInfo(name = "QuestionID", defaultValue = "0")
private int questionID;
@ColumnInfo(name = "QuoteID", defaultValue = "0")
private int quoteID;
@ColumnInfo(name = "TermID", defaultValue = "0")
private int termID;
@ColumnInfo(name = "TopicID")
private int topicID;
@ColumnInfo(name = "Deleted", defaultValue = "0")
private int deleted;
public Notes(int noteID, int sourceID, int commentID, int questionID, int quoteID, int termID, int topicID, int deleted){
this.noteID = noteID;
this.sourceID = sourceID;
this.commentID = commentID;
this.questionID = questionID;
this.quoteID = quoteID;
this.termID = termID;
this.topicID = topicID;
this.deleted = deleted;
}
架构部分
"tableName": "Notes",
"createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`NoteID` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `SourceID` INTEGER NOT NULL, `CommentID` INTEGER NOT NULL, `QuestionID` INTEGER NOT NULL DEFAULT 0, `QuoteID` INTEGER NOT NULL DEFAULT 0, `TermID` INTEGER NOT NULL DEFAULT 0, `TopicID` INTEGER NOT NULL, `Deleted` INTEGER NOT NULL DEFAULT 0, FOREIGN KEY(`SourceID`) REFERENCES `Sources`(`SourceID`) ON UPDATE NO ACTION ON DELETE NO ACTION , FOREIGN KEY(`CommentID`) REFERENCES `Comments`(`CommentID`) ON UPDATE NO ACTION ON DELETE NO ACTION , FOREIGN KEY(`QuestionID`) REFERENCES `Questions`(`QuestionID`) ON UPDATE NO ACTION ON DELETE NO ACTION , FOREIGN KEY(`QuoteID`) REFERENCES `Quotes`(`QuoteID`) ON UPDATE NO ACTION ON DELETE NO ACTION , FOREIGN KEY(`TermID`) REFERENCES `Terms`(`TermID`) ON UPDATE NO ACTION ON DELETE NO ACTION , FOREIGN KEY(`TopicID`) REFERENCES `Topics`(`TopicID`) ON UPDATE NO ACTION ON DELETE NO ACTION )",
"fields": [
{
"fieldPath": "noteID",
"columnName": "NoteID",
"affinity": "INTEGER",
"notNull": true
},
{
"fieldPath": "sourceID",
"columnName": "SourceID",
"affinity": "INTEGER",
"notNull": true
},
{
"fieldPath": "commentID",
"columnName": "CommentID",
"affinity": "INTEGER",
"notNull": true
},
{
"fieldPath": "questionID",
"columnName": "QuestionID",
"affinity": "INTEGER",
"notNull": true,
"defaultValue": "0"
},
{
"fieldPath": "quoteID",
"columnName": "QuoteID",
"affinity": "INTEGER",
"notNull": true,
"defaultValue": "0"
},
{
"fieldPath": "termID",
"columnName": "TermID",
"affinity": "INTEGER",
"notNull": true,
"defaultValue": "0"
},
{
"fieldPath": "topicID",
"columnName": "TopicID",
"affinity": "INTEGER",
"notNull": true
},
{
"fieldPath": "deleted",
"columnName": "Deleted",
"affinity": "INTEGER",
"notNull": true,
"defaultValue": "0"
}
],
"primaryKey": {
"columnNames": [
"NoteID"
],
"autoGenerate": true
},
"indices": [
{
"name": "index_Notes_SourceID",
"unique": false,
"columnNames": [
"SourceID"
],
"createSql": "CREATE INDEX IF NOT EXISTS `index_Notes_SourceID` ON `${TABLE_NAME}` (`SourceID`)"
},
{
"name": "index_Notes_CommentID",
"unique": false,
"columnNames": [
"CommentID"
],
"createSql": "CREATE INDEX IF NOT EXISTS `index_Notes_CommentID` ON `${TABLE_NAME}` (`CommentID`)"
},
{
"name": "index_Notes_QuestionID",
"unique": false,
"columnNames": [
"QuestionID"
],
"createSql": "CREATE INDEX IF NOT EXISTS `index_Notes_QuestionID` ON `${TABLE_NAME}` (`QuestionID`)"
},
{
"name": "index_Notes_QuoteID",
"unique": false,
"columnNames": [
"QuoteID"
],
"createSql": "CREATE INDEX IF NOT EXISTS `index_Notes_QuoteID` ON `${TABLE_NAME}` (`QuoteID`)"
},
{
"name": "index_Notes_TermID",
"unique": false,
"columnNames": [
"TermID"
],
"createSql": "CREATE INDEX IF NOT EXISTS `index_Notes_TermID` ON `${TABLE_NAME}` (`TermID`)"
},
{
"name": "index_Notes_TopicID",
"unique": false,
"columnNames": [
"TopicID"
],
"createSql": "CREATE INDEX IF NOT EXISTS `index_Notes_TopicID` ON `${TABLE_NAME}` (`TopicID`)"
}
],
"foreignKeys": [
{
"table": "Sources",
"onDelete": "NO ACTION",
"onUpdate": "NO ACTION",
"columns": [
"SourceID"
],
"referencedColumns": [
"SourceID"
]
},
{
"table": "Comments",
"onDelete": "NO ACTION",
"onUpdate": "NO ACTION",
"columns": [
"CommentID"
],
"referencedColumns": [
"CommentID"
]
},
{
"table": "Questions",
"onDelete": "NO ACTION",
"onUpdate": "NO ACTION",
"columns": [
"QuestionID"
],
"referencedColumns": [
"QuestionID"
]
},
{
"table": "Quotes",
"onDelete": "NO ACTION",
"onUpdate": "NO ACTION",
"columns": [
"QuoteID"
],
"referencedColumns": [
"QuoteID"
]
},
{
"table": "Terms",
"onDelete": "NO ACTION",
"onUpdate": "NO ACTION",
"columns": [
"TermID"
],
"referencedColumns": [
"TermID"
]
},
{
"table": "Topics",
"onDelete": "NO ACTION",
"onUpdate": "NO ACTION",
"columns": [
"TopicID"
],
"referencedColumns": [
"TopicID"
]
}
]
},
解决方案
@ForeginKey是添加 SQLiteFOREIGN KEY子句的注释。SQLiteFOREIGN KEY子句是一个规则（约束），它表示列的值必须是指定的表/列中的现有值。如果相关表中没有 0 值，则无法插入该行，而是发生冲突。
FOREIGN KEY不需要形成关系，而是用于确保引用完整性，即没有孤立的行。
如果您必须有 0 表示没有相关项目（不是真正需要），那么您有两个选择。
- 通过不是真的需要查看示例/演示，输出包括 0 个关系。
选项是： -
- 不要使用外键，因此不存在阻止 0（或使用 null）的规则，或者
- 或者在相关表中有一行有 0 相应地处理。
我想过尝试一个 OnConflict 注释，但想寻求一些想法和建议。
Using@OnConflict不适用于外键冲突，但适用于由违反唯一、非空、检查和主键约束引起的冲突。
我尝试了默认值，但设置为 null 但似乎无法使其正常工作。
此外，使用@ColumnInfo'sdefaultValue不会使用 @Insert 插入默认值，因为始终为所有列提供值（在某些情况下禁止主键）。要defaultValue应用，您需要使用一个@Query使用INSERT INTO the_table (csv_list_of_columns) VALUES(csv_list_of_values_for_each_column)where 要默认的列不在列列表中的位置，因此没有相应的值（因此将使用默认值）。
工作示例/演示（只是为了简洁注意>来源的一种关系）
该示例使用了两种解决方案，第一种（实体 NotesV1/SourceV1）不使用外键，第二种使用（NotesV2/SourceV2）。两种解决方案都允许空值，后者利用 SQLite 中外键的空值处理：-
有一个例外：如果track表中的外键列为NULL，则不需要artist表中的对应条目。https://sqlite.org/foreignkeys.html
还应该注意使用对象而不是原语所以Integer而不是int。但是，由于 id 的签名高达 64 位，我使用了 Long/long。使用 Long/Integer 允许 null，原语不能为 null。
我还添加了一个名称列以允许呈现描述性。
所以两组实体：-
注释V1：-
@Entity(
indices = {
@Index("SourceIDMap")
}
)
class NotesV1 {
@PrimaryKey
@ColumnInfo(name = "NoteID")
Long noteId = null;
@ColumnInfo(name = "SourceIDMap",defaultValue = "0")
Long sourceId = null;
@ColumnInfo(name = "SourceName",defaultValue = "Not Given")
String noteName;
NotesV1(){}
@Ignore
NotesV1(Long noteId, Long sourceId, String noteName) {
this.noteId = noteId;
this.sourceId = sourceId;
this.noteName = noteName;
}
@Ignore
NotesV1(long sourceId, String noteName) {
this(null,sourceId,noteName);
}
@Ignore
NotesV1(String noteName) {
this(null,null,noteName);
}
}
- 被忽略的构造函数提供了更多的灵活性
NotesV2（带有外键）：-
@Entity(
foreignKeys = {
@ForeignKey(entity = SourceV2.class,parentColumns = {"SourceID"}, childColumns = {"SourceIDMap"})
},
indices = {
@Index("SourceIDMap")
}
)
class NotesV2 {
@PrimaryKey
@ColumnInfo(name = "NoteID")
Long noteId = null;
// suggest to always have unique column names so SourceIDMap will map to SourceID
@ColumnInfo(name = "SourceIDMap",defaultValue = "0")
Long sourceId = null;
@ColumnInfo(name = "SourceName",defaultValue = "Not Given")
String noteName;
}
- 没有添加额外的被忽略的构造函数
源V1
@Entity
class SourceV1 {
@PrimaryKey
@ColumnInfo(name = "SourceID")
Long sourceId = null;
String sourceName;
}
SourceV2（禁止类名与 SourceV1 相同）：-
@Entity
class SourceV2 {
@PrimaryKey
@ColumnInfo(name = "SourceID")
Long sourceId = null;
String sourceName;
}
@Relation通过满足Notes 和 Source 之间的 Join ( ) 关系的两个 POJOS 来显示结果：-
NotesV1WithSources
class NotesV1WithSources {
@Embedded
NotesV1 notesV1;
@Relation(
entity = SourceV1.class,parentColumn = "SourceIDMap",entityColumn = "SourceID"
)
List<SourceV1> sourceV1List;
}
和基本相同的NotesV2WithSources：-
class NotesV2WithSources {
@Embedded
NotesV2 notesV2;
@Relation(
entity = SourceV2.class,parentColumn = "SourceIDMap",entityColumn = "SourceID"
)
List<SourceV2> sourceV2List;
}
单个@DAO类AllDao （注意包括defaultValue仅用于 NotesV1 的插入查询）：-
@Dao
abstract class AllDao {
@Insert(onConflict = OnConflictStrategy.IGNORE)
abstract long insert(NotesV1 notesV1);
@Insert(onConflict = OnConflictStrategy.IGNORE)
abstract long insert(NotesV2 notesV2);
@Insert(onConflict = OnConflictStrategy.IGNORE)
abstract long insert(SourceV1 sourceV1);
@Insert(onConflict = OnConflictStrategy.IGNORE)
abstract long insert(SourceV2 sourceV2);
/* Using @ColumnInfo's defaultValue examples */
@Query("INSERT INTO notesV1 (SourceIdMap) VALUES(:sourceId)")
abstract void insertNotesV1OnlyBySourceId(long sourceId);
@Query("INSERT INTO notesV1 (SourceName) VALUES(:sourceName)")
abstract void insertNotesV1OnlyBySourceName(String sourceName);
@Transaction
@Query("SELECT * FROM notesv1")
abstract List<NotesV1WithSources> getAllNotesV1WithSources();
@Transaction
@Query("SELECT * FROM notesv2")
abstract List<NotesV2WithSources> getAllNotesV2WithSources();
}
一个非常标准/简单的@Database 类TheDatabase：-
@Database(entities = {NotesV1.class,NotesV2.class,SourceV1.class,SourceV2.class},version = 1)
abstract class TheDatabase extends RoomDatabase {
abstract AllDao getAllDao();
private static volatile TheDatabase instance = null;
public static TheDatabase getInstance(Context context) {
if (instance == null) {
instance = Room.databaseBuilder(context,TheDatabase.class,"notes.db")
.allowMainThreadQueries()
.build();
}
return instance;
}
}
最后一个使用/演示上述内容的活动MainActivity：-
public class MainActivity extends AppCompatActivity {
TheDatabase db;
AllDao dao;
private static final String TAGV1 = "NOTESV1INFO";
private static final String TAGV2 = "NOTESV2INFO";
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
db = TheDatabase.getInstance(this);
dao = db.getAllDao();
/* Without Foreign keys and shows defaultValue */
SourceV1 s1 = new SourceV1();
s1.sourceName = "Source 1";
s1.sourceId = dao.insert(s1);
dao.insert(new NotesV1()); // All defaults
dao.insert(new NotesV1()); /// and again
dao.insert(new NotesV1("Just the Note Name")); // Via @Ignore'd constructor
dao.insert(new NotesV1(1000L,s1.sourceId,"All provided")); // Via @Ignore'd constrcutor
dao.insertNotesV1OnlyBySourceId(0); // Via @Query with 0 passed as SourceId (so name is default)
dao.insertNotesV1OnlyBySourceName("Again just the note name"); //Via @Query with name passed (so SourceId is 0)
for(NotesV1WithSources nv1: dao.getAllNotesV1WithSources()) {
Log.d(TAGV1,
"Note ID is " + nv1.notesV1.noteId +
" Name is " + nv1.notesV1.noteName +
" SourceID is " + nv1.notesV1.sourceId +
"\nSources(" + nv1.sourceV1List.size() + ") Are:-");
for (SourceV1 s: nv1.sourceV1List) {
Log.d(TAGV1,"\tSource ID is " + s.sourceId + " Source Name is " + s.sourceName );
}
}
/* With Foreign Keys */
// SPECIAL ROW 0 INDICATIVE OF NO RELATION
SourceV2 s2 = new SourceV2();
s2.sourceId = 0L;
s2.sourceName = "I SHOULD BE IGNORED";
Log.d(TAGV2,"1. Special Source row Inserted with ID = " + dao.insert(s2));
Log.d(TAGV2,"2. NotesV2 insert ID was " + dao.insert(new NotesV2()) + " (note -1 then not inserted)");
NotesV2 n2 = new NotesV2();
n2.sourceId = 0L;
n2.noteName ="references special source";
Log.d(TAGV2,"3. NotesV2 insert ID was " + dao.insert(n2) + " (note -1 then not inserted)");
/*
//OUCH NO such Source2 so Foreign Key conflict NOT trapped by IGNORE so abends
e.g. android.database.sqlite.SQLiteConstraintException: FOREIGN KEY constraint failed (code 787 SQLITE_CONSTRAINT_FOREIGNKEY)
n2.sourceId = 100L;
Log.d(TAGV2,"4. NotesV2 insert ID was " + dao.insert(n2) + " (note -1 then not inserted)");
*/
s2 = new SourceV2();
s2.sourceName = "I am a normal source";
long s2Id = -99;
Log.d(TAGV2,"5. Normal Source row Inserted with ID = " + (s2Id = dao.insert(s2)));
n2.sourceId = s2Id;
for(NotesV2WithSources nv2: dao.getAllNotesV2WithSources()) {
Log.d(TAGV2,
"Note ID is " + nv2.notesV2.noteId +
" Name is " + nv2.notesV2.noteName +
" SourceID is " + nv2.notesV2.sourceId +
"\nSources(" + nv2.sourceV2List.size() + ") Are:-");
for (SourceV2 s: nv2.sourceV2List) {
Log.d(TAGV2,"\tSource ID is " + s.sourceId + " Source Name is " + s.sourceName );
}
}
}
}
- 请注意，上述内容仅设计为运行一次。
- 为简洁起见在主线程上运行
- 前半部分使用 V1 后半部分使用 V2
- 请注意注释掉的插入会崩溃，因为 IGNORE 不会忽略外键约束冲突。
- 评论包括从此类崩溃中提取的内容
运行时输出是：-
D/NOTESV1INFO: Note ID is 1 Name is null SourceID is null
Sources(0) Are:-
D/NOTESV1INFO: Note ID is 2 Name is null SourceID is null
Sources(0) Are:-
D/NOTESV1INFO: Note ID is 3 Name is Just the Note Name SourceID is null
Sources(0) Are:-
D/NOTESV1INFO: Note ID is 1000 Name is All provided SourceID is 1
Sources(1) Are:-
D/NOTESV1INFO: Source ID is 1 Source Name is Source 1
D/NOTESV1INFO: Note ID is 1001 Name is Not Given SourceID is 0
Sources(0) Are:-
D/NOTESV1INFO: Note ID is 1002 Name is Again just the note name SourceID is 0
Sources(0) Are:-
D/NOTESV2INFO: 1. Special Source row Inserted with ID = 0
D/NOTESV2INFO: 2. NotesV2 insert ID was 1 (note -1 then not inserted)
D/NOTESV2INFO: 3. NotesV2 insert ID was 2 (note -1 then not inserted)
D/NOTESV2INFO: 5. Normal Source row Inserted with ID = 1
D/NOTESV2INFO: Note ID is 1 Name is null SourceID is null
Sources(0) Are:-
D/NOTESV2INFO: Note ID is 2 Name is references special source SourceID is 0
Sources(1) Are:-
D/NOTESV2INFO: Source ID is 0 Source Name is I SHOULD BE IGNORED
- 可以看出，关系的存在不需要外键（即 V1 根本没有定义 FK）。
- 也可以看出空值存在并且不会导致任何问题。
使用 Application Inspector（以前的 Database Inspector）然后：-
推荐阅读
- android - 如何在 EvaluateJavascript 中将参数作为带有换行符 \n 的字符串传递？
- angular - RouterLink 无法正常工作
- java - Java如何“覆盖”一个catch块
- jakarta-ee - 作为企业应用程序项目的一部分部署预构建的资源适配器存档
- circuit-sdk - Circuit REST API 中标记项目的限制
- linux - 杀死 Supervisord 不会杀死子进程
- excel - Excel - 用本地文件夹路径替换 Google 的云端硬盘超链接
- javascript - 使用 Node js 从 Web 下载文件并循环
- ios - 带有阿拉伯语文本输入的动态文本视图
- angular - angular 4 [selected] 显示空白选项而不是所选选项