This actually did occur in the last major campaign I DMed.
One of the characters (female friend of mine) was trying to find out why her fiance never showed up for the wedding. The other character (my husband hehehe) was adventuring for the fun of it. They found that their characters had a lot of reason to be attracted to each other (both very charismatic characters who survived a lot together along with the rest of the party). As the female bard grew more bitter about her fiance, she grew more attracted to the male sorceror, yet played the tension very well. By the time she had decided that she was really in love with the sorceror and hated her fiance, I reintroduced the fiance on a more permanent basis (she had run across him a few times before resulting in greater and greater emotional turmoil) and enjoyed the chaos as she battled her recovering feelings for her fiance when he told her the "truth" of everything he had done and the reasons for it and her new feelings for the sorceror (feelings her fiance was quite aware of for certain reasons of reading her personal journals hehehe)(his moment of confession left her useless at work for the entire day as we handled that one over email between sessions).
By the time her fiance died at the end of the game, I had her bawling (the player AND the character), and being comforted in the arms of the sorceror who was ordered to take care of her and watch over her by a fiance that knew he would never be able to truly be with her again (him being an undead vampire and all before he sacrificed himself so he could at least die with the memory of her loving him instead of despising him).
It was absolutely beautiful, and I would do it again at any point. All of my players found the experience very rewarding and not uncomfortable at all.
Of course, such a thing would not have happened if any players had been uncomfortable with it! Hehe...I felt that everyone played their characters quite maturely, and the conflict and final results over the 3 1/2 year period will be remembered in our group for all time.
I love making players cry. :-D