Check constraint violated

From: Me <somebudy_at_despammed.com>
Date: Fri, 13 Jun 2003 21:33:43 +0530
Message-ID: <64tjevsk29678h9vtfqp5posfe47n5odi5@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:03:43 CDT