Unique constraint

[GarryBrown]

Hello,

I am slightly confused about one task regarding usage of unique constraint. I've got two tables:

TABLE ROOM
TABLE BUILDING

The requirement is that table ROOM has room code which must be unique only within a particular building (i've got 4 buildings). Not really sure how to do that. Any ideas?

Thanks is advance.

[NogDog]

You can have a unique index across multiple columns, so presumably your "room" table would have a unique index on the combination of room number and building id.

[GarryBrown]

NogDog,

Thanks for the reply. Maybe you could give me some example how to do that?
I've got 4 buildings:
1
2
3
4

Each building has 4 rooms:
A
B
C
D

Room code is unique only within a particular building:
1A, 1B, 1C, 1D.
2A, 2B, 2C, 2D.
3A, 3B, 3C, 3D.
4A, 4B, 4C, 4D.

How can I make a constraint from this?

[gk53]

so, add table BuildingRooms with biuldingId, RoomId and it is unique compbination