Foreign Key Discovery
A column is a foreign key if its data values match the primary key column values in another data object.
You can perform foreign key discovery on multiple data objects in the Developer tool. Create an enterprise discovery profile to select data objects and define the profile.
Before you perform foreign key discovery, you must identify the parent and child data objects in the enterprise discovery profile. The profile uses one or more keys in the parent object, including its primary key, to discover foreign keys in the child object. After you define the parent and child objects and identify the keys in the parent object, you create and run the profile.
Defining Parent and Child Object Relationships
To find foreign key relationships between two data objects, you must select a parent data object and specify the primary key in that object.
1. Open an enterprise discovery profile that contains the data objects you want to analyze.
2. Select the parent object.
3. Select the primary key in the parent object:
- - Click the Properties tab, and click Keys.
- - Click Add, and select the primary key column in the New Key dialog box.
- - Click OK in the New Key dialog box. Verify that the primary key is displayed in the Selected fields pane and that the Primary Key option is checked.
Create a foreign key profile to analyze the child object for foreign keys.
Discovering Foreign Key Relationships Between Data Objects
Use an enterprise discovery profile in the Developer tool to find key relationships between two data objects
The data object that contains the primary key is the parent object, and the data object that contains the foreign key is the child object.
1. Open an enterprise discovery profile that contains the data objects you want to analyze.
2. Right-click the name of a data object and select Foreign Key Profile.
3. Enter a name for the profile and verify the project location. If required, browse to a new location. Optionally, enter a text description of the profile.
4. Select the keys in the parent object that the profile will use to find foreign keys in the child object.
5. Save and run the profile.
Foreign Key Analysis Results
After you run a foreign key profile, click the profile name below the modeling editor to see the results of the analysis.
The results view lists the columns that meet the primary-foreign key inference criteria you defined. Click the Options button to edit the inference settings. Click a column name and select Validate to verify that an inferred key is a valid key for the data objects.
The following table describes the foreign key analysis properties:
Property | Description |
|---|
Parent Primary Key | A primary key column in the parent data object that the profile uses to find foreign keys in a child object. |
Child Foreign Key | A column that the profile infers to be a foreign key to the parent primary key on this row. |
Inclusion % | The quantity of data values that match between the primary and foreign key, expresses as a percentage. Note: You may see a variance in the Inclusion % value for an inferred column in the foreign key results and after you validate it. For an inferred column, Inclusion % is the number of unique, foreign key column values of a child object that match the unique, primary key column values of the parent object. After you validate an inferred column, it is the number of foreign key column values of a child object that match the primary key column values of the parent object. |
Relationship Type | The type of relationship defined for the primary and foreign key columns before the profile runs. If you define a relationship before the profile runs, the profile returns data for the relationship even if the inclusion percentage figure does not meet the confidence threshold set for the profile. |
Verified | Indicates that a user has validated the primary-foreign key relationship. |
Last Run Time | Data and time the profile last ran. |
Relationship Type (In Model) | Indicates that the profile verified the relationship between the columns. |