[SalesForce] How to create a master detail or lookup relationship to ContentVersion

I can't offer you any insight into why you cannot do this, you just can't unfortunately, it's a seemingly arbitrary limitation of the platform and the Product2 object. Unless someone who knows about the internals of the platform comes across this I doubt you'll ever get a definitive answer.

In terms of working around it though, the only way to achieve this is to create your lookup as a standard non-required Lookup and then enforce that the value of this lookup cannot be blank using a validation rule.

Your validation rule will look something like this:

ISBLANK(ProductId__c)

If the Product is deleted then this will result in the value of the field being cleared, as per the default 'Clear the value of this field.' behaviour, which will prevent you updating the child record until a valid lookup ID is entered.

If you want to change this behaviour you'll have to write a before delete trigger on Product2 with your desired logic.

You can, however create a trigger on a custom object from a managed package. Thus you can essentially fake a m/d

Given a custom object of bar__c in the managed Package namespace foo__ you would create a trigger on

trigger foo__bar__c (after insert, after update, before delete) {
//trigger logic here. Trigger.new will contain the managed custom objects that
// caused the trigger to fire.
}

A trigger that is fired after insert, after update and before delete has the ability to establish or delete any needed relationships with other objects.

You'll just need to find or create the related records, and populate a lookup field on them.