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 must match an existing UID value for a site.
The foreign key constraint also prevents rows in the database table that are referenced by foreign key fields from being deleted. 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.
To add a foreign key to an existing database table:
1. Navigate to the database thing configured for your system (PTC.SCA.SCO.PostgresDatabase or PTC.SCA.SCO.MSSQLDatabase).
2. Under Services, execute the AddForeignKey service with the following inputs:
◦ dataShapeName—The data shape for the database table to which you are adding the foreign key.
◦ fieldName—The field on the data shape on which you are adding the foreign key constraint.
◦ referenceDataShapeName—The data shape of the referenced database table.
◦ referenceFieldName—The name of the field containing the value being referenced.
3. Click Execute.
To remove a foreign key:
 |
Remove only foreign keys which you have previously added. Do not remove any foreign keys that are defined out-of-the-box.
|
1. Navigate to the database thing configured for your system (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.
◦ fieldName—The field on the data shape on which you are removing the foreign key constraint.
3. Click Execute.