Following is the layout of my table, Status Column can have 1,2,3....6 . My requirement is that CNIC column can not be duplicate if status = 1, this can be duplicate for other status types.
This table has about 0.5 Million Records.
CREATE TABLE TARIQ_CORE.pensioner
(
claim_no VARCHAR2(25),
cnic VARCHAR2(13),
status NUMBER(1),
name VARCHAR2(35),
pension_amount NUMBER(5)
);
ALTER TABLE TARIQ_CORE.pensioner ADD (
CONSTRAINT pensioner_PK
PRIMARY KEY
(claim_no));
How can i manage this.