Date:         Tue, 27 Jul 2004 18:12:53 -0400
Reply-To:     sashole@bellsouth.net
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Paul M. Dorfman" <sashole@BELLSOUTH.NET>
Organization: Sashole of Florida
Subject:      Re: ERROR: A rename/delete/replace attempt is not allowed for a
              data              set involved in a referential integrity
              constraint.
Comments: To: "Pardee, Roy" <pardee.r@GHC.ORG>
In-Reply-To:  <C93AE4021921784ABAE89216FF4C371805B087CC@ROC2T7.ghc.org>
Content-Type: text/plain; charset="us-ascii"

Roy,

You appear to be trying to kill a table containing a primary key, thus
leaving the foreign keys in other tables, tied to the primary key, orphans,
or kill a foreign with the

ON DELETE RESTRICT

restraint imposed over the key when the table was defined (or added later).
Run CONTENTS on the files in question (or click on the INTEGRITY tab in the
viewer) and find the keys involved in the referential constraints. Then
remove the constraints, first from the foreign key[s], then from the primary
key[s] using either SQL or DATASETS. For example,

Proc sql ;
   alter table foreign drop constraint for_key ;
   ... Drop the rest of foreign constraints ...
   alter table primary drop constraint pri_key ;
   ... Drop the rest of primary constraints ... ;
Quit ;

Now you can modify (including delete) the tables. Of course, if all the data
sets in question are the only data sets residing in the library containing
them (which would be analogous to a database), you can just kill the
library. To the OS, it is but a directory, and it knows nothing about the
constraints imposed by SAS on the tables inside the database.

Kind regards,
----------------
Paul M. Dorfman
Jacksonville, FL
----------------

> -----Original Message-----
> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On
> Behalf Of Pardee, Roy
> Sent: Tuesday, July 27, 2004 5:20 PM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: ERROR: A rename/delete/replace attempt is not
> allowed for a data set involved in a referential integrity constraint.
>
> I want to delete 3 datasets that have ref integrity set up--a
> parent and
> 2 children.
>
> I'm getting the subject error, both while trying to do a PROC
> DATASETS ... DELETE and a DROP TABLE command in PROC SQL.
> The order in which I'm doing the deletes/drops doesn't seem to work.
>
> Does anybody know how I can kill these 3 datasets?  Neither
> the docs nor google are availing me.  I'm on SAS 8.2 running on win2k.
>
> Thanks!
>
> -Roy
>
> Roy Pardee
> Research Analyst/Programmer
> Center For Health Studies (Cancer Research Network) Group
> Health Cooperative
> (206) 287-2078
>
> This message and any attached files might contain
> confidential information protected by federal and state law.
> The information is intended only for the use of the
> individual(s) or entities originally named as addressees. The
> improper disclosure of such information may be subject to
> civil or criminal penalties. If this message reached you in
> error, please contact the sender and destroy this message.
> Disclosing, copying, forwarding, or distributing the
> information by unauthorized individuals or entities is
> strictly prohibited by law.
>