OneData 10.7 | Managing Master Data with webMethods OneData | Implementing webMethods OneData | Managing Object Constraints | Creating Foreign Key Constraints
 
Creating Foreign Key Constraints
 
Changing Foreign Key Property
Restricting the Values in a Foreign Key Column
Disabling A Foreign Key
Foreign keys establish a relationship between two objects. When the relationship is a hierarchical relationship between a parent and child, the foreign key to the child object displays in the Data Manager, the column that is configured as the foreign key allows you to select values from the mapped column in the parent object in a pop up or drop down as configured. The parent column is referred to as reference column.
For example, consider the objects, Country > State, with the following columns. Country column in the State object is set up as the foreign key column, which refers to the Country ID (primary key) in the Country object. Although the numeric columns are mapped, you can configure the foreign key in such a way to display the corresponding Country Name in the State object.
Note:
If you are using SQL Server, when you create a foreign key constraint for a numeric column, ensure the column has the same length and precision as the base column.
*To create a foreign key constraint
1. On the Menu toolbar, click Define > Objects and select the object in which you want to create the foreign key.
2. Select the Structure tab.
3. Click Edit Constraints.
4. Click Add new Foreign Key constraint and complete the following fields:
Property
Description
Constraint Name
Enter a name for the constraint.
Constraint Caption
Optional. Caption for constraint. If provided, this is displayed with record details in conceptual object hierarchy in Advanced Parent Child or Tree mode.
For information about caption display, see Foreign Key Constraint Caption Display.
Description
Optional description for constraint.
XML Element Name
Enter the name to use as the XSD column name while generating the XML schema in data manager. By default, it uses the Constraint Name.
For details on generating the XML schema, see webMethods OneData User’s Guide.
Related Table Name
Select the parent object.
Related Column Description
Click the icon to select the description column for the foreign key relationship. In Data Manager grid, the selected columns appear as links which allow you to navigate to the record of the referenced object. The records are visible in view or edit mode, depending on the user permissions.
If only a single description is required, select the required column from Select Related Description only.
If multiple descriptions are required, select the columns from the list of Available Columns. You can also select the columns from the tables related to the parent table also by changing the table in Select Table drop down.
Click Set Values after making the selection.
Enforce Referential Integrity
Foreign key is enforced at application or database level. The options include:
*Application Constraint: Referential integrity is enforced at the application level.
*Application Constraint for Display Only: Referential integrity is not enforced for delete/purge at the application level.
*Database Constraint: Referential integrity is enforced at the database level. This enables the constraint at the database level as well.
*Database Constraint for Display Only: Referential integrity is not enforced for delete/purge at the database level.
Note:
If you select Application Constraint for Display Only or Database Constraint for Display Only, OneData skips reference/dependency checks and parent/child temporal validations. For Database Constraint for Display Only, the dependency exists in the physical table and therefore the records in that object cannot be purged.
Passive Constraint
Application level constraint that narrows the reference values depending on the values selected in the parent table.
Show Filter
Whether to show filter when selecting a parent reference value in the pop-up.
Display Options
Select the option for the reference value to:
*Show as dropdown
*Show as lookup with description as textarea
*Show as lookup with description as textbox
*Show as Tree: Applicable only to the constraint set as Primary Network Recursive. It displays the values as a tree when adding a relationship to a network recursive conceptual object via Insert > Add relationship.
*Show as Tree - Leaf node selectable: Not implemented yet. The value selection pop up is populated as in Show as Tree display option only.
Inactive
Whether to inactivate the constraint.
Subtype Column
(Applicable only to supertype subtype structures) Select the subtype column.
Subtype Column Value
(Applicable only to supertype/subtype structures) Enter the subtype column value to fetch the related subtype columns specific to the reference value selected in the supertype object.
Network recursive
(Applicable only to network recursive structures) Select the network recursive relationship. Primary is the main foreign key constraint in a network recursive relationship and secondary is for any additional foreign key constraints created.
Constraint Filter
Specify any filter that needs to be applied to the values for this constraint. Only the values matching the constraint filter are shown in the field. Values must be qualified using single quotes.
Navigation Options
Enable navigation to allow graphical presentation in the data hierarchy window.
Available Columns
Available columns in the object that can be used to build the constraint.
Selected Columns
Columns selected in the object to build the constraint.
Columns from Related Tables
Columns from related table that can be used to build the constraint.
5. Click Save to create the foreign key constraint.