This discussion is archived
1 Reply Latest reply: Nov 5, 2012 8:25 AM by rp0428 RSS

Small PK/FK logical error

972530 Newbie
Currently Being Moderated
Hi, this is my first DB project and all I'm trying to do is create a few tables with relationships so I can insert some data. The concept of foreign keys is a little confusing and I know there is one small logical error in my 'Booking' table in terms of a PK/FK violation(perhaps) but I can't work it out. I have been at this all day no joke.
Please could someone have a look at the code and point out the mistake?

It would really would be appreciated.

Thanks.

Create Table Hotel
(Hotel_No Char(4) Not Null,
H_Name VarChar2(20) Not Null,
H_Address VarChar2(30),
Constraint Hotel_PK Primary Key (Hotel_No));

Create Table Room
(Room_No VarChar2(4) Not Null,
Hotel_No Char(4) Not Null,
R_Type Char(1),
R_Price Number(5,2),
Constraint Room_PK Primary Key (Room_No, Hotel_No),
Constraint Room_Hotel_Fk Foreign Key (Hotel_No) References Hotel(Hotel_No));

Create Table Guest
(Guest_No Char(4) Not Null,
G_Name VarChar2(30),
G_Address VarChar2(35),
Constraint Guest_PK Primary Key (Guest_No));

Create Table Booking
(Hotel_No Char(4) Not Null,
Guest_No Char(4) Not Null,
Date_From Date Not Null,
Date_To Date,
Room_No VarChar2(4),
Constraint Booking_PK Primary Key (Guest_No, Hotel_No, Room_No, Date_From),
Constraint Booking_Guest_FK Foreign Key (Guest_No) References Guest(Guest_No),
Constraint Booking_Hotel_Room_FK Foreign Key (Hotel_No, Room_No) References Room(Hotel_No, Room_No),
Constraint Booking_Hotel_FK Foreign Key (Hotel_No) References Hotel(Hotel_No));
  • 1. Re: Small PK/FK logical error
    rp0428 Guru
    Currently Being Moderated
    Welcome to the forum!

    You have posted to the wrong forum. This is the sql developer forum and your question has nothing to do with sql developer.
    Please mark this question ANSWERED and report it in the sql and pl/sql forum
    SQL and PL/SQL
    >
    Hi, this is my first DB project and all I'm trying to do is create a few tables with relationships so I can insert some data. The concept of foreign keys is a little confusing and I know there is one small logical error in my 'Booking' table in terms of a PK/FK violation(perhaps) but I can't work it out.
    >
    When you post in the other forum be sure to include your 4 digit Oracle version (result of SELECT * FROM V$VERSION).

    You should also mention what you think the 'logical' error is and what you have tried. Just saying "I can't work it out" doesn't help anyone know what you might have tried to work it out.

    For information about foreign keys, what they are and what they are used for see 'Foreign Key Constraints' in the Database Concepts doc
    http://docs.oracle.com/cd/E18283_01/server.112/e16508/datainte.htm#CHDIIGBG

    I don't see anything 'logical' problem but normally ALL columns used in a primary would be defined as NOT NULL. In the booking table the ROOM_NO column is nullable.
    Room_No VarChar2(4),
    Also, the model does violate two best practices. One best practice is to use VARCHAR2, not CHAR, for storage. You are using CHAR. THe other best practice is to name columns appropriately and to use the proper datatypes for the data that wil be stored. You have columns with '_NO' in the name (HOTEL_NO, ROOM_NO, GUEST_NO). Will those columns hold numeric data or characater data? If numeric data the datatype should be numeric.

    If the above does not answer you question repost it in the proper forum. In any event mark the question ANSWERED to sql developer volunteers won't waste their time on it.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points