title: Establishin a relation between two tables without the primary key - foreign key relation (ORACLE)
body: <p>I have the following two tables:</p>
  
  <p>ROOMS: <br></p>
  
  <pre><code>ID_Rooms PK number, 
  RoomNumber number, 
  COD_RoomType number, 
  RoomPrice number,
  Floor number
  </code></pre>
  
  <p>D_ROOMTYPE:</p>
  
  <pre><code>ID_d_RoomType PK number,
  RoomType varchar2,
  COD_RoomType number
  </code></pre>
  
  <p>The first table stores information about the hotel rooms, and the second one the codification for the room types (double room, triple room, family room, etc.). The data in the second table will be used to identify the room types in the first one.</p>
  
  <p>I need to establish a relation between the two tables, but not based on the primary/foreign key relation, but on the COD_RoomType column which appears in both.</p>
  
  <p>I tried:</p>
  
  <pre><code>ALTER TABLE rooms
  ADD CONSTRAINT rooms_FK1 FOREIGN KEY (COD_RoomType) REFERENCES d_RoomType(COD_RoomType) enable; 
  </code></pre>
  
  <p>but i get the following error report:</p>
  
  <blockquote>
    <p>Error report:<br> SQL Error: ORA-02270: no matching unique or primary
    key for this column-list
    02270. 00000 -  "no matching unique or primary key for this column-list"<br></p>
    
    <p>*Cause:    A REFERENCES clause in a CREATE/ALTER TABLE statement
               gives a column-list for which there is no matching unique or primary
               key constraint in the referenced table.<br></p>
    
    <p>*Action:   Find the correct column names using the ALL_CONS_COLUMNS
               catalog view</p>
  </blockquote>
  
  <p>Is there any method to establish a relation between the two tables on the COD_RoomType column without using the primary/foreingn key? If so what other modifications must I do and what will the SQL code look like?</p>
  
  <p>Thank you,
  Calin</p>