Search code examples
sql-servermany-to-manyone-to-one

Zero..One to One Relationship SQL

I am trying to understand better 1-1 vs 1-0..1 relationship.

Let's say I have a Car, SteeringWheel, and Sunroof

  • 1 Car must have 1 SteeringWheel and 1 SteeringWheel must belong to 1 Car; therefore this is 1:1 relationship
  • 1 Car can have 0 or 1 Sunroof and a Sanroof must belong to 1 Car; therefore this is 0..1:1 relationship.

But in terms of entities, I am not sure how to implement 0..1:1 relationship.

Below is 1:1 relationship btw Car and Sunroof but it should be 1:0..1.

How to make it 1:0..1 relationship?

-------------- ONE TO ONE --------------
CAR                         SUNROOF
---------------             -------------
CarId PK    -1----+         SunroofId
Model             |         Diameter
Year              +--0..1-  CarId FK, UNIQUE
Solution

  • From a data perspective your design supports this correctly. To achieve the relationship beyond 1:1 you would just use a left join from Car to Sunroof. That way if there is no row for sunroof you get NULL. But the unique constraint on CarId prevents more than a single sunroof for a given car.