SQL Constraint Syntax and Use

[BrockDeanMunro]

This is my first time using this site, and I have made various searches for SQL Database syntax and use but couldn't find a specific place to post this . . . I'm having trouble in SQL, with the table Players, it requires two primary keys. I'd added one successfully . . .

                                          ALTER TABLE Players
ADD CONSTRAINT fk_PlayerTypeID
FOREIGN KEY (PlayerTypeID)
REFERENCES PlayerTypes(PlayerTypeID)

But when I come to do the same for TeamID . . .

                                                                                            ALTER TABLE Players
ADD CONSTRAINT fk_TeamID
FOREIGN KEY (TeamID)
REFERENCES Teams(TeamID)

It says its already been created. I understand that it has already been created as a foreign key in the TeamLists table-how do I go about making it a foreign key in two tables? If that makes sense, any help would be appreciated!

[Elkvis]

foreign key names (I'm guessing mysql) must be unique within a given schema.

[Mario F.]

As Elkvis says, if in your TeamLists table you named your foreign key with,

ADD CONSTRAINT fk_TeamID

Then you cannot use that name again.

Ideally, in order to ensure unique names, you want to name your foreign keys in this format: fk_SecondaryTableName_PrimaryTableName_PrimaryTableKeyName

SQL is unfortunately friendly to large names since there's virtually no name encapsulation in the language. With large databases this problem becomes sometimes hard to manage if you don't adhere to strict naming rules. Meanwhile SQL is one the few languages that still pretty much demands the use of Hungarian Notation (also one of the few places where it still makes sense these days). So don't ever drop those prefixes ("fk_, ix_, etc)