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!

ORA-00001: unique constraint violated issue reg

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

  1. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    hi all,
    though i have raised this issue a few days back,i would like to discuss once again,since i am unable to solve this issue,i have table tab a

    sql> desc taba;
    pid not null number(10);

    here two constraints are associated with pid:
    SYS_C001496(check constraint) and
    taba_pk(primary constraint)

    i have some requirement that i need to test this column by setting this as null,i am following this steps as follows:

    sql> alter table taba add (PID_bk number(10));
    sql>update taba set PID_bk = PID;
    commit;
    sql>alter table taba disable constraint SYS_C001496;
    sql>alter table taba disable constraint taba_PK;
    sql>update taba set PERSONID=null;
    //after executing this above statement i am facing this error
    ORA-00001: unique constraint violated.

    and i supposed to execute the remaining statement after the above statement.
    sql>alter table taba modify (PID number(10));
    sql>update taba set PERSONID = PID_bk;
    commit;
    sql>alter table tabaenable constraint SYS_C001496;
    sql>alter table taba enable constraint taba_PK;
    sql>alter table taba drop (PID_bk);

    kindly do the needful.

    thanks n regards
    Laxman
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Not getting any errors ... .

    Code (SQL):


    SQL> CREATE TABLE TABA (PID NUMBER(20) NOT NULL);

    TABLE created.

    SQL> ALTER TABLE TABA ADD CONSTRAINT TABA_PK PRIMARY KEY (PID);

    TABLE altered.

    SQL> SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME='TABA';

    CONSTRAINT_NAME
    ------------------------------
    SYS_C00306873
    TABA_PK

    SQL> INSERT INTO TABA VALUES(10000);

    1 ROW created.

    SQL> COMMIT;

    Commit complete.

    SQL> ALTER TABLE TABA ADD (PID_BK NUMBER(10));

    TABLE altered.

    SQL> UPDATE TABA SET PID_BK = PID;

    1 ROW updated.

    SQL> COMMIT;

    Commit complete.

    SQL> ALTER TABLE TABA DISABLE CONSTRAINT SYS_C00306873;

    TABLE altered.

    SQL> ALTER TABLE TABA DISABLE CONSTRAINT TABA_PK;

    TABLE altered.

    SQL> UPDATE TABA SET PID=NULL;

    1 ROW updated.

    SQL> ALTER TABLE TABA MODIFY (PID NUMBER(10));

    TABLE altered.

    SQL> UPDATE TABA SET PID = PID_BK;

    1 ROW updated.

    SQL> COMMIT;

    Commit complete.

    SQL> ALTER TABLE TABA ENABLE CONSTRAINT SYS_C00306873;

    TABLE altered.

    SQL> ALTER TABLE TABA ENABLE CONSTRAINT TABA_PK;

    TABLE altered.

    SQL> ALTER TABLE TABA DROP (PID_BK);

    TABLE altered.

    SQL>

     
     
  3. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    dear sir,
    Actually in order to make you clear my doubts,i have given you a dummy table scenario.but i am facing this problem in real time scenario with another table having huge records approx 3645678 .
    sir i need your help please ,if u need any more information related to that table i will provide you but please help me to sort out this problem.i am definitely facing this problem.

    Regards
    Laxman
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    We can't solve your 'problem' with a dummy table. Without knowing the configuration of this problem table (including constraints and child table data) no one can replicate your problem nor can anyone offer any usable suggestions. Prvide the table definitions and some realistic sample data if you want real help in this situation of yours.
     
  5. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    here is the details sir.
    SQL> DESC MINITEAMMEMBERS;
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    MINITEAMID NOT NULL NUMBER(10)
    PERSONID NOT NULL NUMBER(20)
    ISVALID NOT NULL NUMBER(3)
    STARTDATE NOT NULL DATE
    ENDDATE DATE
    LASTMODDATE DATE

    SQL> select count(*) from MINITEAMMEMBERS;

    COUNT(*)
    ----------
    167727


    SQL> select constraint_name from all_cons_columns where column_name='PERSONID' and table_name='MINITEAMMEMBERS';

    CONSTRAINT_NAME
    ------------------------------
    SYS_C001496
    MINITEAMMEMBERS_PK

    SQL> select personid from MINITEAMMEMBERS where rownum<=10;

    PERSONID
    ----------
    1
    3
    15
    108
    109
    118
    133
    138
    143
    144

    10 rows selected.


    the script is:
    alter table MINITEAMMEMBERS add (PERSONID_bk number(10));
    update MINITEAMMEMBERS set PERSONID_bk = PERSONID;
    commit;
    alter table MINITEAMMEMBERS disable constraint SYS_C001496;
    alter table MINITEAMMEMBERS disable constraint MINITEAMMEMBERS_PK;
    update MINITEAMMEMBERS set PERSONID=null; //after executing this statement i am facing unique constraint problem.
    commit;
    alter table MINITEAMMEMBERS modify (PERSONID number(10));
    update MINITEAMMEMBERS set PERSONID = PERSONID_bk;
    commit;
    alter table MINITEAMMEMBERS enable constraint SYS_C001496;
    alter table MINITEAMMEMBERS enable constraint MINITEAMMEMBERS_PK;
    alter table MINITEAMMEMBERS drop (PERSONID_bk);

    SQL> @/Users/laxman/ArchiveofScripts/MINITEAMMEMBERS.sql

    Table altered.

    Elapsed: 00:00:02.08

    167727 rows updated.

    Elapsed: 00:00:21.40

    Commit complete.

    Elapsed: 00:00:00.50

    Table altered.

    Elapsed: 00:00:00.86

    Table altered.

    Elapsed: 00:00:00.58
    update MINITEAMMEMBERS set PERSONID=null
    *
    ERROR at line 1:
    ORA-00001: unique constraint (SONAR.MINITEAMMEMBERS_PK) violated


    Elapsed: 00:00:01.91

    Commit complete.

    Elapsed: 00:00:00.50
    alter table MINITEAMMEMBERS modify (PERSONID number(10))
    *
    ERROR at line 1:
    ORA-01440: column to be modified must be empty to decrease precision or scale


    Elapsed: 00:00:01.30

    167727 rows updated.

    Elapsed: 00:00:16.93

    Commit complete.

    Elapsed: 00:00:00.50

    Table altered.

    Elapsed: 00:00:00.61

    Table altered.

    Elapsed: 00:00:06.43

    Table altered.

    Elapsed: 00:00:10.06
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Please check the status of the MINITEAMMEMBERS_PK constraint; I expect it hasn't been disabled (even though Oracle states that it has). There is probably a unique index remaining that can't be dropped which keeps the constraint in force. Which release of Oracle are you using?
     
  7. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    kindly verify it sir,shall i drop this constraint before i do this job? and later on i will add those constraint!!!

    ORACLE RELEASE:Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit

    HERE is the constraints status:
    SQL> select constraint_name,constraint_type from user_constraints where table_name='MINITEAMMEMBERS' AND constraint_name IN('SYS_C001496','MINITEAMMEMBERS_PK');

    CONSTRAINT_NAME C
    ------------------------------ -
    SYS_C001496 C
    MINITEAMMEMBERS_PK U

    Regards
    Laxman
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes, it may be best to simply drop the constraint, alter the table then create the constraint again. Be cerrtain you have a script prepared to create this constraint as it currently exists; you should be able to extract this information from the database using dbms_metadata.get_ddl for the table being modified.
     
  9. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Execute the following queries in the SQLPLUS session directly and show us the output. (Not executing the sql file )

    Code (SQL):


    ALTER TABLE MINITEAMMEMBERS ADD (PERSONID_BK NUMBER(10));
    UPDATE MINITEAMMEMBERS SET PERSONID_BK = PERSONID;
    COMMIT;

    SELECT UCC.CONSTRAINT_NAME ,
           UCC.TABLE_NAME ,
           UCC.COLUMN_NAME ,
           UC.STATUS
    FROM USER_CONS_COLUMNS UCC ,
         USER_CONSTRAINTS UC
    WHERE UCC.CONSTRAINT_NAME =  UC.CONSTRAINT_NAME
    AND UCC.COLUMN_NAME='PERSONID'
    AND UCC.TABLE_NAME='MINITEAMMEMBERS';

    ALTER TABLE MINITEAMMEMBERS DISABLE CONSTRAINT SYS_C001496;
    ALTER TABLE MINITEAMMEMBERS DISABLE CONSTRAINT MINITEAMMEMBERS_PK;

    SELECT UCC.CONSTRAINT_NAME ,
           UCC.TABLE_NAME ,
           UCC.COLUMN_NAME ,
           UC.STATUS
    FROM USER_CONS_COLUMNS UCC ,
         USER_CONSTRAINTS UC
    WHERE UCC.CONSTRAINT_NAME =  UC.CONSTRAINT_NAME
    AND UCC.COLUMN_NAME='PERSONID'
    AND UCC.TABLE_NAME='MINITEAMMEMBERS';

    UPDATE MINITEAMMEMBERS SET PERSONID=NULL;
    COMMIT;

    ALTER TABLE MINITEAMMEMBERS MODIFY (PERSONID NUMBER(10));
    UPDATE MINITEAMMEMBERS SET PERSONID = PERSONID_BK;
    COMMIT;
    ALTER TABLE MINITEAMMEMBERS ENABLE CONSTRAINT SYS_C001496;
    ALTER TABLE MINITEAMMEMBERS ENABLE CONSTRAINT MINITEAMMEMBERS_PK;
    ALTER TABLE MINITEAMMEMBERS DROP (PERSONID_BK);

     
     
  10. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Another Way to do the same task (Decreasing the datatype size) is to ,

    1. Create a backup table of MINITEAMMEMBERS as MINITEAMMEMBERS_BKP.

    Code (SQL):

     CREATE TABLE MINITEAMMEMBERS_bkp AS SELECT * FROM MINITEAMMEMBERS;
     
    2. Truncate the table MINITEAMMEMBERS.

    Code (SQL):

     TRUNCATE TABLE MINITEAMMEMBERS;
     
    3. Decrease the datatype length of MINITEAMMEMBERS.PERSONID

    Code (SQL):

     ALTER TABLE Miniteammembers MODIFY (Personid NUMBER(10));
     
    4. Move the data from backup table to MINITEAMMEMBERS.

    Code (SQL):

     INSERT INTO MINITEAMMEMBERS SELECT * FROM MINITEAMMEMBERS_BKP;
     
    5. Drop the backup table.

    Code (SQL):

      DROP TABLE MINITEAMMEMBERS_BKP Purge;
     
    This is with assumption that there is no referencing constraint in the schema referring this table. In that case refrencial constrains need to be disabled.