SAS: proc sql / foreign key constraints

Dropping the child table of a parent/child relationship

/*   Create a parent-child relationship between two tables
     using a primary and a foreign key: */
proc sql;
  
  create table tq84_parent (
     id   num,
     val char(25),
  /* ----------------------------------- */
     constraint tq84_pk primary key (id)
  );

  create table tq84_child (
     id_parent num,
     val       char(25),
  /* ----------------------------------- */
     constraint tq84_fk foreign key (id_parent) references tq84_parent
  );

quit;

/* SAS does not allow to drop the child of a foreign key
   relation ship!
   The following drop statement results in a
      ERROR: A rename/delete/replace attempt
             is not allowed for a data set
             involved in a referential integrity
             constraint. 
*/       
proc sql;
   drop table tq84_child;
quit;

/* So, the foreign key must explicitely be dropped before the
   child table can be dropped:
*/
proc sql;
  alter table tq84_child drop foreign key tq84_fk;

  drop table tq84_child;
quit;

See also