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

Recreating tables with integrity constraints

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

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


Accepted Solutions
Solution
‎01-18-2018 06:10 PM
Valued Guide
Posts: 554

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;

 

 

View solution in original post


All Replies
Solution
‎01-18-2018 06:10 PM
Valued Guide
Posts: 554

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: 37

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

 

Valued Guide
Posts: 554

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: 37

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

Valued Guide
Posts: 554

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

Contributor
Posts: 37

Re: Recreating tables with integrity constraints

Posted in reply to ChrisBrooks

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

Valued Guide
Posts: 554

Re: Recreating tables with integrity constraints

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.

Contributor
Posts: 37

Re: Recreating tables with integrity constraints

Posted in reply to ChrisBrooks

@ChrisBrooks,

 

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

 

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

 

 

May15

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 272 views
  • 0 likes
  • 2 in conversation