The foreign key metadata definition is incomplete after you modify a foreign key on a physical table


An error can occur with SAS® Data Integration Studio jobs and tables after you change foreign key definitions for tables registered in the metadata and update the table metadata.

For example, this issue occurs when you complete steps similar to the following:

  1. Create two tables (table1 and table2) with a foreign key relationship defined on table2 to table1. (For example, table2.var2 to table1.var1.)
  2. Register the two tables either through SAS® Management Console or the METAILB procedure. Ensure that both tables are on the SELECT list.
  3. Create and save a job in SAS® Data Integration Studio that uses table2.
  4. Update the definition of table2 in the physical data source and change the foreign key definition on table2 to use a different column but the same target column. (For example, table2.var3 to table1.var1.)
  5. Update the metadata for the two tables either through SAS Management Console or PROC METALIB. Ensure that both tables are on the SELECT list. (The metadata enters an error state, but this step does not report that the issue occurred.)
  6. Reopen the job in SAS Data Integration Studio. An error occurs.

This issue occurs in SAS® 9.4M7 (TS1M7) and later. This issue might be applicable to all SAS®9 engines.

Note that errors do not occur when you run PROC METALIB or when you update the table metadata. Instead, the error is flagged on the job and the tables in the jobs after you update the table metadata.

When this issue occurs, hovering over an affected table displays an error message similar to the following:

Errors: Foreign key definition in table is incomplete

In addition, the Keys tab on the table properties displays a dialog box with the following error message (which is for the original key):

The foreign key xxx.yyy is invalid because it has no partner unique key.  It is recommended that the key be deleted.

The dialog box asks if you want to delete the key. Select Yes to delete the (original) key, which clears the error.

Cause

This issue occurs when you change a foreign key without first deleting it, which causes an orphaned foreign key definition.
This definition will not be cleaned up by the metadata analysis or repair process. 

Workaround

Method 1: Click "Yes" in the error dialog box on the table properties "Keys" tab

You can circumvent the error on a table-by-table basis by completing the following steps:

  1. Open the table properties. 
  2. Navigate to the Keys tab.
  3. Click Yes in the dialog box (as shown above).

Method 2: Customize and run the delete_unassociated_foreign_keys.sas program

See the delete_unassociated_foreign_keys.sas program.   

Customize the Options statement in this program to point to your SAS Metadata Server and connect as sasadm@saspw.

In addition, there is a macro variable set to run this program in a report-only mode (%let MODE=REPORT;), or you can perform the actual deletion (%let MODE=DELETE;).

When you run this program, it finds all of the affected foreign keys and reports them to the SAS log. If you specify %let MODE=DELETE;, the affected foreign keys are deleted from metadata.