Re: Check constraint violated

Have you looked in all_constraints to find out what is wrong?

It is a GOOD practice to specify the fields list on inserts.

"Me" <somebudy_at_despammed.com> wrote in message news:64tjevsk29678h9vtfqp5posfe47n5odi5_at_4ax.com...
> -- The main table contains no foreign keys
> CREATE TABLE LICAGENT
> (
> NAME VARCHAR2(20) NOT NULL,
> AGE NUMBER (2) NOT NULL ,
> SEX CHAR (1) NOT NULL,
> ADDRESS VARCHAR2(50) NOT NULL,
> LICENCE_NO NUMBER(10) NOT NULL UNIQUE,
> AGENT_CODE NUMBER(10) NOT NULL,
> PRIMARY KEY (AGENT_CODE),
> CHECK (AGE > 17 AND AGE<65),
> CHECK (LICENCE_NO >0 AND AGENT_CODE>0),
> CHECK (SEX IN ('M','F'))
> );
>
> CREATE TABLE POLICY
> (
> POLICY_NAME VARCHAR2(20) NOT NULL,
> POLICY_NO VARCHAR2(10) NOT NULL UNIQUE,
> FORM_NO VARCHAR2(10) NOT NULL UNIQUE,
> CREATION_DATE DATE NOT NULL,
> PRIMARY KEY (POLICY_NO)
> );
>
> -- Proposers data
> -- Prop_no stands for proposal number
> CREATE TABLE PROPOSER
> (
> PROP_NO VARCHAR2(10) NOT NULL,
> NAME VARCHAR2(20) NOT NULL,
> SHORT_NAME VARCHAR2(10),
> AGE NUMBER(2) NOT NULL,
> SEX CHAR(1) NOT NULL,
> ADDRESS VARCHAR2(50) NOT NULL,
> NATIONALITY VARCHAR(20) NOT NULL,
> RES_IN_INDIA CHAR (3) NOT NULL,
> FATHER_NAME VARCHAR (20) NOT NULL,
> UAGENT NUMBER(10) REFERENCES LICAGENT(AGENT_CODE) ON DELETE CASCADE,
> POLICY_NO VARCHAR2 (10) REFERENCES POLICY (POLICY_NO) ON DELETE CASCADE,
> PRIMARY KEY (PROP_NO),
> CHECK (AGE >0 AND AGE<65),
> CHECK (SEX IN ('M','F') ),
> CHECK (RES_IN_INDIA IN ('yes','no'))
> );
>
> -- Nominee's data
>
> CREATE TABLE NOMINEE
> (
> NAME VARCHAR2(20) NOT NULL,
> AGE NUMBER(2) NOT NULL,
> SEX CHAR(1) NOT NULL,
> RELATION CHAR(15) NOT NULL,
> PRESENT_OCCUPATION CHAR(20) NOT NULL,
> UAGENT NUMBER(10) REFERENCES LICAGENT(AGENT_CODE) ON DELETE CASCADE ,
> POLICY_NO VARCHAR2(10) REFERENCES POLICY (POLICY_NO) ON DELETE CASCADE ,
> PROP_NO VARCHAR2(10) REFERENCES PROPOSER (PROP_NO) ON DELETE CASCADE ,
> CHECK (AGE>0 AND AGE<65),
> CHECK (SEX IN ('M','F'))
> );
>
> -- Nominee has apointee if age of nominee is less than 18
>
> CREATE TABLE APOINTEE
> (
> NAME VARCHAR2(20) NOT NULL,
> AGE NUMBER(2) NOT NULL,
> SEX CHAR(1) NOT NULL,
> RELATION CHAR(15) NOT NULL,
> PRESENT_OCCUPATION CHAR(20) NOT NULL,
> UAGENT NUMBER(10) REFERENCES LICAGENT(AGENT_CODE) ON DELETE CASCADE ,
> POLICY_NO VARCHAR2(10) REFERENCES POLICY (POLICY_NO) ON DELETE CASCADE,
> PROP_NO VARCHAR2(10) REFERENCES PROPOSER (PROP_NO) ON DELETE CASCADE,
> CHECK (AGE>18 AND AGE<65),
> CHECK (SEX IN ('M','F'))
> );
>
> -- Propors Policy data
> CREATE TABLE POLICY_DATA
> (
> AGE_PROOF VARCHAR2(20) NOT NULL,
> SUM_ASSURED NUMBER(10) NOT NULL,
> AMOUNT_DEPOSITED NUMBER(10) NOT NULL,
> TABLE_NO NUMBER (4) NOT NULL,
> TERM NUMBER (2) NOT NULL,
> BOC_NO NUMBER(10),
> BOC_DATE DATE,
> PROP_DATE DATE NOT NULL,
> PAN VARCHAR2(16) NOT NULL UNIQUE,
> UAGENT NUMBER (10) REFERENCES LICAGENT(AGENT_CODE) ON DELETE CASCADE,
> POLICY_NO VARCHAR2(10) REFERENCES POLICY (POLICY_NO) ON DELETE CASCADE,
> PROP_NO VARCHAR2(10) REFERENCES PROPOSER (PROP_NO) ON DELETE CASCADE,
> CHECK (AMOUNT_DEPOSITED >0 AND AMOUNT_DEPOSITED<1000000),
> CHECK (SUM_ASSURED >0 AND SUM_ASSURED<1000000),
> CHECK (TERM IN (5,10)),
> CHECK (TABLE_NO >0)
> );
>
> -- Proposers health data
>
> CREATE TABLE PROP_HEALTH
> (
> HEIGHT NUMBER (3) NOT NULL,
> WEIGHT NUMBER(3) NOT NULL,
> PRES_HEALTH CHAR(4) NOT NULL,
> ADMIT_HOSP CHAR(3) NOT NULL,
> PHY_DEFORMITY CHAR (3) NOT NULL,
> UAGENT NUMBER(10) REFERENCES LICAGENT(AGENT_CODE) ON DELETE CASCADE ,
> POLICY_NO VARCHAR2(10) REFERENCES POLICY (POLICY_NO) ON DELETE CASCADE
,
> PROP_NO VARCHAR2(10) REFERENCES PROPOSER (PROP_NO) ON DELETE CASCADE ,
> CHECK (WEIGHT>0 AND HEIGHT>0),
> CHECK (PRES_HEALTH IN ('good','bad')),
> CHECK (ADMIT_HOSP IN ('yes','no'))
> );
>
> -- Additional data for female proposors
>
> CREATE TABLE FEMALE_PROP_DATA
> (
> MARRIED CHAR (3) NOT NULL,
> PREG_NOW CHAR (3) NOT NULL,
> MISCARRIAGE CHAR(3) NOT NULL,
> UAGENT NUMBER(10) REFERENCES LICAGENT(AGENT_CODE) ON DELETE CASCADE,
> POLICY_NO VARCHAR2(10) REFERENCES POLICY (POLICY_NO) ON DELETE
CASCADE,
> PROP_NO VARCHAR2(10) REFERENCES PROPOSER (PROP_NO) ON DELETE CASCADE,
> CHECK (MARRIED IN ('yes','no')),
> CHECK (PREG_NOW IN ('yes','no')),
> CHECK (MISCARRIAGE IN ('yes','no'))
> );
> _________________________________________
> insert into licagent values ('Harish Patel',0,'a','Calcutta',0,0);
>
> If you do this you get:
> ORA-02290: check constraint (SCOTT.SYS_C005196) violated
>
> 1) Is there a way of getting an error message as to which check constraint
was
> violated?
>
> 2) How can I put a check constraint that allows entering data into
apointee only
> if the nominee's age is lesser than 18?
>
> In a similar way entering data into FEMALE_PROP_DATA only if the proposer
is a
> female etc. etc. (one example will do)
>
> 3) http://www.licindia.com/lichome2/300(bn).doc
> If you have the time could you tell me:
> Is there something wrong with the relations? (This is for a college
> project) any suggestions are welcome.
>
>
> Thanks a lot.
> --
> Just trying my fate here :)
> Send mail to:
> somebudy_at_despammed.com
> In OE press Crl-F3 (view source) to see post properly
Received on Fri Jun 13 2003 - 11:16:37 CDT