create table SUPPLIERS ( 
    SUPPLIER_ID number not null constraint suppliers_pk primary key, 
    NAME varchar2(45) not null 
)
/

create table SUPPLIER_SITES ( 
    SITE_ID number not null constraint supplier_sites_pk primary key, 
    NAME varchar2(45) not null, 
    SUPPLIER_ID number not null 
)
/

ALTER TABLE SUPPLIER_SITES ADD CONSTRAINT SUPPLIER_SITES_FK1 FOREIGN KEY (SUPPLIER_ID) REFERENCES SUPPLIERS (SUPPLIER_ID) 
/

create table SUPPLIER_AGREEMENTS ( 
    AGREEMENT_NO varchar2(30) not null constraint supplier_agreements_pk primary key, 
    SUPPLIER_ID number not null 
)
/

create table SUPPLIER_DELIVERIES ( 
    DELIVERY_ID number not null constraint supplier_deliveries_pk primary key, 
    AGREEMENT_NO varchar2(30) not null, 
    SUPPLIER_SITE_ID number not null, 
    DELIVERY_DATE date not null 
)
/

ALTER TABLE SUPPLIER_DELIVERIES ADD CONSTRAINT SUPPLIER_DELIVERIES_FK1 FOREIGN KEY (AGREEMENT_NO) REFERENCES SUPPLIER_AGREEMENTS (AGREEMENT_NO)
/

ALTER TABLE SUPPLIER_DELIVERIES ADD CONSTRAINT SUPPLIER_DELIVERIES_FK2 FOREIGN KEY (SUPPLIER_SITE_ID) REFERENCES SUPPLIER_SITES (SITE_ID) 
/

INSERT INTO SUPPLIERS(SUPPLIER_ID, NAME) VALUES(1, 'SUPPLIER 1') 
/

INSERT INTO SUPPLIERS(SUPPLIER_ID, NAME) VALUES(2, 'SUPPLIER 2') 
/

INSERT INTO SUPPLIER_SITES(SITE_ID, NAME, SUPPLIER_ID) VALUES(1, 'SUPPLIER 1 SITE A', 1) 
/


INSERT INTO SUPPLIER_SITES(SITE_ID, NAME, SUPPLIER_ID) VALUES(2, 'SUPPLIER 2 SITE B', 2) 
/

INSERT INTO SUPPLIER_AGREEMENTS(AGREEMENT_NO, SUPPLIER_ID) VALUES('111', 1) 
/

INSERT INTO SUPPLIER_AGREEMENTS(AGREEMENT_NO, SUPPLIER_ID) VALUES('222', 2) 
/

--what is the best way to prevent this insert from occurring successfully?
INSERT INTO SUPPLIER_DELIVERIES(DELIVERY_ID, AGREEMENT_NO, SUPPLIER_SITE_ID, DELIVERY_DATE) VALUES(1, '111', 2, TRUNC(SYSDATE)) 
/

I see three basic options:

- Create a M:M join table between the sites and agreements (e.g. supplier_agreement_sites)
- Add supplier_id to supplier_deliveries
- Make supplier_agreements a child of supplier_sites (or vice-versa)

M:M Join Table

This stores every agreement and site for a supplier:

create table supplier_agreement_sites (
  supplier_id number not null ,
  agreement_no varchar2(30) not null , 
  site_id number not null ,
  primary key (
    supplier_id, agreement_no, site_id 
  )
);

You can then make supplier_deliveries a child of this table. Thus you can only insert deliveries to sites for that supplier.

But this breaks 4th normal form, complicating DML on this table. Every time you register a new site for a supplier, you need to insert all it's agreements in this table too. And the reverse. Managing updates and deletes is tricky too.

Avoid this unless the other options are unworkable for you.

Add supplier_id to deliveries

create table supplier_deliveries ( 
  delivery_id number not null 
    constraint supplier_deliveries_pk primary key, 
  agreement_no varchar2(30) not null, 
  supplier_site_id number not null, 
  delivery_date date not null,
  supplier_id number not null
);

You can then add unique constraints on:

supplier_agreements (supplier_id, agreement_no )
supplier_sites (supplier_id, site_id )

And foreign keys from these columns in the deliveries table to the unique key in the corresponding parent.

Which gives a schema along the lines of:

create table suppliers ( 
  supplier_id number not null 
    constraint suppliers_pk primary key, 
  name varchar2(45) not null 
)
/

create table supplier_sites ( 
  site_id number not null 
    constraint supplier_sites_pk primary key, 
  name varchar2(45) not null, 
  supplier_id number not null 
    constraint supplier_sites_fk1 
    references suppliers (supplier_id),
  constraint supplier_sites_u unique (
    supplier_id, site_id
  )
)
/

create table supplier_agreements ( 
  agreement_no varchar2(30) not null 
    constraint supplier_agreements_pk primary key, 
  supplier_id number not null,
  constraint supplier_agreements_u unique (
    supplier_id, agreement_no
  )
)
/

create table supplier_deliveries ( 
  delivery_id number not null 
    constraint supplier_deliveries_pk primary key, 
  agreement_no varchar2(30) not null
    constraint supplier_deliveries_fk1 
    references supplier_agreements (agreement_no), 
  supplier_site_id number not null
    constraint supplier_deliveries_fk2 
    references supplier_sites (site_id) , 
  delivery_date date not null,
  supplier_id number not null,
  constraint supplier_deliveries_fk3
    foreign key (supplier_id, supplier_site_id)
    references supplier_sites (supplier_id, site_id),
  constraint supplier_deliveries_fk4
    foreign key (supplier_id, agreement_no)
    references supplier_agreements (supplier_id, agreement_no)
)
/

insert into suppliers(supplier_id, name) 
  values (1, 'SUPPLIER 1') 
/

insert into suppliers(supplier_id, name) 
  values (2, 'SUPPLIER 2') 
/

insert into supplier_sites(site_id, name, supplier_id) 
  values (1, 'SUPPLIER 1 SITE A', 1) 
/

insert into supplier_sites(site_id, name, supplier_id) 
  values (2, 'SUPPLIER 2 SITE B', 2) 
/

insert into supplier_agreements(agreement_no, supplier_id) 
  values ('111', 1) 
/

insert into supplier_agreements(agreement_no, supplier_id) 
  values ('222', 2) 
/

insert into supplier_deliveries (delivery_id, agreement_no, supplier_site_id, delivery_date, supplier_id) 
  values(1, '111', 2, trunc(sysdate), 1) 
/

ORA-02291: integrity constraint (CHRIS.SUPPLIER_DELIVERIES_FK3) violated - parent key not found

This is my preferred option based on my assumptions.

Make one table a child of the other

This means you have to link an agreement to a site (or vice-versa). So an agreement covers which sites that supplier delivers to. For example:

Supplier 1, agreement X covers sites 1, 2, 3
Supplier 1, agreement Y covers sites 3, 4, 5

As that's not in the current data model, I'm assuming this doesn't fit how the business works. Of course, if there is a relationship between agreements and sites, this is the correct way to solve this!