To prevent the deletion of a parent row which has one or more related child rows in my Room database, I've set my ForeignKey onDelete method to RESTRICT.
My database has two tables: products and document_products which has the ForeignKey on products, during the application usage, the user is able to delete all items from the products table but I need to still keep the items in document_products but the RESTRICT seems not to be working as even with it I'm getting:
FOREIGN KEY constraint failed (code 1811 SQLITE_CONSTRAINT_TRIGGER)
My DocumentProduct entity looks like this:
@JsonClass(generateAdapter = true)
@Entity(
tableName = "document_products",
foreignKeys = [
ForeignKey(
entity = Document::class,
parentColumns = ["id"],
childColumns = ["documentId"],
onDelete = CASCADE
),
ForeignKey(
entity = Product::class,
parentColumns = ["products_id"],
childColumns = ["document_products_productIdMap"],
onDelete = RESTRICT
)
],
indices = [Index("document_products_productIdMap"), Index("documentId"), Index(
value = ["document_products_productIdMap", "documentId", "labelType"],
unique = true
)]
)
data class DocumentProduct(
@PrimaryKey(autoGenerate = true)
@ColumnInfo(name = "document_products_id")
var id: Long,
@ColumnInfo(name = "document_products_productIdMap")
var productId: String,
@ColumnInfo(name = "document_products_quantity")
var quantity: Float,
var orderQuantity: Float,
@ColumnInfo(name = "document_products_purchase")
var purchase: Float,
var documentId: Long,
var labelType: String?,
var timestamp: Long?
)
While Product:
@Entity(tableName = "products")
open class Product(
@PrimaryKey(autoGenerate = false)
@ColumnInfo(name = "products_id")
open var id: String,
open var description: String?,
@ColumnInfo(defaultValue = "PZ")
open var unitOfMeasure: String,
@ColumnInfo(name = "products_purchase")
open var purchase: Float,
open var price: Float,
@ColumnInfo(name = "products_quantity")
open var quantity: Float
)
And in the application settings the user is able to run the following query from ProductDAO:
@Query("DELETE FROM products")
suspend fun deleteAll(): Int
What I'm looking for is a solution in which I can keep the parent rows which has one or more related child rows OR where I can keep the ForeignKey without a real relation.
CodePudding user response：
RESTRICT is working as intended, it is not meant to exit quietly and leave things as before. Rather it is used to immediately exit rather than at the end of the current statement as per:-
RESTRICT: The "RESTRICT" action means that the application is prohibited from deleting (for ON DELETE RESTRICT) or modifying (for ON UPDATE RESTRICT) a parent key when there exists one or more child keys mapped to it. The difference between the effect of a RESTRICT action and normal foreign key constraint enforcement is that the RESTRICT action processing happens as soon as the field is updated - not at the end of the current statement as it would with an immediate constraint, or at the end of the current transaction as it would with a deferred constraint. Even if the foreign key constraint it is attached to is deferred, configuring a RESTRICT action causes SQLite to return an error immediately if a parent key with dependent child keys is deleted or modified.
You could simply not use Foreign Keys they are not mandatory for a relationship to exist. They are to enforce referential integrity.
An alternative approach with referential integrity would be to have the Products and DocumentsProducts independent relationships wise (i.e. drop the Foregin Keys and the productId column) and to then have an table for any relationships this catering for a many-many relationship between Products and DocumentProducts (which inherently supports 1-many and 1-1).
Such a table (a mapping table/crossref table/associative table ....) would have 2 columns one for the reference/map/association with the Product, the other for the DocumentProduct. You could have 2 Foreign Keys and also you could CASCADE for when a deletion happens.
The Delete (and update if coded) would CASCADE to this table not to the Product or the DocumentProduct, thus just removing cross reference between the two.
The Primary Key would be a composite of the two columns, you would have to use the primaryKey parameter of the @Entity annotation to define this.
The following code is along the lines of what would suit:-
@Entity(
tableName = "document_products",/*
foreignKeys = [
ForeignKey(
entity = Document::class,
parentColumns = ["id"],
childColumns = ["documentId"],
onDelete = CASCADE
),
ForeignKey(
entity = Product::class,
parentColumns = ["products_id"],
childColumns = ["document_products_productIdMap"],
onDelete = RESTRICT
)
],*/
indices = [/*Index("document_products_productIdMap"),*/ Index("documentId"), Index(
value = [/*"document_products_productIdMap",*/ "documentId", "labelType"],
unique = true
)]
)
data class DocumentProduct(
@PrimaryKey(autoGenerate = true)
@ColumnInfo(name = "document_products_id")
var id: Long,
//@ColumnInfo(name = "document_products_productIdMap")
//var productId: String,
@ColumnInfo(name = "document_products_quantity")
var quantity: Float,
var orderQuantity: Float,
@ColumnInfo(name = "document_products_purchase")
var purchase: Float,
var documentId: Long,
var labelType: String?,
var timestamp: Long?
)
@Entity(tableName = "products")
open class Product(
@PrimaryKey(autoGenerate = false)
@ColumnInfo(name = "products_id")
open var id: String,
open var description: String?,
@ColumnInfo(defaultValue = "PZ")
open var unitOfMeasure: String,
@ColumnInfo(name = "products_purchase")
open var purchase: Float,
open var price: Float,
@ColumnInfo(name = "products_quantity")
open var quantity: Float
)
@Entity(
primaryKeys = ["productIdMap","documentProductIdMap"],
foreignKeys = [
ForeignKey(
entity = Product::class,
parentColumns = ["products_id"],
childColumns = ["productIdMap"],
onDelete = ForeignKey.CASCADE,
onUpdate = ForeignKey.CASCADE
),
ForeignKey(
entity = DocumentProduct::class,
parentColumns = ["document_products_id"],
childColumns = ["documentProductIdMap"],
onDelete = ForeignKey.CASCADE,
onUpdate = ForeignKey.CASCADE
)
]
)
data class ProductDocumentProductMap(
val productIdMap: Long,
@ColumnInfo(index = true)
var documentProductIdMap: Long
)
- note commenting out code has been used to indicate code that isn't needed or must be changed to suit.