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. For more information, see Adding or Removing Foreign Keys.
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.
 |
When a cascade delete service is used to delete an entity, the deleteReference input parameter on the service determines whether the deleteReference settings in the database information for the entity are followed. When the deleteReference input parameter is set to true, all entities referenced by foreign keys which have a deleteReference setting of true are deleted. When the deleteReference input parameter is set to false, the referenced entities are not deleted.
|
For example, a repeating calendar day (PTC.Shift.CalendarDayRepeating) is a relationship between a calendar and a shift schedule. This is defined on the repeating calendar day with foreign key fields that reference the UIDs of the calendar (CalendarUID) and the shift schedule (ShiftScheduleUID). The following code is from the database information specified for the repeating calendar day Data Shape (PTC.Shift.CalendarDayRepeating) in the GetDBInfo service on the PTC.ShiftImpl.Manager Thing.
foreignKeys: [{
name: "calendarUid",
referenceDataShapeName: "PTC.Shift.Calendar",
referenceFieldName: "uid",
onDelete: "CASCADE"
}, {
name: "shiftScheduleUid",
referenceDataShapeName: "PTC.Shift.ShiftSchedule",
referenceFieldName: "uid",
deleteReference: true
}, {
name: "siteUid",
referenceDataShapeName: "PTC.MfgModel.Site",
referenceFieldName: "uid"
}]
name: "calendarUid",
referenceDataShapeName: "PTC.Shift.Calendar",
referenceFieldName: "uid",
onDelete: "CASCADE"
}, {
name: "shiftScheduleUid",
referenceDataShapeName: "PTC.Shift.ShiftSchedule",
referenceFieldName: "uid",
deleteReference: true
}, {
name: "siteUid",
referenceDataShapeName: "PTC.MfgModel.Site",
referenceFieldName: "uid"
}]
Because onDelete is set to CASCADE for the calendarUid foreign key, if the referenced calendar is deleted, then the repeating calendar day is also deleted. This ensures referential integrity; the repeating calendar day cannot exist unless the calendar is present.
Because deleteReference is set to true for the foreign key on the shiftScheduleUid field, if the repeating calendar day is deleted, the shift schedule referenced by the shiftScheduleUid field is also to be deleted. This behavior is determined by a business decision specifying that if the repeating calendar day is deleted, then the shift schedule it relates to should 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.
|
Delete Services and Foreign Key Delete Behavior
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.DBConnection.DBManagementThingShape). These cascade delete 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.
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. An exception to this is the DeleteSite service on the PTC.MfgModelImpl.Manager Thing which calls the CascadeDeleteModel service.