CODEWITHSUNDEEP
sql-serverconstraintscheck-constraints
chama
chama

Reputation: 6163

SQL Server Constraints Across Tables

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!

Upvotes: 2

Views: 672

Answers (2)

A-K
A-K

Reputation: 17080

  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)

Upvotes: 0

D&#39;Arcy Rittich
D&#39;Arcy Rittich

Reputation: 171411

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.

Upvotes: 3

Related Questions