会议室数据库:NOT NULL约束在删除时失败 [英] Room database: NOT NULL constraint failed at deletion
问题描述
我有以下实体Session
和Label
:
@Entity(
foreignKeys =
@ForeignKey(
entity = Label.class,
parentColumns = {"id", "archived"},
childColumns = {"labelId", "archived"},
onUpdate = CASCADE,
onDelete = SET_DEFAULT))
public class Session {
@PrimaryKey(autoGenerate = true)
public long id;
@Nullable
public String label = null;
public boolean archived = false;
}
@Entity(primaryKeys = {"id", "archived"})
public class Label {
@NonNull
public String id;
public boolean archived = false;
}
在删除附加到Session
的Label
时,我得到的是Session.archived
的NOT NULL constraint failed
.
我在做什么错了?
我认为问题出在使用
由于 boolean 是Java主要类型,因此它具有隐式 @NonNull (表是使用NOT NULL约束创建的)
使用Room不能(我相信)设置SQLite表中生成的默认值,因此当使用onDelete SET_DEFAULT
时,默认值将为 NULL ,因为尚未设置实际的默认值./p>
-
注意Room现在使用支持默认值 @ColumnInfo 批注中的defaultValue().
-
使用
= false
不会影响实际的SQLite表,即,如果使用 Boolean ,则列定义将是labelId INTEGER
而不是labelId INTEGER DEFAULT 0
.
假设NULL是可接受的,则可以使用 Boolean (对象)而不是boolean(主要类型).对象可以为空.
即在会话中使用
public Boolean archived = false;
视觉外植:-
以下是Room为会话实体生成的代码,但使用:-
添加了附加列.........
public boolean archived = false;
public Boolean other_archived = false; //<<<<<<<<<< ADDED
........
房间生成的代码:-
CREATE TABLE IF NOT EXISTS `Session` (
`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
`label` TEXT,
`archived` INTEGER NOT NULL, /*<<<<<<<<<< boolean so NOT NULL */
`other_archived` INTEGER, /*<<<<<<<<<< Boolean so no NOT NULL */
FOREIGN KEY(`label`, `archived`) REFERENCES `Label`(`id`, `archived`) ON UPDATE CASCADE ON DELETE SET DEFAULT)
额外
如果您希望会话"表中的存档列为0(假),则必须修改该表.这将需要
- 创建一个替换表,除了标签列定义为
label INTEGER DEFAULT 0
(即添加DEFAULT 0)之外,其他操作均相同 - 将数据从会话"表复制到替换表中,例如
INSERT INTO replacement_Session SELECT * FROM Session;
- 重命名会话表.
- 将替换表重命名为Session.
- 如果高兴,请删除重命名的Session表.
或者(我认为),您可以使用AFTER UPDATE TRIGGER将NULL更改为0(假).
这两个选项都必须在Room外部或在RoomDatabase构建之前完成.
I have the following entities, Session
and Label
:
@Entity(
foreignKeys =
@ForeignKey(
entity = Label.class,
parentColumns = {"id", "archived"},
childColumns = {"labelId", "archived"},
onUpdate = CASCADE,
onDelete = SET_DEFAULT))
public class Session {
@PrimaryKey(autoGenerate = true)
public long id;
@Nullable
public String label = null;
public boolean archived = false;
}
@Entity(primaryKeys = {"id", "archived"})
public class Label {
@NonNull
public String id;
public boolean archived = false;
}
When deleting a Label
attached to a Session
I'm getting a NOT NULL constraint failed
for Session.archived
.
What am I doing wrong here?
I believe that the issue is with using a type of boolean as in public boolean archived = false;
.
As boolean is a Java primary type then it has an implicit @NonNull (the table is created with the NOT NULL constraint)
With Room you cannot (I beleive) set default values that are generated in the SQLite table so when using onDelete SET_DEFAULT
the default value will be NULL as no actual default value has been set.
Note Room does now support default values using defaultValue() within the @ColumnInfo annotation.
using
= false
does not affect the actual SQLite table i.e. the column definition, if using Boolean, will belabelId INTEGER
notlabelId INTEGER DEFAULT 0
.
Assuming that NULL is acceptable, then you can use Boolean (the Object) instead of boolean (the primary type). Objects can be null.
i.e. in Session use
public Boolean archived = false;
Visual explantion :-
The following is the code, generated by Room, for the Session Entity but with an additional column added using :-
.........
public boolean archived = false;
public Boolean other_archived = false; //<<<<<<<<<< ADDED
........
Code Generated by Room :-
CREATE TABLE IF NOT EXISTS `Session` (
`id` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
`label` TEXT,
`archived` INTEGER NOT NULL, /*<<<<<<<<<< boolean so NOT NULL */
`other_archived` INTEGER, /*<<<<<<<<<< Boolean so no NOT NULL */
FOREIGN KEY(`label`, `archived`) REFERENCES `Label`(`id`, `archived`) ON UPDATE CASCADE ON DELETE SET DEFAULT)
Extra
If you wanted the archived column in the Session table to be 0 (false), then you would have to modify the table. This would entail
- Creating a replacement table that is the same except that the label column definition would be
label INTEGER DEFAULT 0
(i.e. DEFAULT 0 is added) - Copying the data from the Session table into the replacement table e.g.
INSERT INTO replacement_Session SELECT * FROM Session;
- Rename the Session table .
- Rename the replacement table to Session.
- If happy Drop the renamed Session table.
Alternately (I think) you could use an AFTER UPDATE TRIGGER to change the NULL to 0 (false).
Either option would have to be done outside of Room or before the RoomDatabase has been built.
这篇关于会议室数据库:NOT NULL约束在删除时失败的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!