Circular Foreign key

Question

I have two tables, Contestants and Competition. A competition can have 0 to many contestants, only one contestant is a winner of the competition. In my database model there is a 1 to N and a 1 to 1 relationship between these two tables but when I try to implement it in SQL there is the foreign key issue when inserting data.

The Contestants table has a foreign key to the Competition (CompetitionID) since there can be competitions in different years, countries. The Competition table has a foreign key to the contestant which is the winner.

How can I solve this?

Answer 1

It's perfectly normal to have multiple foreign key constraints between each pair of tables in a relational database, even if they point to each other.

There are two well-known solutions for these cases.

1. Use at Least One Nullable Column

create table competition (
  id int primary key not null,
  name varchar(10) not null,
  winner_id int -- nullable
);

create table contestant (
  id int primary key not null,
  score int not null,
  competition_id int not null references competition (id)
);

alter table competition 
add constraint fk1 foreign key (winner_id) references contestant (id);

Then it's trivial to:

• Insert the competition(s) making sure winner_id is null at first.
• Insert the contestants.
• Update the competition(s) with the winner_id, that can now reference the contestant.

This up side of this solution is that it works in all relational databases, even in the low end ones.

The down side of it is that the database model does not enforce each competition to have a winner.

2. Use Deferrable Constraints

The SQL Standard defines a clean solution for this case: it indicates that constraints can be deferrable. That means the validation of a constraint can be delayed until the end of the transaction.

Now, as far as I know only two databases implement this section of the SQL Standard. Namely: PostgreSQL and Oracle.

You can do:

create table competition (
  id int primary key not null,
  name varchar(10) not null,
  winner_id int not null -- winner is now enforced
);

create table contestant (
  id int primary key not null,
  score int not null,
  competition_id int not null references competition (id)
);

alter table competition 
add constraint fk1 foreign key (winner_id) references contestant (id)
deferrable initially deferred;

In this case you can do:

• Start a transaction (necessary!).
• Insert into competition -- with winner_id this time. Don't worry; they won't be checked just yet...
• Insert all contestants.
• Commit the transaction. At this moment, all constraints marked as deferrable initially deferred will be validated. If the validation succeeds all data will be committed; if the validation fails, the whole transaction will be rolled back. It's quite clean, actually.