Referencing a composite primary key

Foreign keys have to match the primary/unique key they reference column for column. Since the primary key of BOOKING_REFERENCE is (REFERENCE_ID, CUST_ID), that means that the foreign key from BR_FLIGHT to BOOKING_REFERENCE must consist of 2 columns also. That means you need to add CUST_ID to the BR_FLIGHT table - either that or your BOOKING_REFERENCE primary key is wrong and should just be (REFERENCE_ID).

That said, it doesn't make sense to have foreign keys defined in both directions as you do. The "child" table should reference the "parent" and not vice versa.

When you reference composite primary key with a foreign key you must reference the whole key. In your case you should alter the BR_FLIGHT table and add the CUST_ID column

 ALTER TABLE BR_FLIGHT       ADD       (        CUST_ID NUMBER(10)NOT NULL       );

And reference the full key as:

FOREIGN KEY  (REFERENCE_ID, CUST_ID) REFERENCES BOOKING_REFERENCE (REFERENCE_ID, CUST_ID)

Now DDL for BR_FLIGHT table will be:

CREATE TABLE BR_FLIGHT (REFERENCE_ID NVARCHAR(10) NOT NULL ,CUST_ID NUMBER(10)NOT NULL,FLIGHT_ID NVARCHAR (10) NOT NULL,PRIMARY KEY(REFERENCE_ID, FLIGHT_ID),FOREIGN KEY  (REFERENCE_ID, CUST_ID) REFERENCES BOOKING_REFERENCE (REFERENCE_ID, CUST_ID));

As Tony Andrews pointed out you don't need the foreign part in the BOOKING_REFERENCE table. It should look like this:

CREATE TABLE BOOKING_REFERENCE (REFERENCE_ID NVARCHAR(10) NOT NULL,CUST_ID NUMBER(10)NOT NULL,STATUS NVARCHAR (1), NOT NULL,PRIMARY KEY(REFERENCE_ID, CUST_ID));

You have to put the UNIQUE restriction:

CREATE TABLE BOOKING_REFERENCE (REFERENCE_ID NVARCHAR(10) NOT NULL UNIQUE,CUST_ID NUMBER(10)NOT NULL,STATUS NVARCHAR (1), NOT NULL,PRIMARY KEY(REFERENCE_ID, CUST_ID),FOREIGN KEY(REFERENCE_ID) REFERENCES BR_FLIGHT(REFERENCE_ID):FOREIGN KEY (CUST_ID) REFERENCES CUSTOMER(CUST_ID);CREATE TABLE BR_FLIGHT (REFERENCE_ID NVARCHAR(10) NOT NULL ,FLIGHT_ID NVARCHAR (10) NOT NULL,PRIMARY KEY(REFERENCE_ID, FLIGHT_ID),FOREIGN KEY (REFERENCE_ID) REFERENCES BOOKING_REFERENCE(REFERENCE_ID)FOREIGN KEY (FLIGHT_ID) REFERENCES FLIGHT(FLIGHT_ID));