CODEWITHSUNDEEP
mysqlsqlprimary-key
batsta13
batsta13

Reputation: 539

SQL: two tables with same primary key

I have two tables:

What code would I use so I could use the personID in Person as a primary key in both the Person and Player tables?

I understand that playerID wold also have to be a foreign key in Player as well. Any ideas?

Upvotes: 3

Views: 10468

Answers (4)

Jonathan Leffler
Jonathan Leffler

Reputation: 753505

It is not clear that you need two tables for this information, unless there are people represented who are not players. Let's assume that is the case (other people can be coaches, parents, referees, etc). Further, even though coaches were indeed born, their date of birth is not material to the system (so there's no need to transfer the date of birth back to the Person table). Also, assume that you are dealing with people who only attend one school; if they were at a different school last year, the Player record will have been updated in between the seasons. (If you need history information about schools attended in different years, you will need a different table structure.) It is also plausible to suppose that in due course you'll add more fields to the Player table.

In that case, you need to link the Player data back to the right person:

CREATE TABLE Player
(
    PlayerID     INTEGER NOT NULL PRIMARY KEY REFERENCES Person(PersonID),
    DateOfBirth  DATE NOT NULL,
    School       VARCHAR(20) NOT NULL REFERENCES School(SchoolName)
);

I'm hypothesizing that the list of schools is finite. You might use a SchoolID integer instead of the school name for joining; that tends to be more compact.

Upvotes: 2

Nivas
Nivas

Reputation: 18344

First you should havepersonID in the Player table as well.

Once that is done, you can have the personID as the primary key of the Player table as well or can have a separate playerID column, and this can be the primary key.

Which one you choose depends on your application's requirements. If you think a player will be associated with only one person and definitely one person then personId can be the primary key.

But in many cases you would need a separate playerId (and I suggest having one). For example if are not modeling computer games, all players may not be persons - personId will be null. Also if you model a basketball player different from a soccer player, a person who plays both these games can have multiple entries in the player table (multiple records with same personId).

Upvotes: 2

No'am Newman
No'am Newman

Reputation: 6477

I suggest that you move the date of birth field from the 'Player' table to the 'person' table (after all, a person doesn't have multiple dates of birth!), then add the 'personID' field to the 'player' table. If a person has multiple schools (primary, middle high, high), then the primary key of the 'player' table should be personID + school.

There is definitely no problem in having one table's primary key serve as the primary key in a second table; such a second table could help save disk space and improve access time. Let's say that there is a large text field to be stored for some - but not all - people. Storing this field in a secondary table (with the same primary key) would mean that only people that need this field would have records in the secondary table.

Upvotes: 0

Sebas
Sebas

Reputation: 21522

Could you add a personID field and manage it the same way you manage the person table? Each time you insert a player, you would have to insert the corresponding person with the same id.

of course if your person concept is abstract then it should definetly be in the same table

Upvotes: 0

Related Questions