How to Exclude / Delete Relational Model Data
When working Database First or Model First, you might end up with elements in the relational model data, like tables and table fields which are no longer used by the Abstract Entity Model. There are two different actions which can be taken:
- Exclude from project. Excluding an element from the project is the same as when you work Database First and you don't check that element's checkbox in the wizard: the element will be removed from the Relational Model Data storage, however not from the real schema in the RDBMS.
- Delete from schema. Deleting an element means that the element is no longer usable in any way and should be removed.
Not all elements can be deleted or excluded. The elements which can be excluded are:
- Catalog
- Schema
- Table
- View
- Stored Procedure
- Sequence (not system sequences)
- Table Valued Functions
The elements which are delete-able are:
- Table
- Table field
- Foreign Key constraint
- Unique constraint
- Sequence (not system sequences)
To exclude an excludable element, right-click it in the Catalog Explorer and select Exclude From Project from the context menu. The element is removed from the project immediately. Any mappings referring to the element or containing elements (if you exclude a catalog / schema for example), are now 'orphaned' and invalid.
To delete a delete-able element, right-click it in the Catalog Explorer and select Toggle Marked for Deletion Flag from the context menu. When an element is marked for deletion, it's grayed out in the Catalog Explorer and it's not usable in mappings. Any mapping referring to the 'deleted' element is invalid. Elements which have their Marked for Deletion flag set are exportable in an Update script to make the deletion final. Exporting these changes will also remove the elements from the project.
Automating the excluding/deletion process
It might not always be obvious or easy to determine which elements can be deleted and which elements can be excluded. To make this simple, the designer has a feature which can automate the deletion and exclusion of orphaned elements in the project. An orphaned element is an element which is not used in any mapping.
The automated process is controlled by two Project Settings: Excludable orphaned element detected action for elements which are excludable and Non excludable orphaned element detected action for elements which aren't excludable.
The values the settings are set to are applied on orphaned elements every time Relational Model Data Synchronization is performed. By default a warning is issued, which offers you through the Errors & Warnings panel docked at the bottom of the designer per warning a list of suggestions what to do. While this might be handy for a small number of elements, for larger number of elements, it's more efficient to automate it.