SQL: two tables with same primary key

I have two tables:

• Person (personID, name, address, phone, email)
• Player (dateOfBirth, school)

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?

Answer

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.