Updating MySQL Schema to CASCADE on DELETE


#1

I appreciate your help as I’m new to working with DF. I’m running into an issue where I’m unable to PATCH a table to update the foreign key relationships. I’ve been working on this for a few hours without success, so I’m certain, I’m either missing a step or performing the task completely incorrectly.

Here’s the scoop. I have 2 tables: person, personLog. The personLog table uses the personId (primary key for the person table) as a reference to the person record. Unfortunately, when I set up the relationships, I didn’t provide a ref_on_delete or ref_on_update value, so they default to null.

When attempting to PATCH the table and provide these values as CASCADE, the attempt appears to succeed with an HTTP 200 status response. However, if I immediately pull the table schema again, the changes were not made. Here’s my personLog table schema and the PATCH body that’s submitted for the change.

Thank you in advance for your help!

personLog schema:

{
“alias”: null,
“name”: “personLog”,
“is_view”: false,
“label”: “Person Log”,
“plural”: “Person Logs”,
“description”: “This is the resource that manages the log for a person.”,
“primary_key”: “personLogId”,
“name_field”: null,
“field”: [
{
“alias”: null,
“name”: “personLogId”,
“label”: “Person Log ID”,
“description”: “Primary Key for person activity records.”,
“type”: “id”,
“db_type”: “int(11)”,
“length”: 11,
“precision”: null,
“scale”: null,
“default”: null,
“required”: false,
“allow_null”: false,
“fixed_length”: false,
“supports_multibyte”: false,
“auto_increment”: true,
“is_primary_key”: true,
“is_unique”: false,
“is_index”: false,
“is_foreign_key”: false,
“is_virtual_foreign_key”: false,
“is_foreign_ref_service”: false,
“ref_service”: null,
“ref_service_id”: null,
“ref_table”: null,
“ref_fields”: null,
“ref_on_update”: null,
“ref_on_delete”: null,
“picklist”: null,
“validation”: null,
“db_function”: null
},
{
“alias”: null,
“name”: “personId”,
“label”: “Person ID”,
“description”: null,
“type”: “reference”,
“db_type”: “int(11)”,
“length”: 11,
“precision”: null,
“scale”: null,
“default”: null,
“required”: true,
“allow_null”: false,
“fixed_length”: false,
“supports_multibyte”: false,
“auto_increment”: false,
“is_primary_key”: false,
“is_unique”: false,
“is_index”: true,
“is_foreign_key”: true,
“is_virtual_foreign_key”: false,
“is_foreign_ref_service”: false,
“ref_service”: null,
“ref_service_id”: null,
“ref_table”: “person”,
“ref_fields”: “personId”,
“ref_on_update”: null,
“ref_on_delete”: null,
“picklist”: null,
“validation”: {
“not_empty”: {
“on_fail”: “This record must be linked to a person.”
}
},
“db_function”: null
},
{
“alias”: null,
“name”: “personLogTime”,
“label”: “Log Time”,
“description”: null,
“type”: “timestamp_on_create”,
“db_type”: “timestamp”,
“length”: null,
“precision”: null,
“scale”: null,
“default”: {
“expression”: “CURRENT_TIMESTAMP”
},
“required”: false,
“allow_null”: false,
“fixed_length”: false,
“supports_multibyte”: false,
“auto_increment”: false,
“is_primary_key”: false,
“is_unique”: false,
“is_index”: false,
“is_foreign_key”: false,
“is_virtual_foreign_key”: false,
“is_foreign_ref_service”: false,
“ref_service”: null,
“ref_service_id”: null,
“ref_table”: null,
“ref_fields”: null,
“ref_on_update”: null,
“ref_on_delete”: null,
“picklist”: null,
“validation”: {
“not_empty”: {
“on_fail”: “You must provide a time for this log event.”
}
},
“db_function”: null
},
{
“alias”: null,
“name”: “personLogUserId”,
“label”: “User ID”,
“description”: null,
“type”: “user_id_on_create”,
“db_type”: “int(11)”,
“length”: 11,
“precision”: null,
“scale”: null,
“default”: null,
“required”: true,
“allow_null”: false,
“fixed_length”: false,
“supports_multibyte”: false,
“auto_increment”: false,
“is_primary_key”: false,
“is_unique”: false,
“is_index”: false,
“is_foreign_key”: false,
“is_virtual_foreign_key”: false,
“is_foreign_ref_service”: false,
“ref_service”: null,
“ref_service_id”: null,
“ref_table”: null,
“ref_fields”: null,
“ref_on_update”: null,
“ref_on_delete”: null,
“picklist”: null,
“validation”: {
“not_empty”: {
“on_fail”: “You must provide a user id for this log event.”
}
},
“db_function”: null
},
{
“alias”: null,
“name”: “personLogText”,
“label”: “Log Event”,
“description”: “Log text”,
“type”: “text”,
“db_type”: “text”,
“length”: null,
“precision”: null,
“scale”: null,
“default”: null,
“required”: true,
“allow_null”: false,
“fixed_length”: false,
“supports_multibyte”: false,
“auto_increment”: false,
“is_primary_key”: false,
“is_unique”: false,
“is_index”: false,
“is_foreign_key”: false,
“is_virtual_foreign_key”: false,
“is_foreign_ref_service”: false,
“ref_service”: null,
“ref_service_id”: null,
“ref_table”: null,
“ref_fields”: null,
“ref_on_update”: null,
“ref_on_delete”: null,
“picklist”: null,
“validation”: {
“not_empty”: {
“on_fail”: “You must provide log text for this log event.”
}
},
“db_function”: null
}
],
“related”: [
{
“alias”: null,
“name”: “person_by_personId”,
“label”: “Person By PersonId”,
“description”: null,
“always_fetch”: false,
“flatten”: false,
“flatten_drop_prefix”: false,
“type”: “belongs_to”,
“field”: “personId”,
“is_virtual”: false,
“is_foreign_service”: false,
“ref_service”: null,
“ref_service_id”: null,
“ref_table”: “person”,
“ref_fields”: “personId”,
“ref_on_update”: null,
“ref_on_delete”: null,
“is_foreign_junction_service”: false,
“junction_service”: null,
“junction_service_id”: null,
“junction_table”: null,
“junction_field”: null,
“junction_ref_field”: null
}
],
“access”: 31
}

Here is the PATCH post body:

{
“alias”: null,
“name”: “personLog”,
“is_view”: false,
“label”: “Person Log”,
“plural”: “Person Logs”,
“description”: “This is the resource that manages the log for a person.”,
“primary_key”: “personLogId”,
“name_field”: null,
“field”: [
{
“name”: “personId”,
“label”: “Person ID”,
“type”: “reference”,
“ref_table”: “person”,
“ref_fields”: “personId”,
“ref_on_update”: “CASCADE”,
“ref_on_delete”: “CASCADE”,
“validation”: {
“not_empty”: {
“on_fail”: “This record must be linked to a person.”
}
}
}
]
}


#2

For reference, and I’m not sure if this is a bug or by design, so I’d like to have some feedback, if possible. On a PATCH or PUT, I was unable to update the ref_on_update/delete to CASCADE; however, if I deleted the table and used the same schema, it correctly saved the value.