Have Geek Brain

SQL Server Restrictions on All Tables

I have a SQL Server database with a table Apartment

(which has columns FloorNum

and BuildingID

) and a table (which has a ApartmentBuilding

column NumFloors

). Is there a way to set the limit (using SQL Server UI) to check which is Apartment.FloorNum

greater than ApartmentBuilding.NumFloors

?

I've tried this:

FloorNum > ApartmentBuilding.NumFloors

but now I understand that I somehow need to join the columns on BuildingID

, but I don't know how to do it within the constraint.

Thanks for your help!

+2


a source to share


2 answers


You cannot do this with CHECK CONSTRAINT, as it requires data from another table. You would handle this with an INSERT / UPDATE trigger .



+3


a source


  • In ApartmentBuilding table add UNIQUE constraint (BuildingID, NumFloors)
  • In the Apartment table add the NumFloorsInBuilding column
  • In the Apartment table, add a foreign key (BuildingID, NumFloorsInBuilding) referencing (BuildingID, NumFloors). This ensures that NumFloorsInBuilding is always equal to NumFloors in the parent table.
  • In the apartment table add CHECK (FloorNum <NumFloorsInBuilding).


0


a source






More articles:


All Articles