Enterprise Data Manager Guide

6.4.4
- 6.5.1
- 6.5 HotFix 1
- 6.5
- 6.4.3 HotFix 1
- 6.4.3

Back Next

Step 2. Import Table Relationships

Import of suggested table relationships is the second phase of constraint definition from table relationships. When you define constraints from table relationships, you must view the suggested relationships and import the relationships that you want to create constraints from. You can view suggested unique columns and suggested table relationships.

Constraint definition from table relationships consists of discovering table relationships and then importing the discovered relationships as constraints. The discovery process identifies relationships and stores the suggestions in a profiling suggestion table in the ILM repository. The discovery process does not automatically create the constraints for the tables in the ILM repository. After you discover table relationships, you must review the suggestions. Then, you import the suggestions that you want to create constraints from. You import table relationships after you use any of the available tools to discover relationships.

All of the discovery tools store the suggestions in the same table. If you discovery table relationships multiple times or if you use multiple discovery tools, the system appends the suggestions to the profiling suggestion table.

You can review and import the following types of suggestions:

Suggested Table Relationships: View the suggested table relationships to determine which relationships you want to use to create constraints for. Then, import the relationships as constraints for the source metadata in the ILM repository. When you import the suggested relationships, the ILM engine creates an unique constraint on the parent table and a referential constraint on the child table. The child table includes a reference to the unique key on the parent table. Although the discovery process identifies primary keys, when you import the relationships, the ILM engine creates the discovered primary keys as unique keys to avoid a database conflict. A conflict can occur if a primary key already exists on the table.
Suggested Unique Columns: View the suggested unique columns to create unique key constraints for columns that are not included as part of the primary key-foreign key relationship discovery. Then, choose the suggested unique columns that you want to import. When you import the unique columns, the ILM engine creates unique key constraints for the source metadata in the ILM repository.

The import sequence order is independent. You can import suggested unique columns or table relationships in any order. For example, if you import table relationships first, and the foreign key does not find a primary key on the same column in the parent, then the import creates the unique key on the parent and then creates the foreign key.

When you import the suggestions, the ILM engine checks if the constraint exists in the ILM repository. If the constraint exists in the ILM repository, then the import ignores the suggestion. If the constraint does not exist, the ILM engine uses the suggestions that you selected to create the constraints.

The import creates constraints with the following naming conventions:

Unique keys.

PROF_<TABLE NAME>_UK

Foreign keys.

PROF_<TABLE NAME>_FK

The

PROF_

prefix identifies constraints that you imported from table relationship discovery versus constraints that you created when you imported metadata from the source database or that you manually created.

You can only import constraints for tables that exist in the ILM repository. If you try to import constraints for tables that do not exist in the ILM repository, the import generates an error. You must import all metadata for the source database before you define constraints from table relationships.

After you import the suggestions as constraints, the suggestions remain in the ILM repository. After you create the constraints, you can maintain the constraints if required. For example, you can change or delete constraints that you imported.

ILM Repository Constraints

Suggested Table Relationships

Suggested Unique Columns

Download Guide

Watch

Comments

Communities

Knowledge Base

Success Portal