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!

Help with table

Discussion in 'SQL PL/SQL' started by laxman, Jan 28, 2010.

  1. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Here is table structure details.
    SQL> select dbms_metadata.get_ddl('TABLE','MINITEAMMEMBERS','LAX') from dual;

    DBMS_METADATA.GET_DDL('TABLE','MINITEAMMEMBERS','LAX')
    --------------------------------------------------------------------------------

    CREATE TABLE "LAX"."MINITEAMMEMBERS"
    ( "MINITEAMID" NUMBER(10,0) NOT NULL ENABLE,
    "PERSONID" NUMBER(20,0) NOT NULL ENABLE,
    "ISVALID" NUMBER(3,0) NOT NULL ENABLE,
    "STARTDATE" DATE NOT NULL ENABLE,
    "ENDDATE" DATE,
    "LASTMODDATE" DATE DEFAULT sysdate,
    CONSTRAINT "MINITEAMMEMBERS_PK" UNIQUE ("MINITEAMID", "PERSONID")
    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
    STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

    DBMS_METADATA.GET_DDL('TABLE','MINITEAMMEMBERS','LAX')
    --------------------------------------------------------------------------------
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    TABLESPACE "INDEX_TS" ENABLE
    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
    STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
    TABLESPACE "DATA_TS"
    Also i have checked the status of both the constraint,it showing the status based on operation done.
    Regards
    Laxman
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: kind attn Zargon sir

    You've created a UNIQUE constraint on two columns in this table and have labeled it as a primary key constraint. UNIQUE constraints are enforced by a UNIQUE index, where true primary key constraints can be enforced with non-unique indexes. This, I believe, is your problem, disabling the UNIQUE constraint does not drop the UNIQUE index and thus the constraint remains in force. Had you done this:

    Code (SQL):
    ALTER TABLE MINITEAMMEMBERS
    ADD CONSTRAINT MINITEAMMEMBERS_PK
    PRIMARY KEY(miniteamid, personid);
    then disabled the primary key constraint you'd not have constraint violations with the code you've posted. If you are naming constraints as PK you should really declare them as primary keys, not as unique constraints coupled with NOT NULL constraints.
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Re: kind attn Zargon sir

    Disabling the UNIQUE constraint does not drop the UNIQUE index and constraint remains in force but with DISABLED status. ie you can insert duplicate values as long as it is in DISABLED status. no need to drop the constraint.

    Code (SQL):


     SELECT * FROM dual;

    SQL>  DROP TABLE taba purge;

    TABLE dropped.

    SQL> CREATE TABLE taba ( x NUMBER(10) , y NUMBER(10) );

    TABLE created.

    SQL> ALTER TABLE taba ADD CONSTRAINT taba_UX UNIQUE ( x,y);

    TABLE altered.

    SQL> ALTER TABLE taba MODIFY x NOT NULL;

    TABLE altered.

    SQL> INSERT INTO  taba
      2  SELECT 1,1 FROM dual UNION ALL
      3  SELECT 1,2 FROM dual UNION ALL
      4  SELECT 1,3 FROM dual UNION ALL
      5  SELECT 1,NULL FROM dual UNION ALL
      6  SELECT 2,1 FROM dual UNION ALL
      7  SELECT 2,2 FROM dual UNION ALL
      8  SELECT 2,3 FROM dual UNION ALL
      9  SELECT 2,NULL FROM dual;

    8 ROWS created.

    SQL> INSERT INTO  taba
    SELECT * FROM taba;
      2  INSERT INTO  taba
    *
    ERROR at line 1:
    ORA-00001: UNIQUE CONSTRAINT (PPMS_SUDAN.TABA_UX) violated


    SQL> SELECT * FROM taba ORDER BY 1,2;

             X          Y
    ---------- ----------
             1          1
             1          2
             1          3
             1
             2          1
             2          2
             2          3
             2

    8 ROWS selected.

    SQL> SELECT  constraint_name     , constraint_type       ,STATUS
    FROM user_constraints WHERE TABLE_NAME= 'TABA'
      2    3  ;

    CONSTRAINT_NAME                C STATUS
    ------------------------------ - --------
    TABA_UX                        U ENABLED
    SYS_C0016250                   C ENABLED

    SQL>
    SQL> ALTER TABLE taba disable CONSTRAINT SYS_C0016250;

    TABLE altered.

    SQL> ALTER TABLE taba disable CONSTRAINT taba_UX;

    TABLE altered.

    SQL> SELECT  constraint_name     , constraint_type       ,STATUS
    FROM user_constraints WHERE TABLE_NAME= 'TABA'
      2    3  ;

    CONSTRAINT_NAME                C STATUS
    ------------------------------ - --------
    TABA_UX                        U DISABLED
    SYS_C0016250                   C DISABLED

    SQL> SELECT * FROM taba ORDER BY 1,2;

             X          Y
    ---------- ----------
             1          1
             1          2
             1          3
             1
             2          1
             2          2
             2          3
             2

    8 ROWS selected.

    SQL> INSERT INTO  taba
    SELECT * FROM taba;
      2
    8 ROWS created.

    SQL> SELECT * FROM taba ORDER BY 1,2;

             X          Y
    ---------- ----------
             1          1
             1          1
             1          2
             1          2
             1          3
             1          3
             1
             1
             2          1
             2          1
             2          2

             X          Y
    ---------- ----------
             2          2
             2          3
             2          3
             2
             2

    16 ROWS selected.

    SQL> UPDATE taba
    SET x= NULL;
      2
    16 ROWS updated.

    SQL> SELECT * FROM taba ORDER BY 1,2;

             X          Y
    ---------- ----------
                        1
                        1
                        1
                        1
                        2
                        2
                        2
                        2
                        3
                        3
                        3

             X          Y
    ---------- ----------
                        3





    16 ROWS selected.

    SQL> ALTER TABLE taba enable CONSTRAINT taba_UX;
    ALTER TABLE taba enable CONSTRAINT taba_UX
    *
    ERROR at line 1:
    ORA-02299: cannot validate (PPMS_SUDAN.TABA_UX) - duplicate KEYS found


    SQL> ALTER TABLE taba enable CONSTRAINT SYS_C0016250;
    ALTER TABLE taba enable CONSTRAINT SYS_C0016250
                                       *
    ERROR at line 1:
    ORA-02293: cannot validate (PPMS_SUDAN.SYS_C0016250) - CHECK CONSTRAINT
    violated

    SQL>

     
     
  4. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Re: kind attn Zargon sir

    Now i got the root cause.let me try sir and get back to you with the updated status.
    thanks for your valuable information.

    Regards
    Laxman
     
  5. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Please don't use Specific member names in Thread titles.