Hows it going? noob sas sql question here . Im trying to learn foreign references and what Im trying to do is establish a primary foreign relationship and then delete from the primary and have it auto delete in the table with an associated foreign key . Say I have stopped selling cars > 30000 because no one is buying them. I want to delete all the cars in my records that match this condition from all linked tables by simply deleting the entries in the table with the primary key. I thought that using DELETE CASCADE would accomplish this but I must be missing sth. It says this is not supported or maybe my syntax is wrong? Any help would be appreciated.
%macro make_sql;
proc sql ;
create table k1 (id num , model char , msrp num ) ;
insert into k1
select monotonic()*2 as id , model , msrp from sashelp.cars;
quit;
proc sql;
alter table k1
add constraint prime primary key(id);
run;
proc sql ;
create table k2 (id num , model char, inventory num );
insert into k2
select monotonic()*2 ,model, invoice
from sashelp.cars;
quit;
proc sql ;
alter table k2
add constraint foreign2 foreign key(id)
references k1
on delete cascade ;
quit;
%mend make_sql;
%macro del_sql;
proc sql;
alter table k2
drop foreign key foreign2;
drop table k1;
quit;
proc sql;
drop table k1;
quit;
%mend del_sql;
%make_sql;
* condition to remove the primary values
proc sql;
delete from k1
where msrp > 30000
;
quit;
*K1 deletes the values k2 stays the same? ;
proc contents data=k1 ;
run;
proc contents data=k2;
run;
*********************************************************
Ive also replaced ON DELETE CASCADE with ON UPDATE CASCADE
then used below. its not working either so I must be missing sth very basic. 😞
proc sql;
update k1
set id = 0
where msrp > 30000
;
quit;
