Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2013
    Posts
    3

    Unanswered: single field as a foreign key to two different tables

    Hi

    I have three tables which are co-related
    1. Hotel table( HOTEL_ID)
    PRIMARY KEY (HOTEL_ID)

    2. Room category table(ROOM_TYPE,HOTEL_ID)
    PRIMARY KEY (ROOM_TYPE, HOTEL_ID)
    FOREIGN KEY (HOTEL_ID) REFERENCES HOTEL(HOTEL_ID)

    3. Room table(ROOM#, HOTEL_ID,ROOM_TYPE)
    PRIMARY KEY (ROOM#, HOTEL_ID)
    FOREIGN KEY (HOTEL_ID) REFERENCES HOTEL(HOTEL_ID),
    FOREIGN KEY (ROOM_TYPE,HOTEL_ID) REFERENCES ROOM_CATEGORY(ROOM_TYPE,HOTEL_ID)

    I created all three tables successfully. I also loaded both table Hotel and room category, but when i tried to load data in ROOM table it giving me error
    Rejected - Error on table ROOM.
    ORA-02291: integrity constraint (ROOM_FK2) violated - parent key not found Record. However I checked (even i copied) contents are same in ROOM table as mentioned in ROOM_CATEGORY.

    When i tried to drop constraint ROOM_fk2... I loads data successfully but it is not solving my problem.

    I understand this is because i have given single column(hotel_id) as foreign key to two different tables. But I dont know how to do it as all three tables are interconnected.

    Please give advise.
    Last edited by COOLMIND; 03-01-13 at 13:15.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It works OK for me.
    Code:
    SQL> insert into hotel values(1);
    
    1 row created.
    
    SQL> insert into room_category values ('Single',1);
    
    1 row created.
    
    SQL> insert into room values (2,1,'Single');
    
    1 row created.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2013
    Posts
    3
    Quote Originally Posted by anacedent View Post
    It works OK for me.
    Code:
    SQL> insert into hotel values(1);
    
    1 row created.
    
    SQL> insert into room_category values ('Single',1);
    
    1 row created.
    
    SQL> insert into room values (2,1,'Single');
    
    1 row created.
    Thanks for your quick reply...I am trying to load data using sqlldr. I make the data on excel sheet first than I saved as .csv file. So does this problem occurs because I didn't mention contents within single quote ' ' for ROOM_TYPE ie varchar2??

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    parent record MUST exist before you can load any child record.

    Since I don't know exactly what you did do, therefore I can't know what you did wrong.
    Did you do error of commission or error of omission?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Feb 2013
    Posts
    3
    Thanks Anacedent,

    As you suggested there was no problem in schema creation, that syntax worked fine. the problem was I was not using double quote while using loader as I mentioned in control file.
    So for ROOM_TYPE (varchar2) I used double quote in csv file and load data with loader. Now it is working fine. Thanks for your direction.
    COOLMIND

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •