I’d like to know the reasoning behind the Rails way of not having support for multi-column foreign keys.
Let’s take a sample schema with the following entities: Apartment Building Tenant
These are the relationships: Apartment may have one or more buildings. Building belongs to an Apartment. Tenant belongs to an Apartment. Building will have one or more tenants. A tenant can live in one or more buildings.
The above relationships can be mapped into tables as follows: Building will have a foreign key for apartments. A tenant will have a foreign key for apartments. There will be a many-to-many between tenant and buildings.
Now, the issue here is: the building_id and tenant_id in the building_tenants table does not guarantee that those belong to the same apartment.
This consistency can be guaranteed by having an apartment_id column in the building_tenants table and then having foreign keys for (building_id, apartment_id) -> buildings and (tenant_id, apartment_id) -> tenants. However, rails does not support this by default. I just want to know what is the Rails rationale behind this. Should be enforce this constraint only on the application code itself and not on the database? Or, is there any other way we can solve this problem?