While this is certainly a fun and interesting weakness for Nathaniel Cho and other guardians, or fighters. That much isn't the case for decks that mainly focus on clues, I would advice you to draw a different basic weakness if you happen to draw it with one of those decks. Fighter type investigators usually have a larger health pool, but Self-Destructive can easily trigger twice on a turn in multiplayer, so it's comparable to Internal Injury in impact I think.

In solo it's mostly fine and fun, since you always have to deal with enemies (though some decks use evasion and/or cards like Waylay, Persuasion and Dumb Luck or even Scrying to deal with enemies).

The fact that the impact of this weakness varies so heavily does raise an interesting question: "should Fantasy Flight restrict the basic weaknesses a character can draw?". I think the answer is yes, and they could just put it under deckbuilding requirements e.g. no pact weakness. And for characters who have multiple obvious archetypes, not all of which are impacted, probably also restrict their weakness pool to cards that are highly likely to be at least somewhat impactful.

All in all I rather like this weakness (for any deck in which it has any impact), though it does fall into the, frankly massive, suite of weaknesses with remove bad effect. I find that these weaknesses often still linger in my threat area, as two actions can be a steep cost if you have to deal with monsters or you just have to hurry up to progress through the act deck in a timely fashion.