CODEWITHSUNDEEP
sqldatabasemany-to-manyrelationship
batsta13
batsta13

Reputation: 539

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.

Upvotes: 5

Views: 19515

Answers (2)

Michael Buen
Michael Buen

Reputation: 39393

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

Upvotes: 8

Konstantin Pereiaslov
Konstantin Pereiaslov

Reputation: 1814

You don't specify a kind of a relationship when you create the table, unless you're using FOREIGN KEY. In your case you're actually missing table name in your query, it should be like that

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

Upvotes: 0

Related Questions