I'm recreating tables through data step code with all attributes, formats, informats and labels and then recreating all the integrity constraints through proc datasets.
However, if a table has a foreign key that is a primary key in another table or vice-versa, I've got an error/warning saying I can't update/delete because of relational integrity constraints. What should be the step by step of recreating tables when they have these kind of constraints?
By the way, it has been done through the user written code transformation (all user written) in SAS DIS.
You have to drop the foreign key constraint first, then the primary key constraint and then re-create them - this time with the primary key first e.g.
data class; set sashelp.class; run; data boys; set class(where=(sex="M")); run; proc datasets lib=work; modify class; ic create prim = primary key(name); modify boys; ic create for = foreign key(name) references class; quit; proc datasets lib=work; modify boys; ic delete for; modify class; ic delete prim; quit;
Many thanks for your answer.
I've tried something like that but when I try to delete the foreign key I've got a warning saying the foreign key doesn't exist... It doesn't make sense because when I run the proc contents of that table I actually can see it and also in the metadata of the table...
I have no idea why it is happening. I've checked the syntax and it is ok. I'm doing this in the user written code of SAS DIS, I don't know if it has any impact.
yesterday - last edited yesterday
I can't specify the real fields but it is like you said:
proc datasets library=myLibrary nolist; modify mytableB; ic delete forkeymytableB; modify mytableA; ic delete primkeymytableA; quit;
where forkeymytableB has the same name as the primkeymytableA (forkeymytableB refers to primkeymytableA)
And this is the output running the proc contents of mytableB:
That's strange because the following works for me as expected
data class; set sashelp.class; run; data boys; set sashelp.class(where=(sex="M")); run; proc datasets lib=work; modify class; ic create mypkey = primary key (name); modify boys; ic create myfkey= foreign key (name) references class on delete restrict on update restrict; quit; proc datasets lib=work; modify boys; ic delete myfkey; modify class; ic delete mypkey; quit;
Try ic delete _all_ instead of naming the keys