Search code examples
sqldatabasemany-to-manyrelationship

SQL how to handle a many to many relationship

I have two tables that have a many-many relationship:

Player(personID, school)
Team(teamID, name)

What code would I use to create the associative entity table called playerTeam.

I have tried the following:

CREATE TABLE
(
playerID INT NOT NULL, 
teamID INT NOT NULL,
PRIMARY KEY(playerID, teamID)
);

I do not know how to connect up the tables in this instance.

Solution

  • Try this:

    CREATE TABLE teamPlayer
(
playerID INT NOT NULL, 
teamID INT NOT NULL,
PRIMARY KEY(playerID, teamID)
);

alter table teamPlayer
add constraint 
    fk_teamPlayer__Player foreign key(playerID) references Player(personID);

alter table teamPlayer
add constraint 
    fk_teamPlayer__Team foreign key(teamID) references Team(teamID);

    Or this:

    CREATE TABLE teamPlayer
(
playerID INT NOT NULL, 
teamID INT NOT NULL,
PRIMARY KEY(playerID, teamID),

constraint fk_teamPlayer__Player
foreign key(playerID) references Player(personID),

constraint fk_teamPlayer__Team 
foreign key(teamID) references Team(teamID)

);

    If you don't need to name your foreign keys explicitly, you can use this:

    CREATE TABLE teamPlayer
(
playerID INT NOT NULL references Player(personID), 
teamID INT NOT NULL references Team(teamID),
PRIMARY KEY(playerID, teamID)
);

    All major RDBMS pretty much complied with ANSI SQL on relationship DDL. Everyone is identical

    CREATE THEN ALTER(explicitly named foreign key):

    CREATE(explicitly named foreign key):

    CREATE(auto-named foreign key):