Table Relationship Problems

[Guest_imported]

The problem I am having is that it is possible to add a product to the Order_product table that isn't supplied by supplier the whom the order is placed with. For Example if OrderID 1 was placed with SupplierID 1, then I shouldn't be-able to add a product that doesn't have SupplierID 1. Any ideas?


CREATE TABLE SUPPLIER (
SUPPLIERID NUMBER(6) NOT NULL,
SUPPLIERNAME VARCHAR(12),
ADDRESS1 VARCHAR(15),
ADDRESS2 VARCHAR(15),
TOWNCITY VARCHAR(15),
COUNTY VARCHAR(15),
POSTCODE VARCHAR(8),
TELEPHONE NUMBER(12),
FAX NUMBER(12),
CONSTRAINT SUPPLIER_PRIMARY_KEY PRIMARY KEY (SUPPLIERID));

CREATE TABLE PRODUCT (
BARCODE NUMBER(18) NOT NULL,
DESCRIPTION VARCHAR(20),
PACKSIZE VARCHAR(10),
PRICE NUMBER(3,2),
STOCKLEVEL NUMBER(3),
BUYINGRPRICE NUMBER(3,2),
SUPPLIERID NUMBER(6) NOT NULL,
DEPARTMENTID NUMBER(2) NOT NULL,
CONSTRAINT PRODUCT_FOREIGN_KEY FOREIGN KEY (SUPPLIERID) REFERENCES SUPPLIER (SUPPLIERID),
FOREIGN KEY (DEPARTMENTID) REFERENCES DEPARTMENT (DEPARTMENTID),
CONSTRAINT PRODUCT_PRIMARY_KEY PRIMARY KEY (BARCODE));

CREATE TABLE ORDER (
ORDERID NUMBER(6) NOT NULL,
ORDERDATE DATE,
TOTALCOST NUMBER(8,2),
SUPPLIERID NUMBER(6) NOT NULL,
CONSTRAINT ORDER_FOREIGN_KEY FOREIGN KEY (SUPPLIERID) REFERENCES SUPPLIER (SUPPLIERID),
CONSTRAINT ORDER_PRIMARY_KEY PRIMARY KEY (ORDERID),

CREATE TABLE ORDER_PRODUCT (
ORDERID NUMBER(6) NOT NULL,
BARCODE NUMBER(18) NOT NULL,
QUANTITY NUMBER(3),
COST NUMBER(5,2),
CONSTRAINT ORDER_PRODUCT_FOREIGN_KEY FOREIGN KEY (BARCODE) REFERENCES PRODUCT (BARCODE),
FOREIGN KEY (ORDERID) REFERENCES ORDER (ORDERID));