CODEWITHSUNDEEP
sqloracle-databasecheck-constraints
Suman
Suman

Reputation: 5

Constraint Violated in SQL

I created the table as:

CREATE TABLE SALES_ORDER2_56 (
    ORDERNO VARCHAR2 (6) CONSTRAINT SOpkey1 PRIMARY KEY CONSTRAINT SOcheck1 CHECK (ORDERNO LIKE 'O%'),
    CLIENTNO VARCHAR2 (6) CONSTRAINT SOfkey1 REFERENCES CLIENT_MASTER2_56 (CLIENTNO),
    ORDERDATE DATE CONSTRAINT SOnull1 NOT NULL,
    DELYADDR VARCHAR2 (25),
    SALESMANNO VARCHAR2 (6) CONSTRAINT SOfkey2 REFERENCES SALESMAN_MASTER2_56 (SALESMANNO),
    DELYTYPE CHAR (1) DEFAULT 'F',
    CONSTRAINT SOcheck2 CHECK (
        DELYTYPE LIKE 'F'
        OR DELYTYPE LIKE 'P'
    ),
    BILLYN CHAR (1),
    DELYDATE DATE,
    ORDERSTATUS VARCHAR2 (10),
    CHECK (
        ORDERSTATUS IN (
            'In Process',
            'Fulfilled',
            'BackOrder',
            'Cancelled'
        )
    ),
    CONSTRAINT SOcheck3 CHECK (DELYDATE > ORDERDATE)
);

But whenever I try to run this :

INSERT INTO SALES_ORDER2_56 (ORDERNO,CLIENTNO,ORDERDATE,SALESMANNO,DELYTYPE,BILLYN,DELYDATE,ORDERSTATUS)
    VALUES('O19001','C00001','12-jun-04','S00001','F','N','20-jul-04','In Process');

It gives me this: [Err] ORA-02291: integrity constraint (SCOTT.SOFKEY2) violated - parent key not found

I tried many times but in vain. Help is highly appreciated.

Upvotes: 0

Views: 447

Answers (1)

jpw
jpw

Reputation: 44871

This answer was in response to the original version of the question, it has since been edited which makes the answer seem irrelevant.

The CHECKconstraint for ORDERSTATUSdemands that ORDERSTATUS is one of eitherIn Progress, Fulfilled, BackOrder or Cancelled, but in your insert query you use In Process which violates the constraint. I'm guessing you meant to use In Progress?

The error [Err] ORA-02291: integrity constraint (SCOTT.SOFKEY2) violated - parent key not found

indicates that the SALESMANNO S00001 doesn't exist in the SALESMAN_MASTER2_56 table

Upvotes: 2

Related Questions