topic Re: Recreating tables with integrity constraints in SAS Data Management

Recreating tables with integrity constraints

May15 — Thu, 11 Jan 2018 20:58:05 GMT

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

Re: Recreating tables with integrity constraints

ChrisBrooks — Fri, 12 Jan 2018 00:17:43 GMT

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;

Re: Recreating tables with integrity constraints

May15 — Fri, 12 Jan 2018 16:51:14 GMT

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

Re: Recreating tables with integrity constraints

ChrisBrooks — Fri, 12 Jan 2018 18:52:19 GMT

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?

Re: Recreating tables with integrity constraints

May15 — Mon, 15 Jan 2018 10:00:39 GMT

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

Re: Recreating tables with integrity constraints

ChrisBrooks — Mon, 15 Jan 2018 10:56:09 GMT

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

Re: Recreating tables with integrity constraints

May15 — Thu, 18 Jan 2018 12:48:50 GMT

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

Re: Recreating tables with integrity constraints

ChrisBrooks — Thu, 18 Jan 2018 23:09:17 GMT

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.

Re: Recreating tables with integrity constraints

May15 — Thu, 18 Jan 2018 23:19:05 GMT

@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