SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Recreating tables with integrity constraints

Reply
Contributor
Posts: 32

Recreating tables with integrity constraints

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

Super Contributor
Posts: 478

Re: Recreating tables with integrity constraints

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;

 

 

Contributor
Posts: 32

Re: Recreating tables with integrity constraints

Posted in reply to ChrisBrooks

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

 

Super Contributor
Posts: 478

Re: Recreating tables with integrity constraints

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?
Contributor
Posts: 32

Re: Recreating tables with integrity constraints

[ Edited ]
Posted in reply to ChrisBrooks

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:

 

Output.PNG

 

Thanks,

 

May15

Super Contributor
Posts: 478

Re: Recreating tables with integrity constraints

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

Ask a Question
Discussion stats
  • 5 replies
  • 137 views
  • 0 likes
  • 2 in conversation