Take the 2-minute tour ×

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!

share|improve this question
add comment

2 Answers

up vote 2 down vote accepted

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.

share|improve this answer
No wonder I couldn't figure out how to do it! Thanks for stopping me from wasting my time –  chama May 5 '10 at 20:12
add comment
  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)
share|improve this answer
Just adding a foreign key references doesn't magically fill the BuildingID column in the Apartment table. Of course - if you manually add the max. number of floors to each Apartment, then the CHECK constraint is a piece of cake - but that unnecessarily duplicates information that's already in the Building table.... –  marc_s May 5 '10 at 21:02
Surely it duplicates information, no argument here. If you need rock solid integrity, this is the only way to go. If you tolerate some invalid data, then go for triggers. –  AlexKuznetsov May 5 '10 at 21:40
add comment

Your Answer


