I want to have a one-to-many relationship in which for each parent, one or zero of the children is marked as a “favorite.” However, not every parent will have a child. (Think of the parents as questions on this site, children as answers, and favorite as the accepted answer.) For example,
TableA
Id INT PRIMARY KEY
TableB
Id INT PRIMARY KEY
Parent INT NOT NULL FOREIGN KEY REFERENCES TableA.Id
The way I see it, I can either add the following column to TableA:
FavoriteChild INT NULL FOREIGN KEY REFERENCES TableB.Id
or the following column to TableB:
IsFavorite BIT NOT NULL
The problem with the first approach is that it introduces a nullable foreign key, which, I understand, is not in normalized form. The problem with the second approach is that more work needs to be done to ensure that at most one child is the favorite.
What sort of criteria should I use to determine which approach to use? Or, are there other approaches I am not considering?
I am using SQL Server 2012.
Best Answer
Another way (without Nulls and without cycles in the
FOREIGN KEY
relationships) is to have a third table to store the "favourite children". In most DBMS, you'll need an additionalUNIQUE
constraint onTableB
.@Aaron was faster to identify that the naming convention above is rather cumbersome and can lead to errors. It's usually better (and will keep you sane) if you don't have
Id
columns all over your tables and if the columns (that are joined) have same names in the many tables that appear. So, here's a renaming:In SQL-Server (that you are using), you also have the option of the
IsFavorite
bit column you mention. The unique favourite child per parent can be accomplished via a filtered Unique Index:And the main reason that your option 1 is not recommended, at least not in SQL-Server, is that the pattern of circular paths in the foreign key references has some problems.
Read a quite old article: SQL By Design: The Circular Reference
When inserting or deleting rows from the two table, you'll run into the "chicken-and-egg" problem. Which table should I insert first - without violating any constraint?
In order to solve that, you have to define at least one column nullable. (OK, technically you don't have to, you can have all columns as
NOT NULL
but only in DBMS, like Postgres and Oracle, that have implemented deferrable constraints. See @Erwin's answer in a similar question: Complex foreign key constraint in SQLAlchemy on how this can be done in Postgres). Still, this setup feels like skating on thin ice.Check also an almost identical question at SO (but for MySQL) In SQL, is it OK for two tables to refer to each other? where my answer is pretty much the same. MySQL has no partial indexes though, so the only viable options are the nullable FK and the extra table solution.