SQL Server Constraints Across Tables

Question

I have a SQL Server database with an Apartment table (which has columns FloorNum and BuildingID) and an ApartmentBuilding table (with column NumFloors). Is there any way to set up a constraint (using the SQL Server UI) to check that Apartment.FloorNum is greater than ApartmentBuilding.NumFloors?

I tried this:

FloorNum > ApartmentBuilding.NumFloors

but now I realize that I somehow have to join the columns on the BuildingID, but I have no idea how to do that within a constraint.

Thanks for your help!

Answer 1

You can't do this with a CHECK CONSTRAINT since it requires data from another table. You would handle this with an INSERT/UPDATE trigger.

Answer 2

1. In ApartmentBuilding table, add UNIQUE constraint on(BuildingID, NumFloors)
2. In Apartment table, add column NumFloorsInBuilding
3. In Apartment table, add foreign key on (BuildingID, NumFloorsInBuilding) referring to (BuildingID, NumFloors). This guarantees that NumFloorsInBuilding is always equal to NumFloors in parent table.
4. In Apartment table, add CHECK(FloorNum < NumFloorsInBuilding)