TERM:
put "End of test SCL integrity constraint"
"functions.";
return;
The previous code creates the SCL catalog entry. The following code creates two data
files, One and Two, and executes the SCL entry Example.Ic_Cat_Allics.SCL:
/* Submit to create data files. */
data one two;
input name $ age;
datalines;
Morris 13
Elaine 14
Tina 15
;
/* after compiling, run the SCL program */
proc display catalog= example.ic_cat.allics.scl;
run;
Removing Integrity Constraints
The following sample program segments remove integrity constraints. In the code that
deletes a primary key integrity constraint, note that the foreign key integrity constraint is
deleted first.
This program segment deletes integrity constraints using PROC SQL.
proc sql;
alter table salary
DROP CONSTRAINT for_key;
alter table people
DROP CONSTRAINT gender
DROP CONSTRAINT _nm0001_
DROP CONSTRAINT status
DROP CONSTRAINT prim_key
;
quit;
This program segment removes integrity constraints using PROC DATASETS.
proc datasets nolist;
modify tv_survey;
ic delete val_max;
ic delete val_gender;
ic delete val_age;
run;
quit;
This program segment removes integrity constraints using SCL.
TERM:
put "Opening WORK.TWO in utility mode.";
dsid2 = open( 'work.two' , 'V' ); /* Utility mode. */
if (dsid2 = 0) then
do;
636 Chapter 26 • SAS Data Files
_msg_=sysmsg();
put _msg_=;
end;
else do;
if (dsid2 > 0) then
put "Successfully opened WORK.TWO in Utility mode.";
end;
rc = icdelete(dsid2, 'fk');
if (rc > 0) then
do;
put rc=;
_msg_=sysmsg();
end;
else
do;
put "Successfully deleted a foreign key integrity constraint.";
end;
rc = close(dsid2);
return;
Reactivating an Inactive Integrity Constraint
The following program segment reactivates a foreign key integrity constraint that has
been inactivated as a result of a COPY, CPORT, CIMPORT, UPLOAD, or
DOWNLOAD procedure.
proc datasets;
modify SAS-data-set;
ic reactivate fkname references
libref;
run;
quit;
Defining Overlapping Primary Key and Foreign Key Constraints
The following code illustrates defining overlapping primary key and foreign key
constraints:
data Singers1;
input FirstName $ LastName $ Age;
datalines;
Tom Jones 62
Kris Kristofferson 66
Willie Nelson 69
Barbra Streisand 60
Paul McCartney 60
Randy Travis 43
;
data Singers2;
input FirstName $ LastName $ Style $;
datalines;
Tom Jones Rock
Kris Kristofferson Country
Willie Nelson Country
Barbra Streisand Contemporary
Paul McCartney Rock
Randy Travis Country
Understanding Integrity Constraints 637
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.188.218.157