Was sweet Caroline written for Caroline Kennedy?

No. It was a real or fictional paramour of the songwriter and performer Neil Diamond. Songs of this type are usually fictional or about fictional characters, for example the tragic (Sunny).