The thing is: Cassidy has a heart condition and if you give him drugs, he'll die. But he can use Stimpaks and Super Stimpaks without any problems. Don't give him Psycho, Jet, Mentats or any of that other brainfood, or you'll lose a valuable npc.
And yes, of course anyone in the game can die from a Super Stimpak overdose.
This is potentially comic--Once I was getting ready for a fight, I think against Frog Morton in Redding and he'd given me trouble in previous games. I wanted Cassidy to be a rabid berserk bulletproof killer so I gave him a dose of Psycho.