CODEWITHSUNDEEP
sqlsql-server
w0051977
w0051977

Reputation: 15787

Foreign key references one of many tables

Say I have a structure like this:

Sport:

reference int identity not null, primary key (ID),
Duration int, --e.g. football is 90 minutes
Players int,
SportID int,
SportType int --0 for football and 1 for tennis

Tennis

Reference int IDENTITY NOT NULL,
TurfType int,
-- + more fields related to Tennis

Football:

Reference int IDENTITY NOT NULL,
-- + more fields related to Football

Sport.SportID references either Tennis or Football or Gymnastics etc. The only way I can think of doing this is having a SportType in the Sport table to say what table Sport.SportID references.

This doesn't look right though. Is this the best approach?

Upvotes: 1

Views: 99

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Having a SportType column is reasonable. You can approach this using a structure such as:

create table Sports (
    SportsId int identity not null, primary key (ID),
    Duration int, --e.g. football is 90 minutes
    Players int,
    SportType varchar(10),
    SportId int,
    check (SportType in ('Football', 'Tennis'),
    FootballId as (case when SportType = 'Football' then SportId end),
    TennisId as (case when SportType = 'Tennis' then SportId end),
    foreign key (FootballId) references Football(FootballId),
    foreign key (Tennis) references Tennis(Tennis)
);

Note that this uses computed columns for the foreign key reference to ensure referential integrity.

Upvotes: 2

Eda
Eda

Reputation: 208

Are you trying to give the name of a sport, so that the database displays the details(duration, type of field played on, number of players etc)?

Upvotes: 0

Related Questions