1 to 1 relationship

Question

Worker can have only one vehicle and Vehicle can belong to only one worker at a time. There are 3 possible implementations I know:
1.

Vehicle(Id, Number)
Worker(Id, Name, VehicleId)
--> This allows two workers have the same vehicle.

2.

Worker(Id, Name)
Vehicle(Id, Number, WorkerId)
--> This allows worker to have two vehicles.

3.

   Worker(Id, Name)
   Vehicle(Id, Number)
   WorkersVehicles(Id, VehicleId, WorkerId)
    --> This allows each worker to have many vehicles and each vehicle to belong to many workers.

None of the above can describe the desired 1:1 relationship.

How can I describe this 1:1 relationship in the DB and in the Entity Framework?

Answer 1

Defining a data model that enforces a 1:1 relationship through constraints only is not possible, as this requires a circular reference, which would mean that you would never be able to insert one side of the relationship until the other side existed.

While it's possible to get around this with trickery (dropping constraints, using RDBMS-specific commands like Oracle's deferred constraints), it's not possible to do in the traditional sense. The closest you can get is 1:0..1.

The following are models that represent the various combinations of Worker:Vehicle:

0..1:1

Worker (ID, VehicleID unique constraint)
Vehicle (ID)

1:0..1

Worker (ID)
Vehicle (ID, WorkerID unique constraint)

0..1:0..1

Worker (ID)
Vehicle (ID)
WorkerVehicle (WorkerID, VehicleID) <-- primary key on one column, 
                                        unique constraint on the other

Unfortunately, since EF does not support unique constraints (or, rather, it does not recognize or enforce them), you'll always end up with a collection on the other side of the relationship rather than a single entity.

Answer 2

You would probably need to use a unique constraint that says employees must have a unique VehicleId

It would seem that EF doesn't directly support unique constraints as per this SO post How can I add constraints to an ADO.NET Entity?

Answer 3

As @Adam described you need to make FK unique to enforce one-to-one relation and because EF doesn't support unique constrains yet the only way to make this in EF is:

Worker(ID)
Vehicle(ID) <-- PK and FK to worker

Placing FK to PK will enforce uniqueness. The workaround is using:

Worker(ID)
Vehicle(ID, WorkerID) <-- WorkerID is FK with unique constraint in the database

Once this is mapped to EDMX you will remove Vehicles navigation property from Worker. If you assign one vehicle to two workers database will throw exception. The disadvantage of this workaround is that you don't have access to vehicle from worker (you don't have navigation property).

Anyway one-to-one relation is rare - it should be mostly used for "is-a" one more rarely for "has-a" scenario. In some cases I think it is reasonable to use one-to-many instead of one-to-one and control validation in the application logic. If requirement change in the future adding a new related entity to a principal will be just a matter of changing validation instead of changing half of the application. You can decide if this can be your case.

Answer 4

This kind of thing can be handled in either the database or in your code. If you do it in code then just create a rule before attempting to persist your objects that checks to see if the vehicle belongs to another worker. If so throw an exception. If you choose to add the rule in the database as well then that should be done in a trigger on the table that would contain the foreign key, that would again check to see if the vehicle belongs to another worker or not and if it does it should raise an error. Entity Framework shouldn't be used to handle the business logic.