C:\Public\pkey_directory and C:\Public\fkey_directory.
The pathnames are stored in the descriptor information of the SAS
data files.
libname pkds '\\D4064\Public\pkey_directory';
C:\Public\fkey_directory.
However, that directory does not exist on machine F2760. Therefore,
opening the foreign key data file fails.
data tv_survey(label='Validity checking');
length idnum age 4 gender $1;
input idnum gender age network pbs other;
datalines;
1 M 55 80 . 20
2 F 36 50 40 10
3 M 42 20 5 75
4 F 18 30 0 70
5 F 84 0 100 0
;
proc datasets nolist;
modify tv_survey;
ic create val_gender = check(where=(gender in ('M','F')))
message = "Valid values for variable GENDER are
either 'M' or 'F'.";
ic create val_age = check(where=(age >= 18 and age = 120))
message = "An invalid AGE has been provided.";
ic create val_new = check(where=(network = 100));
ic create val_pbs = check(where=(pbs = 100));
ic create val_ot = check(where=(other = 100));
ic create val_max = check(where=((network+pbs+other)= 100));
quit;proc sql;
create table people
(
name char(14),
gender char(6),
hired num,
jobtype char(1) not null,
status char(10),
constraint prim_key primary key(name),
constraint gender check(gender in ('male' 'female')),
constraint status check(status in ('permanent'
'temporary' 'terminated'))
);
create table salary
(
name char(14),
salary num not null,
bonus num,
constraint for_key foreign key(name) references people
on delete restrict on update set null
);
quit;INIT:
put "Test SCL integrity constraint functions start.";
return;
MAIN:
put "Opening WORK.ONE in utility mode.";
dsid = open('work.one', 'V');/* Utility mode.*/
if (dsid = 0) then
do;
_msg_=sysmsg();
put _msg_=;
end;
else do;
if (dsid > 0) then
put "Successfully opened WORK.ONE in"
"UTILITY mode.";
end;
put "Create a check integrity constraint named teen.";
rc = iccreate(dsid, 'teen', 'check',
'(age > 12) && (age < 20)');
if (rc > 0) then
do;
put rc=;
_msg_=sysmsg();
put _msg_=;
end;
else do;
put "Successfully created a check"
"integrity constraint.";
end;
put "Create a not-null integrity constraint named nn.";
rc = iccreate(dsid, 'nn', 'not-null', 'age');
if (rc > 0) then
do;
put rc=;
_msg_=sysmsg();
put _msg_=;
end;
else do;
put "Successfully created a not-null"
"integrity constraint.";
end;
put "Create a unique integrity constraint named uq.";
rc = iccreate(dsid, 'uq', 'unique', 'age');
if (rc > 0) then
do;
put rc=;
_msg_=sysmsg();
put _msg_=;
end;
else do;
put "Successfully created a unique"
"integrity constraint.";
end;
put "Create a primary key integrity constraint named pk.";
rc = iccreate(dsid, 'pk', 'Primary', 'name');
if (rc > 0) then
do;
put rc=;
_msg_=sysmsg();
put _msg_=;
end;
else do;
put "Successfully created a primary key"
"integrity constraint.";
end;
put "Closing WORK.ONE.";
rc = close(dsid);
if (rc > 0) then
do;
put rc=;
_msg_=sysmsg();
put _msg_=;
end;
put "Opening WORK.TWO in utility mode.";
dsid2 = open('work.two', 'V');
/*Utility mode */
if (dsid2 = 0) then
do;
_msg_=sysmsg();
put _msg_=;
end;
else do;
if (dsid2 > 0) then
put "Successfully opened WORK.TWO in"
"UTILITY mode.";
end;
put "Create a foreign key integrity constraint named fk.";
rc = iccreate(dsid2, 'fk', 'foreign', 'name',
'work.one','null', 'restrict');
if (rc > 0) then
do;
put rc=;
_msg_=sysmsg();
put _msg_=;
end;
else do;
put "Successfully created a foreign key"
"integrity constraint.";
end;
put "Closing WORK.TWO.";
rc = close(dsid2);
if (rc > 0) then
do;
put rc=;
_msg_=sysmsg();
put _msg_=;
end;
return;
TERM:
put "End of test SCL integrity constraint"
"functions.";
return;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;proc datasets nolist;
modify tv_survey;
ic delete val_max;
ic delete val_gender;
ic delete val_age;
run;
quit;TERM:
put "Opening WORK.TWO in utility mode.";
dsid2 = open( 'work.two' , 'V' ); /* Utility mode. */
if (dsid2 = 0) then
do;
_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;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
;
proc datasets library=work nolist;
modify Singers1;
ic create primary key (FirstName LastName); 1
run;
modify Singers2;
ic create foreign key (FirstName LastName) references Singers1
on delete restrict on update restrict; 2
run;
modify Singers2;
ic create primary key (LastName FirstName); 3
run;
modify Singers1;
ic create foreign key (LastName FirstName) references Singers2
on delete restrict on update restrict; 4
run;
quit;| 1 | Defines a primary key constraint for data set Singers1, for variables FIRSTNAME and LASTNAME. |
| 2 | Defines a foreign key constraint for data set Singers2 for variables FIRSTNAME and LASTNAME that references the primary key defined in Step 1. Because the intention is to define a primary key using the same variables, the foreign key update and delete referential actions must both be RESTRICT. |
| 3 | Defines a primary key constraint for data set Singers2 for variables LASTNAME and FIRSTNAME. Because those exact same variables are already defined as a foreign key, the order must be different. |
| 4 | Defines a foreign key constraint for data set Singers1 for variables LASTNAME and FIRSTNAME that references the primary key defined in Step 3. Because those exact same variables are already defined as a primary key, the order must be different. Because a primary key is already defined using the same variables, the foreign key's update and delete referential actions must both be RESTRICT. |