Inactive Foreign Key Error (Migration issues of SAS 9.1 to 9.2)

Foreign key integrity constraints can become inactive when SAS data sets are moved via the COPY, CPORT, CIMPORT, UPLOAD, or DOWNLOAD procedures. It is possible to happen with PROC MIGRATE too. The reason may be because of PROC MIGRATE being potential enough to migrate datasets with integrity constraints but not with referential constraints. In that case, a user has to activate the foreign key by using IC REACTIVATE.

proc datasets library=XYZ;
modify AAA;
ic reactivate for references ABC;
run;
quit;

Things to do -

1. Execute proc contents of the dataset to view the inactive foreign key.
2. Execute the above proc statement for reactivating the respective foreign key.
3. Once executed check the log which would say that the foreign key is reactivated
4. Now execute proc contents again for the same dataset, to see the status as null for “Inactive” column.

IMPORTANT: The “describe table constraints” has to be executed (in the old box - SAS 9.1) to understand the referential constraints and to pass the dataset to the ‘references’ of ic reactivate statement.


ERROR WHILE EXECUTING IC REACTIVATE

The below error is expected to occur if the foreign key reference dataset (ABC.STR) has lost its constraints during UNIX copying (which should actually be migrated with PROC MIGRATE).

2984 proc datasets library=XYZ;
2985 modify AAA;
2986 ic reactivate forkey references ABC;
ERROR: Primary key does not exist in data set ABC.STR.DATA.
2987 run;