1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Applying a constraint

Discussion in 'SQL PL/SQL' started by Tariq Bashir Malhi, Oct 6, 2010.

  1. Tariq Bashir Malhi

    Tariq Bashir Malhi Forum Advisor

    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    80
    In the following table we store insured person's(IP) employer's detail, if he worked with one employer he has one record and if insured person worked with many employer, then he has as many records. Among these records his record with last employer is active and previous ones are inactive, to distinguish active/inactive , if STATUS='A' it is active & if STATUS='I' it is inactive. His last employer is that where ENTRY_DATE is MAXIMUM.
    I want to apply such a constraint, that only last records of the insured person having MAX(ENTRY_DATE) can have STATUS='A'. At the present we can not stop users from inserting more then one active record.

    Code (SQL):

    DESC CORE_BUSINESS.cb_insured_person_detail
     
    Name                           NULL     TYPE                                                                                                                                                                                          
    ------------------------------ -------- -------------------------------
    IP_REGION_FO_CODE              NOT NULL NUMBER(4)                                                                                                                                                                                    
    IP_CHECK_DIGIT                 NOT NULL VARCHAR2(1)                                                                                                                                                                                  
    IP_SERIAL_NO                   NOT NULL NUMBER(6)                                                                                                                                                                                    
    EMP_AREA_CODE                  NOT NULL VARCHAR2(3)                                                                                                                                                                                  
    EMP_REG_SERIAL_NO              NOT NULL NUMBER(5)                                                                                                                                                                                    
    EMP_SUB_AREA_CODE              NOT NULL VARCHAR2(2)                                                                                                                                                                                  
    EMP_SUB_SERIAL_NO              NOT NULL NUMBER(3)                                                                                                                                                                                    
    SERIAL_NO                               NUMBER                                                                                                                                                                                        
    ENTRY_DATE                     NOT NULL DATE                                                                                                                                                                                          
    EXIT_DATE                               DATE                                                                                                                                                                                          
    EXIT_INFO_DATE                          DATE                                                                                                                                                                                          
    CURRENT_WAGES                           NUMBER(6)                                                                                                                                                                                    
    IBCO_NO                                 NUMBER(8)                                                                                                                                                                                    
    PMT_SLIP_NO                             NUMBER(10)                                                                                                                                                                                    
    SCHEME_CODE                             NUMBER(2)                                                                                                                                                                                    
    REMARKS                                 VARCHAR2(255)                                                                                                                                                                                
    NATURE_OF_OCCUPATION                    VARCHAR2(1)                                                                                                                                                                                  
    STATUS                         NOT NULL VARCHAR2(1)                                                                                                                                                                                  
    CREATED_BY                     NOT NULL VARCHAR2(10)                                                                                                                                                                                  
    CREATED_DATE                   NOT NULL DATE                                                                                                                                                                                          
    MODIFIED_BY                    NOT NULL VARCHAR2(10)                                                                                                                                                                                  
    MODIFIED_DATE                  NOT NULL DATE                                                                                                                                                                                          
    EOBI_NO                                 VARCHAR2(11)                                                                                                                                                                                  
    OLD_EOBI_NO                             VARCHAR2(11)                                                                                                                                                                                  
    ORPHANAGE_DATE                          DATE                                                                                                                                                                                          
    PKEY                                    NUMBER(20)
     
    I have not yet been able to do this......... Your help is required.:hurray
     
  2. debasisdas

    debasisdas Active Member

    Messages:
    46
    Likes Received:
    3
    Trophy Points:
    90
    Location:
    Bangalore, India
    What about doing all the transaction through a Procedure instead of raw SQL.
     
  3. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Try with rowid . I'll give one example

    select * from emp where rowid in (select max(rowid) from emp group by hiredate);

    here i've written select query,
    You try the same with Update and decode.

    It it works , fine.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Or you can use a materialized view to enforce the constraint:

    Code (SQL):
     
    SQL> CREATE materialized VIEW log ON product_to_type WITH rowid, (product_id) including NEW VALUES;
    Materialized VIEW log created.
    SQL>
    SQL> CREATE materialized VIEW prod_type_dept_ck
      2  refresh fast ON commit
      3  AS
      4  SELECT product_id, COUNT(*) ct FROM product_to_type
      5  GROUP BY product_id
      6  /
    Materialized VIEW created.
    SQL>
    SQL> ALTER TABLE prod_type_dept_ck
      2  ADD CONSTRAINT check_ct
      3  CHECK(ct < 3)
      4  /
    TABLE altered.
    SQL>
    SQL> SELECT *
      2  FROM product_to_type;
    PROD_TYPE_CD PRODUCT_ID DEPT_TYPE_CD
    ------------ ---------- ------------
             101        100            1
             102        100            2
             201        200            3
             202        200            4
    SQL>
    SQL> INSERT
      2  INTO product_to_type
      3  VALUES(203, 200, 2);
    1 ROW created.
    SQL>
    SQL> commit;
    commit
    *
    ERROR at line 1:
    ORA-12008: error IN materialized VIEW refresh path
    ORA-02290: CHECK CONSTRAINT (BING.CHECK_CT) violated

    SQL>
    SQL> SELECT *
      2  FROM product_to_type;
    PROD_TYPE_CD PRODUCT_ID DEPT_TYPE_CD
    ------------ ---------- ------------
             101        100            1
             102        100            2
             201        200            3
             202        200            4
    SQL>
     
    Of course you'll need some better criteria than max(entry_date) as selecting that will always produce one record; possibly using ip_serial_no, status and count(*) where status = 'A' in the materialized view then set up a check constraint on the materialized view where ct < 2.