0x1337 0x1337 - 1 month ago 6
SQL Question

Attribute that implies different foreign keys

I've faced with a problem while studied databases.
Suppose there are relations:

SomeEntity (
id int PRIMARY KEY
);

CompletelyAnotherEntity (
id int PRIMARY KEY
);

Competition (
competitor1 FOREIGN KEY REFERENCES SomeEntity,
competitor2 FOREIGN KEY REFERENCES CompletelyAnotherEntity
winner FOREIGN KEY REFERENCES ???
);


What type should has
winner
attribute in the
Competition
relation? What should it reference?

I came up with a solution of defining two attribute
winner1
references
SomeEntity
and
winner2
references
CompletelyAnotherEntity
. One of them can be
null
, but both simultaneously cannot be defined. I think it is not a correct solution, so help me resolve this issue properly.

Answer

I'd use two relation variables for GladiatorWinsCompetition and AnimalWinsCompetition respectively (you could get away with having just one and inferring the other).

Cleaning up your schema a little (use more useful entity names, make attribute names consistent across relvars, always include data types):

Gladiator (
  GladiatorId int PRIMARY KEY
);

Animal (
  AnimalId int PRIMARY KEY
);

Competition (
  GladiatorId int FOREIGN KEY REFERENCES Gladiator, 
  AnimalId int FOREIGN KEY REFERENCES Animal,
  PRIMARY KEY ( GladiatorId, AnimalId )
);

GladiatorWinsCompetition (
  GladiatorId int, 
  AnimalId int,
  PRIMARY KEY ( GladiatorId, AnimalId ),
  FOREIGN KEY ( GladiatorId, AnimalId ) REFERENCES Competition
);

AnimalWinsCompetition (
  GladiatorId int, 
  AnimalId int,
  PRIMARY KEY ( GladiatorId, AnimalId ),
  FOREIGN KEY ( GladiatorId, AnimalId ) REFERENCES Competition
);
Comments