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

Question

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);

The error is obvious: column `room_charges` in table `room_details` is not primary key or unique. It probably really isn't unique; the primary key of the table is `room_id`, and "charge" probably means cost per night or similar, which may be the same for several different rooms. How to fix this is less obvious; the only info you shared with us is code that does NOT do what is required. From that alone, we can't deduce what IS required. — , Dec 04 '21 at 17:30
I ran the alter command to make room_charges NOT NULL .. even after that it is giving me this error. Please do tell me what should I share with you more? — Shaheer, Dec 04 '21 at 17:34
@mathguy Sorry I replied incorrectly. Actually, I thought FK should be NOT NULL always and was mixing this with Unique contraint. I got you. Does this mean I have to make room_charges unique in order to add FK constraint on it? Then I guess there is an error in my ERD design. Can you tell me the reason why it is compulsory to add a unique constraint on FK? — Shaheer, Dec 04 '21 at 17:46
Not on FK - on PK. The FK doesn't have to be unique OR non-null. The referenced column does NOT need to be PK in its table (although it often is), but it DOES have to be unique. Again, I can't tell you how to fix your design since you didn't actually tell us anything about what you need; but almost surely the keys (unique/primary, and foreign) should NOT be on the `room_charges` column. — , Dec 04 '21 at 18:25

MT0 · Accepted Answer · 2021-12-05T10:10:06.117

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(days_stayed * 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).

Thank you for your answer. Everything looks okay. Just one thing. Firstly, is there a reason of excluding Total_charges attribute from CUSTOMER_RECEIPT Table? Secondly, If I am adding this attribute then it equals 'service_charges'+'room_charges*days_stayed'. Can you tell me how can i enforce it? I am thinking of trigger, but couldn't write one. — Shaheer, Dec 05 '21 at 03:10
@Shaheer Still don't violate 3NF. If `total_charges` is calculated from other data in other tables then calculate it rather than trying to store a value using a trigger. I've updated the answer to multiply by `days_stayed`. — MT0, Dec 05 '21 at 10:11

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

1 Answers1