title: Unexpected result with Constraints - Database SQL
body: <p>Trying to figure out why this constraint doesn't work as expected and attempting to find solution.</p>
  
  <p>A description of tables and attributes below:</p>
  
  <pre><code>Table Hotel
  hotel_id - primary key - INT(5)
  
  Table Room
  room_id - primary key - INT(5)
  hotel_id - primary key - INT(5)
  room_type - VARCHAR(25)
  
  Table RoomType
  room_type - primary key - VARCHAR(25)
  hotel_id - primary key - INT(5)
  type_description - VARCHAR(100)
  price - INT(5)
  </code></pre>
  
  <p>So the constriants:</p>
  
  <pre><code>room.room_type references roomtype.room_type
  room.hotel_id references hotel.hotel_id
  roomtype.hotel_id references hotel.hotel_id
  </code></pre>
  
  <p>So the desired result of this would be that for all the hotels will have room types that are unique to them. Then hotels will also have rooms that are linked to a roomtype. So example data for these would be:</p>
  
  <pre><code>Hotel:
  hotel_id
  1
  2
  
  
  Room:
  room_id     hotel_id     room_type
  1           1            Single
  2           2            Double
  
  RoomType:
  room_type  hotel_id    type_description   price
  Single     1           Random             500
  Double     2           Random             500
  </code></pre>
  
  <p>So if I tried adding this row to the Rooms I would expect it not to work due to constraints. As for hotel_id 1 there is no roomtype Double in the roomtypes table. This still works and doesn't get stopped by the constraint.</p>
  
  <pre><code>Room
  room_id   hotel_id  room_type
  3         1         Double
  </code></pre>
  
  <p>So how would you do this?</p>