EMPLOYEE database has data that violates CHECK constraint in the table SALES

[pavel-zotov]

Employee DB has table 'sales' with following constraint:

ALTER TABLE SALES ADD 
        CHECK (NOT (order_status = 'shipped' AND
            EXISTS (SELECT on_hold FROM customer
                    WHERE customer.cust_no = sales.cust_no
                    AND customer.on_hold = '*')));

AFAIU, during build process (make employee ?) this constraint is applied after all data has been added in that table.
Following query:

select * from sales
where
(
   order_status = 'shipped' and
   exists (
       select on_hold from customer
       where customer.cust_no = sales.cust_no
       and customer.on_hold = '*'
   )
);

-- shows that there is three records that violates above mentioned constraint:

INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE, AGED) VALUES ('v9333005', 1002, 11, 'shipped', '3-FEB-1993 00:00:00', '3-MAR-1993 00:00:00', NULL, 'y', 2, 600.5, 0, 'software', NULL);
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE, AGED) VALUES ('v9333006', 1002, 11, 'shipped', '27-APR-1993 00:00:00', '2-MAY-1993 00:00:00', '2-MAY-1993 00:00:00', 'n', 5, 20000, 0, 'other', NULL);
INSERT INTO SALES (PO_NUMBER, CUST_NO, SALES_REP, ORDER_STATUS, ORDER_DATE, SHIP_DATE, DATE_NEEDED, PAID, QTY_ORDERED, TOTAL_VALUE, DISCOUNT, ITEM_TYPE, AGED) VALUES ('v93f0020', 1009, 61, 'shipped', '10-OCT-1993 00:00:00', '11-NOV-1993 00:00:00', '11-NOV-1993 00:00:00', 'n', 1, 490.69, 0, 'software', NULL);

Adding CHECK constraint does not verify existing records (at least currently).
It will be good if script that adds data in this table will be adjusted.