I'm kinda new to ERM, SQL and databases in genuine, so this might just be a silly question, but here I go anyway.
I made an ERM and this is the part in question:
Name is going to be a foreign key to MainCharacter, but I also want it to be the primary key. Is this even possible at all or should I add a surrogate key to MainCharacter? If possible, is there a way to show this in the ERM?
I found some related questions here, but couldn't really find the answer I was looking for. Any help would be appreciated :)
It's certainly possible and not unusual to have a foreign key be a primary key too. This commonly happens when implementing overlapping or optional subtypes.
Your example sounds like a case of optional subtyping, but your diagram shows a relationship between independent entity sets. Classic ERD notation doesn't have specific notation for subtyping, but we can show it as a weak entity set in an identifying relationship:
The double rectangle indicates a weak entity set (meaning it doesn't have its own key but is identified by its relationship with Character) while the double association line indicates total participation (a MainCharacter can't exist without an association to a Character). The double diamond indicates an identifying relationship. These elements are always used in combination, though total participation can be used in regular relationships as well. By using the right notation, we rarely need cardinality indicators in ERDs.
Extended ER notations indicate subtyping using different styles, like a circle and arc or a triangle. I prefer the circle-and-arc notation since it can be used to distinguish between overlapping and disjoint subtypes by writing an o or d in the circle, and the arc distinguishes the subtype from the supertype regardless of the orientation of the elements.