Recreating tables with integrity constraints

[May15]

Hi all,

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.

Thanks,

May15

[ChrisBrooks · Accepted solution]

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;

[May15]

Hi ChrisBrooks,

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.

Thanks,

May15

[ChrisBrooks]

That shouldn’t make any difference - can you post the Proc Datasets code you’re using to delete the foreign key along with the Proc Contents output?

[May15]

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:

Thanks,

May15

[ChrisBrooks]

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

[May15]

Hi @ChrisBrooks,

Using the ic delete _all_ it actually works. I have no idea why it doesn't when I name the keys.

Thanks,

May15

[ChrisBrooks]

It may be that there's something odd about the constraint name which makes it look other than it is e.g. a numeral 1 instead of a letter l or two letter Vs instead of a W or some unprintable character at the end.

It would be interesting to know if the problem persists after the constraints have been re-created but in any can if you're happy the problem is resolved it would be appreciated if you could mark the solution as accepted.

[May15]

@ChrisBrooks,

I've checked the name several times. I think it is something else. If someone knows why it could be happening, please share 🙂

Anyway, the step by step you've said makes sense and it works with the _all_ option, so thanks again.

May15