Is it possible to define a foreign key with duplication and cascading delete only if no values exist? I have the following scenario:

A survey table with the columns survey_type and survey_version.
(survey_type, survey_version) is a primary key of the survey table.

I also have a request table. It contains requestIDs that are associated with a survey_type. Whenever a user initiates a survey for a certain requestID, they will get the most up to date survey_version. 

I wish to enforce the restriction that allows creation of requests only for survey_types that actually exist. I also wish for requests to be deleted if the associated request_type is removed from the system. A FOREIGN KEY with ON DELETE CASCADE is almost a perfect solution, except that I need the cascading to happen only if all records with a certain survey_type are removed. If we are only removing an old and no longer used version of a survey_type, but records of that survey_type still exist in the survey table, then the survey request should also remain in the system.

Is this possible to do with FOREIGN KEY constraints, or should I look for another way?
Is this possible to do with FOREIGN KEY constraints, or should I look for another way?


Look another way. I'm not 100% I understand what's happening from that description but ON DELETE CASCADE quite explicitly means "allow the deletion to cascade". There are no buts about it.

Or, just make sure in the app, you check -- in a transaction -- that there are no remaining survey_type's then you can delete and allow it to cascade naturally.