"no matching unique or primary key for this column-list" Error in SQL-CodePudding

Was creating some tables in SQL and got stuck when I had to design the following tables:

As you can see it is impossible to create Room details and services details Table without customer receipt table as they contain Receipt_no as the primary key.

Similarly, it is impossible to create a Customer Receipt table without Room_charges and Service_charges attributes without first creating the two details tables.

Hence, I first created a customer receipt table but without FK constraints on Room_Charges and Service_charges and then I created Services Details and Room Details tables.

Later, using ALTER command I tried to add FK Constraints on Customer Receipt table but it gives me this error

ORA-02270: no matching unique or primary key for this column-list

Now, after researching a bit about it on StackOverflow, there might be three possible cases as mentioned in the approved answer (@ Oracle (ORA-02270) : no matching unique or primary key for this column-list error )

I think my case is number 3 as I have ensured the first two cases to be implemented.

Can anyone help me resolve it?

I am attaching SQL Code as a reference:

CREATE TABLE Customer_Receipt 
(
     Receipt_no VARCHAR2(12) PRIMARY KEY,
     Booking_no NUMBER NOT NULL,
     Total_charges NUMBER(12,2),

     CONSTRAINT bookingnocustrec 
         FOREIGN KEY(Booking_no) REFERENCES Room_booking (Booking_no)
);
 
CREATE TABLE Services_Details 
(
    Receipt_no VARCHAR2(12) NOT NULL,
    Service_offered VARCHAR2(8) NOT NULL,
    Service_charges NUMBER(12,2),

    PRIMARY KEY(Receipt_no, Service_offered),
    CONSTRAINT recno 
        FOREIGN KEY(Receipt_no) REFERENCES Customer_receipt (Receipt_no)
);

ALTER TABLE Services_Details 
    MODIFY Service_charges NOT NULL; 
 
CREATE TABLE Room_Details 
(
    Receipt_no VARCHAR2(12) NOT NULL,
    Category_name VARCHAR2(9) NOT NULL,
    Days_stayed INT,
    Room_charges NUMBER(12,2),

    PRIMARY KEY(Receipt_no, Category_name),
    CONSTRAINT recno1 
        FOREIGN KEY(Receipt_no) REFERENCES Customer_receipt (Receipt_no),
    CONSTRAINT catname1 
        FOREIGN KEY(Category_name) REFERENCES Room_category (Category_name)
);

ALTER TABLE Customer_receipt
    ADD Room_charges NUMBER(12,2) NOT NULL;

ALTER TABLE Customer_receipt
    ADD CONSTRAINT FK_RC
        FOREIGN KEY (Room_charges) REFERENCES Room_Details (Room_charges);

CodePudding user response：

As a frame challenge.

Can anyone help me resolve it?

Yes, do not violate Third Normal Form and do not duplicate data by storing Total_Charges, Service_Charges or Room_Charges in the Customer_Receipt table when the data is already stored in the Service_Details and Room_Details tables.

If you are storing the same data in two locations then you are likely get into the situation where the data is inconsistent between those two location; just store each piece of data in a single location so there is a single source of truth in your database.

CREATE TABLE Customer_Receipt 
(
     Receipt_no VARCHAR2(12)
                CONSTRAINT custreceipt__recno__pk PRIMARY KEY,
     Booking_no CONSTRAINT custreceipt__bookingno__fk REFERENCES Room_booking
                NOT NULL
);

CREATE TABLE Services_Details 
(
  Receipt_no      CONSTRAINT servicedetails__recno__fk REFERENCES Customer_receipt
                  NOT NULL,
  Service_offered VARCHAR2(8)
                  NOT NULL,
  Service_charges NUMBER(12,2),

  CONSTRAINT servicedetails__recno_servoff__pk PRIMARY KEY(Receipt_no, Service_offered)
);

CREATE TABLE Room_Details 
(
    Receipt_no    CONSTRAINT roomdetails__recno__fk REFERENCES Customer_receipt
                  NOT NULL,
    Category_name CONSTRAINT roomdetails__catname__fk REFERENCES Room_category
                  NOT NULL,
    Days_stayed   INT,
    Room_charges  NUMBER(12,2),

    CONSTRAINT roomdetails__recno_catname__pk PRIMARY KEY(Receipt_no, Category_name)
);

If you want to display the Total_Charges, Service_Charges and Room_Charges then use a JOIN and get the data from the related tables. Something like:

SELECT cr.*,
       COALESCE(s.service_charges, 0) AS service_charges,
       COALESCE(r.room_charges, 0) AS room_charges,
       COALESCE(s.service_charges, 0)   COALESCE(r.room_charges, 0)
         AS total_charges
FROM   customer_receipt cr
       LEFT OUTER JOIN (
         SELECT receipt_no,
                SUM(service_charges) AS service_charges
         FROM   services_details
         GROUP BY receipt_no
       ) s
       ON cr.receipt_no = s.receipt_no
       LEFT OUTER JOIN (
         SELECT receipt_no,
                SUM(room_charges) AS room_charges
         FROM   room_details
         GROUP BY receipt_no
       ) r
       ON cr.receipt_no = r.receipt_no;

Or create a view (or a materialized view).