Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    2

    Question Help with Foreign Keys and Tables

    Hey I have a question here about my tables. Would appreciate the help!

    The question is about my foreign key for Hotel_id in the Place_Reservation table. I have it referencing hotel, but should I reference the foreign key to the hotel id in Has_Room. Has_Room is a weak entity of Hotel.




    CREATE TABLE Hotel (
    hotel_id varchar(20),
    hotel_location varchar(40),
    hotel_name varchar(30),
    hotel_capacity integer,
    hotel_rating integer,
    pets_allowed bit,
    primary key(hotel_id))

    CREATE TABLE Has_Room (
    room_available bit,
    room_type varchar(30),
    room_price real,
    guest_count int,
    room_number int,
    hotel_id varchar(20),
    PRIMARY KEY (room_number, hotel_id),
    FOREIGN KEY (hotel_id) REFERENCES Hotel
    ON DELETE CASCADE)

    CREATE TABLE Place_Reservation (
    room_number int,
    hotel_id varchar(20),
    reservation_id integer,
    to_date date,
    from_date date,
    user_id varchar(30) NOT NULL,
    PRIMARY KEY (reservation_id),
    FOREIGN KEY (user_id) REFERENCES Customer,
    FOREIGN KEY (room_number) references Has_ROOM,
    FOREIGN KEY (hotel_id) references Hotel)

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171

    Re: Help with Foreign Keys and Tables

    The foreign key of a child table must reference a complete primary or unique key of the parent. So your foreign key from Place_Reservation to Has_Room should be like this:

    CREATE TABLE Place_Reservation (
    room_number int,
    hotel_id varchar(20),
    reservation_id integer,
    to_date date,
    from_date date,
    user_id varchar(30) NOT NULL,
    PRIMARY KEY (reservation_id),
    FOREIGN KEY (user_id) REFERENCES Customer,
    FOREIGN KEY (room_number, hotel_id) references Has_ROOM)

    There is then no need for any foreign key to Hotel.

    Incidentally, it is unusual (wrong, really) to use verbs in table names. I would rename Has_Room to Room and Place_Reservation to Reservation.

  3. #3
    Join Date
    Nov 2003
    Posts
    2

    Re: Help with Foreign Keys and Tables

    Hey Thanks a lot! That really clarified things. Also thanks for the tip about the verb thing. Kind of my first time doing this!

    Originally posted by andrewst
    The foreign key of a child table must reference a complete primary or unique key of the parent. So your foreign key from Place_Reservation to Has_Room should be like this:

    CREATE TABLE Place_Reservation (
    room_number int,
    hotel_id varchar(20),
    reservation_id integer,
    to_date date,
    from_date date,
    user_id varchar(30) NOT NULL,
    PRIMARY KEY (reservation_id),
    FOREIGN KEY (user_id) REFERENCES Customer,
    FOREIGN KEY (room_number, hotel_id) references Has_ROOM)

    There is then no need for any foreign key to Hotel.

    Incidentally, it is unusual (wrong, really) to use verbs in table names. I would rename Has_Room to Room and Place_Reservation to Reservation.

Posting Permissions

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