Setting the Delete Behavior with Foreign Keys
When the entity on either side of a foreign key relationship is deleted, you must specify what happens to the entity on the other side of the relationship. This ensures that referential integrity is maintained, and that any decisions based on business logic are followed.
The delete behavior is specified in the database information for a Data Shape, in the same array where the foreign keys are specified. The delete behavior, if any, is specified for each individual foreign key. In the following descriptions, A is the entity with the foreign key, and B is the entity with the field referenced by the foreign key.
• onDelete—Determines what happens to A when B is deleted.
◦ CASCADE—When B is deleted, A is also deleted. This is known as a cascade delete.
◦ SET_NULL—When B is deleted, the value of the foreign key field on A is set to null.
◦ If onDelete is not specified, nothing happens to A when B is deleted.
• deleteReference—Determines what happens to B when A is deleted.
◦ If true, then when A is deleted, B is also deleted.
◦ If false, or when not specified, nothing happens to B when A is deleted.
For example, a work definition link is a relationship between a parent work definition and a child work definition. This is defined on the work definition link with foreign key fields that reference the UIDs of the two work definitions (ParentUID and ChildUID). The following code is from the database information specified for the work definition link Data Shape (PTC.SCA.SCO.WorkDefinitionLink) in the GetWorkDefinitionDBInfo service on PTC.SCA.SCO.DefaultProductionOrderManager.
"foreignKeys": [{
"name": "ChildUID",
"referenceDataShapeName": "PTC.SCA.SCO.WorkDefinition",
"referenceFieldName": "UID",
"onDelete": "CASCADE",
"deleteReference": true
}, {
"name": "ParentUID",
"referenceDataShapeName": "PTC.SCA.SCO.WorkDefinition",
"referenceFieldName": "UID",
"onDelete": "CASCADE"
}]
"name": "ChildUID",
"referenceDataShapeName": "PTC.SCA.SCO.WorkDefinition",
"referenceFieldName": "UID",
"onDelete": "CASCADE",
"deleteReference": true
}, {
"name": "ParentUID",
"referenceDataShapeName": "PTC.SCA.SCO.WorkDefinition",
"referenceFieldName": "UID",
"onDelete": "CASCADE"
}]
Because onDelete is set to CASCADE for both of the foreign keys, if either of the referenced work definitions is deleted, then the work definition link is also deleted. This ensures referential integrity; the work definition link cannot exist unless the work definition on each side of the relationship is present.
Because deleteReference is set to true for the foreign key on the ChildUID field, if the work definition link is deleted, the work definition referenced by the ChildUID field is also deleted. This behavior is determined by a business decision specifying that if the link between a parent and child work definition is deleted, then the child work definition must also be deleted.
 |
The delete behaviors do not need to be specified before synchronizing with the database to add a new Data Shape or foreign key to the database schema. As a best practice, the delete behaviors should be specified before any instances of the Data Shapes on either side of the foreign key relationship are deleted.
|
The services used to perform deletions, while satisfying the onDelete and deleteReference settings for each foreign key, are found on the database management Thing Shape (PTC.SCA.SCO.DBManagementThingShape). These services are BatchCascadeDelete, CascadeDelete, and CollectActionForCascadeDelete. All managers extend from this Thing Shape, so they inherit these services. For more information, see Database Management Thing Shape Services
Some entities in the Operator Advisor data model (work definition related documents and work definition illustrations) also reference files that are stored in the ThingWorx file repository. The BatchCascadeDelete service on the job order manager Thing Template (PTC.SCA.SCO.JobOrderManagerThingTemplate) has been overridden so that those referenced files are also deleted from the file repository when the entities that reference them are deleted.
The standard delete actions for individual data model objects, such as DeleteShift or DeleteJobOrder delete only the specified entity. If deleting only that entity violates referential integrity, the service fails. In such cases, use the cascade delete services.