SQL how to handle a many to many relationship

database, many-to-many, relationship, sql

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):

- Postgresql: http://www.sqlfiddle.com/#!1/0a096

- MySQL: http://www.sqlfiddle.com/#!2/0a096

- Oracle: http://www.sqlfiddle.com/#!4/0a096

- SQL Server: http://www.sqlfiddle.com/#!3/0a096

CREATE(explicitly named foreign key):

- Postgresql: http://www.sqlfiddle.com/#!1/46ebb

- MySQL: http://www.sqlfiddle.com/#!2/46ebb

- Oracle: http://www.sqlfiddle.com/#!4/46ebb

- SQL Server: http://www.sqlfiddle.com/#!3/46ebb

CREATE(auto-named foreign key):

- Postgresql: http://www.sqlfiddle.com/#!1/82742

- MySQL: http://www.sqlfiddle.com/#!2/82742

- Oracle: http://www.sqlfiddle.com/#!4/82742

- Sql Server: http://www.sqlfiddle.com/#!3/82742

Problem

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.

Original source