Adding or Removing Foreign Keys
A foreign key is a field in one database table that references the primary key in another table. The foreign key is defined as a constraint on the related column in the database table.
With the foreign key constraint in place, the field must either be empty, or the value must point to an existing value for the primary key in the referenced table. For example, the SiteUID field on a shift is a foreign key that references the UID of a site. As a result, the value for the SiteUID on a shift must either be empty or match an existing UID value for a site.
The foreign key constraint also prevents the deletion of rows from the database table that are referenced by foreign key fields. For example, if a shift has a SiteUID value of 5, then because of the foreign key constraint on the SiteUID field references the UID on a site, the site with the UID value of 5 cannot be deleted.
Adding a Foreign Key
To add a foreign key to an existing database table:
1. Navigate to the appropriate manager for the ThingWorx entity that is related to the database table. These managers are specified in the ManagerConfigurationSettings table on the Configuration page of the launch point configuration Thing (PTC.Factory.C_LaunchPointConfigurationThing_[ReleaseVersion]). For example, the manager for the Operator Advisor entities is the PTC.SCA.SCO.DefaultProductionOrderManager.
2. Under Services, find and override the Get<entity>DBInfo service for the entity to which you are adding the foreign key. For example, to add a foreign key to the database table for job orders, find and override the GetJobOrderDBInfo service.
3. In the script editor, scroll down to the entry for the Data Shape to which you want to add the foreign key.
4. Add a foreignKeys array for the Data Shape, with the following properties:
◦ identifier—The name of the entity in the database. If not specified, then the system automatically generates the value in the format <table_name>_<column_name>_fk. If specified, then the value must be unique both for specified values and for any automatically generated values. The maximum length for the value is the maximum length allowed by the database for identifiers.
◦ name—Name of the field on the current Data Shape to be a foreign key. The base type of the foreign key field should match the base type of the referenced field. For example, if the referenced field has the STRING base type, the foreign key field should also have a STRING base type.
◦ referenceDataShapeName—The Data Shape of the referenced database table.
◦ referenceFieldName—The name of the field containing the value being referenced.
◦ onDelete—The impact on instances of the current Data Shape when instances of the referenced Data Shape are deleted.
◦ deleteReference—The impact on instances of the referenced Data Shape when instances of the current Data Shape are deleted.
For more information on the delete behaviors, see Setting the Delete Behavior with Foreign Keys.
5. Click Save to save the changes to the service.
6. Navigate to the PTC.SCA.SCO.DatabaseManager Thing.
7. Synchronize the database information in Operator Advisor with the database schema. For more information, see Synchronizing the Database Information and the Database Schema.
Removing a Foreign Key
To remove a foreign key:
 |
Do not remove any foreign keys that are defined out-of-the-box. Remove only foreign keys which you have previously added, for example by Adding Custom Properties to Operator Advisor Entities or Adding Custom Entities to the Operator Advisor Data Model.
|
1. Navigate to the database Thing configured for your system, for example, PTC.SCA.SCO.PostgresDatabase or PTC.SCA.SCO.MSSQLDatabase.
2. Under Services, execute the RemoveForeignKey service with the following inputs:
◦ dataShapeName—The Data Shape for the database table from which you are removing the foreign key constraint.
◦ fieldName—The field on the Data Shape on which you are removing the foreign key constraint.
◦ dbInfo—The JSON description of the foreign key to be removed. The same identifier (if any), name, referenceDataShapeName, and refereceFieldName information that is specified in the Get<entity>DBInfo service where the foreign key is defined must be provided. Any onDelete or deleteReference values are ignored by the service if they are included.
3. Click Execute.
4. Navigate to the Get<entity>DBInfo service where the foreign key is defined, and remove the foreign key definition from the services. If this is not removed, then the foreign key is added back to the database the next time the synchronization services are run.
For example, to remove a foreign key constraint with no identifier specified from a field named WorkDefinitionUID on the MyObject Data Shape, specify the following for the input parameters:
• dataShapeName—MyObject
• fieldName—WorkDefinitionUID
• dbInfo—
{
"name":"WorkDefinitionUID",
"referenceDataShapeName":"PTC.SCA.SCO.WorkDefinition",
"referenceFieldName":"UID"
}
"name":"WorkDefinitionUID",
"referenceDataShapeName":"PTC.SCA.SCO.WorkDefinition",
"referenceFieldName":"UID"
}