This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Jan 19, 2013 3:56 AM by Solomon Yakobson RSS

unique constraint not working

831564 Newbie
Currently Being Moderated
Hi,
I originally had a unique constraint on a table on columns say C1,C2,C3. Later I added a 4th column C4. I can see that the 4th column is part of the same index in all_cons_columns.r
But, when I try to insert row with same value of C1,C2,C3 but different C4 it gives me the same Unique key error even though the value I am entering for C4 is unique for that table. What could cause this?
  • 1. Re: unique constraint not working
    Syed Ullah Journeyer
    Currently Being Moderated
    what command did you use to create the constraints?
  • 2. Re: unique constraint not working
    831564 Newbie
    Currently Being Moderated
    I used alter Table something like this to add the 4th constraint
    alter table table_name add (constraint TAB_PK PRIMARY KEY(ODB) USING INDEX TAB_PK, constraint TAB_UK UNIQUE(C1,C2,C3,C4) USING INDEX tab_UIX);
  • 3. Re: unique constraint not working
    TSharma-Oracle Guru
    Currently Being Moderated
    You have to check whether combination of values of C1,C2,C3 and C4 is unique or not. Not just C4.
  • 4. Re: unique constraint not working
    831564 Newbie
    Currently Being Moderated
    Yes, combination of C1,C2,C3 and C4 are unique, what I meant was only C4 differs (and unique constraint error is thrown). but as soon as I make changes for C1,C2 and C3, unique constraint error is not thrown
  • 5. Re: unique constraint not working
    sb92075 Guru
    Currently Being Moderated
    user13755008 wrote:
    Yes, combination of C1,C2,C3 and C4 are unique, what I meant was only C4 differs (and unique constraint error is thrown). but as soon as I make changes for C1,C2 and C3, unique constraint error is not thrown
    You can claim whatever you think is true, but it is 100% devoid of any actionable detail.

    We all speak SQL here so please consider to actually use COPY & PASTE so we can actually see what you do & how Oracle really responds.

    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 6. Re: unique constraint not working
    831564 Newbie
    Currently Being Moderated
    select * from all_cons_columns where constraint_name='HSRP_TRACK_UK';

    OWNER CONSTRAINT_NAME TABLE_NAME
    ------------------------------ ------------------------------ ------------------------------
    COLUMN_NAME
    --------------------------------------------------------------------------------------------------------------------------------
    POSITION
    ----------
    NC3 HSRP_TRACK_UK HSRP_TRACK
    ODBID_EQP
    1

    NC3 HSRP_TRACK_UK HSRP_TRACK
    START_DATE
    4

    NC3 HSRP_TRACK_UK HSRP_TRACK
    OBJECT_NUM
    2

    NC3 HSRP_TRACK_UK HSRP_TRACK




    select * from all_ind_columns where INDEX_NAME='HSRP_OBJ_INTER_ST_UIX';

    INDEX_OWNER INDEX_NAME TABLE_OWNER TABLE_NAME
    ------------------------------ ------------------------------ ------------------------------ ------------------------------
    COLUMN_NAME
    --------------------------------------------------------------------------------------------------------------------------------
    COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESC
    --------------- ------------- ----------- ----
    NC3 HSRP_OBJ_INTER_ST_UIX NC3 HSRP_TRACK
    ODBID_EQP
    1 22 0 ASC

    NC3 HSRP_OBJ_INTER_ST_UIX NC3 HSRP_TRACK
    OBJECT_NUM
    2 4 4 ASC

    NC3 HSRP_OBJ_INTER_ST_UIX NC3 HSRP_TRACK
    INTERFACE
    3 32 32 ASC

    NC3 HSRP_OBJ_INTER_ST_UIX NC3 HSRP_TRACK
    START_DATE
    4 7 0 ASC



    select * from nc3.hsrp_track where odbid= 87820678;

    ODBID CREATETMSTMP CREATEUS CREATETRAN
    ---------- --------------------------------------------------------------------------- -------- ----------------
    LASTUPDTMSTMP LASTUPDU LASTUPDTRAN ODBID_EQP
    --------------------------------------------------------------------------- -------- ---------------- ----------
    ODBID_START_ORDER ODBID_STOP_ORDER ODBID_PREV START_DATE STOP_DATE STATUS COMPONENT_TYPE OBJE
    ----------------- ---------------- ---------- ---------- ---------- ---------------- -------------------------------- ----
    INTERFACE PROCESS
    -------------------------------- ---------------
    87820678 2013-01-18-11:29:29.285050 NC3USER StoreRouting
    2013-01-18-11:29:29.285050 NC3USER StoreRouting 87811116
    87811106 2013-01-18 9999-12-31 Started Equipment 1
    Serial0/0 line-protocol




    SQL> insert into NC3.HSRP_TRACK Values(3,'2013-01-18-11:29:29.436033', 'NC3USER ', 'StoreRouting', '2013-01-18-11:29:29.436033', 'NC3USER ', 'StoreRouting ', 87820377,'','','', '2013-01-18','9999-12-31','Started', 'Equipment', '1', 'Serial0/0', '');
    insert into NC3.HSRP_TRACK Values(3,'2013-01-18-11:29:29.436033', 'NC3USER ', 'StoreRouting', '2013-01-18-11:29:29.436033', 'NC3USER ', 'StoreRouting ', 87820377,'','','', '2013-01-18','9999-12-31','Started', 'Equipment', '1', 'Serial0/0', '')
    *
    ERROR at line 1:
    ORA-00001: unique constraint (NC3.HSRP_TRACK_UK) violated



    SQL> desc nc3.hsrp_track
    Name Null? Type
    ---------------------------------------------------------------------- -------- -----------------------------------------------
    ODBID NOT NULL NUMBER(11)
    CREATETMSTMP NOT NULL TIMESTAMP(6)
    CREATEUSER NOT NULL CHAR(8)
    CREATETRAN NOT NULL CHAR(16)
    LASTUPDTMSTMP NOT NULL TIMESTAMP(6)
    LASTUPDUSER NOT NULL CHAR(8)
    LASTUPDTRAN NOT NULL CHAR(16)
    ODBID_EQP NOT NULL NUMBER(11)
    ODBID_START_ORDER NUMBER(11)
    ODBID_STOP_ORDER NUMBER(11)
    ODBID_PREV NUMBER(11)
    START_DATE NOT NULL DATE
    STOP_DATE NOT NULL DATE
    STATUS NOT NULL CHAR(16)
    COMPONENT_TYPE NOT NULL VARCHAR2(32)
    OBJECT_NUM NOT NULL VARCHAR2(4)
    INTERFACE NOT NULL VARCHAR2(32)
    PROCESS VARCHAR2(15)



    SQL> select * from nc3.hsrp_track where odbid_eqp=87820377;

    no rows selected




    Please, see since odbid_eqp that I am trying to insert is not presen in table unique index should not be thrown in any case? I am confused.

    Edited by: user13755008 on Jan 18, 2013 8:24 PM
  • 7. Re: unique constraint not working
    sb92075 Guru
    Currently Being Moderated
    user13755008 wrote:
    select * from all_cons_columns where constraint_name='HSRP_TRACK_UK';

    OWNER CONSTRAINT_NAME TABLE_NAME
    ------------------------------ ------------------------------ ------------------------------
    COLUMN_NAME
    --------------------------------------------------------------------------------------------------------------------------------
    POSITION
    ----------
    NC3 HSRP_TRACK_UK HSRP_TRACK
    ODBID_EQP
    1

    NC3 HSRP_TRACK_UK HSRP_TRACK
    START_DATE
    4

    NC3 HSRP_TRACK_UK HSRP_TRACK
    OBJECT_NUM
    2

    NC3 HSRP_TRACK_UK HSRP_TRACK
    did some details get misplaced here?

    >
    >
    >
    select * from all_ind_columns where INDEX_NAME='HSRP_OBJ_INTER_ST_UIX';

    INDEX_OWNER INDEX_NAME TABLE_OWNER TABLE_NAME
    ------------------------------ ------------------------------ ------------------------------ ------------------------------
    COLUMN_NAME
    --------------------------------------------------------------------------------------------------------------------------------
    COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESC
    --------------- ------------- ----------- ----
    NC3 HSRP_OBJ_INTER_ST_UIX NC3 HSRP_TRACK
    ODBID_EQP
    1 22 0 ASC

    NC3 HSRP_OBJ_INTER_ST_UIX NC3 HSRP_TRACK
    OBJECT_NUM
    2 4 4 ASC

    NC3 HSRP_OBJ_INTER_ST_UIX NC3 HSRP_TRACK
    INTERFACE
    3 32 32 ASC

    NC3 HSRP_OBJ_INTER_ST_UIX NC3 HSRP_TRACK
    START_DATE
    4 7 0 ASC



    select * from nc3.hsrp_track where odbid= 87820678;

    ODBID CREATETMSTMP CREATEUS CREATETRAN
    ---------- --------------------------------------------------------------------------- -------- ----------------
    LASTUPDTMSTMP LASTUPDU LASTUPDTRAN ODBID_EQP
    --------------------------------------------------------------------------- -------- ---------------- ----------
    ODBID_START_ORDER ODBID_STOP_ORDER ODBID_PREV START_DATE STOP_DATE STATUS COMPONENT_TYPE OBJE
    ----------------- ---------------- ---------- ---------- ---------- ---------------- -------------------------------- ----
    INTERFACE PROCESS
    -------------------------------- ---------------
    87820678 2013-01-18-11:29:29.285050 NC3USER StoreRouting
    2013-01-18-11:29:29.285050 NC3USER StoreRouting 87811116
    87811106 2013-01-18 9999-12-31 Started Equipment 1
    Serial0/0 line-protocol




    SQL> insert into NC3.HSRP_TRACK Values(3,'2013-01-18-11:29:29.436033', 'NC3USER ', 'StoreRouting', '2013-01-18-11:29:29.436033', 'NC3USER ', 'StoreRouting ', 87820377,'','','', '2013-01-18','9999-12-31','Started', 'Equipment', '1', 'Serial0/0', '');
    insert into NC3.HSRP_TRACK Values(3,'2013-01-18-11:29:29.436033', 'NC3USER ', 'StoreRouting', '2013-01-18-11:29:29.436033', 'NC3USER ', 'StoreRouting ', 87820377,'','','', '2013-01-18','9999-12-31','Started', 'Equipment', '1', 'Serial0/0', '')
    *
    ERROR at line 1:
    ORA-00001: unique constraint (NC3.HSRP_TRACK_UK) violated



    SQL> desc nc3.hsrp_track
    Name Null? Type
    ---------------------------------------------------------------------- -------- -----------------------------------------------
    ODBID NOT NULL NUMBER(11)
    CREATETMSTMP NOT NULL TIMESTAMP(6)
    CREATEUSER NOT NULL CHAR(8)
    CREATETRAN NOT NULL CHAR(16)
    LASTUPDTMSTMP NOT NULL TIMESTAMP(6)
    LASTUPDUSER NOT NULL CHAR(8)
    LASTUPDTRAN NOT NULL CHAR(16)
    ODBID_EQP NOT NULL NUMBER(11)
    ODBID_START_ORDER NUMBER(11)
    ODBID_STOP_ORDER NUMBER(11)
    ODBID_PREV NUMBER(11)
    START_DATE NOT NULL DATE
    STOP_DATE NOT NULL DATE
    STATUS NOT NULL CHAR(16)
    COMPONENT_TYPE NOT NULL VARCHAR2(32)
    OBJECT_NUM NOT NULL VARCHAR2(4)
    INTERFACE NOT NULL VARCHAR2(32)
    PROCESS VARCHAR2(15)



    SQL> select * from nc3.hsrp_track where odbid_eqp=87820377;

    no rows selected




    Please, see since odbid_eqp that I am trying to insert is not presen in table unique index should not be thrown in any case? I am confused.

    Edited by: user13755008 on Jan 18, 2013 8:24 PM
    post results from SQL below

    SELECT COLUMN_NAME, POSITION, CONSTRAINT_NAME FROM ALL_CONS_COLUMNS
    WHERE TABLE_NAME = 'HSRP_TRACK' AND OWNER = 'NC3';
  • 8. Re: unique constraint not working
    831564 Newbie
    Currently Being Moderated
    SQL> desc nc3.hsrp_track
    Name Null? Type
    ---------------------------------------------------------------------- -------- -----------------------------------------------
    ODBID NOT NULL NUMBER(11)
    CREATETMSTMP NOT NULL TIMESTAMP(6)
    CREATEUSER NOT NULL CHAR(8)
    CREATETRAN NOT NULL CHAR(16)
    LASTUPDTMSTMP NOT NULL TIMESTAMP(6)
    LASTUPDUSER NOT NULL CHAR(8)
    LASTUPDTRAN NOT NULL CHAR(16)
    ODBID_EQP NOT NULL NUMBER(11)
    ODBID_START_ORDER NUMBER(11)
    ODBID_STOP_ORDER NUMBER(11)
    ODBID_PREV NUMBER(11)
    START_DATE NOT NULL DATE
    STOP_DATE NOT NULL DATE
    STATUS NOT NULL CHAR(16)
    COMPONENT_TYPE NOT NULL VARCHAR2(32)
    OBJECT_NUM NOT NULL VARCHAR2(4)
    INTERFACE NOT NULL VARCHAR2(32)
    PROCESS VARCHAR2(15)
  • 9. Re: unique constraint not working
    sb92075 Guru
    Currently Being Moderated
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ


    SCROLL DOWN TO #9!

    learn how to use
     tags!                                                                                                                                                                                                                                                                                                                                                
  • 10. Re: unique constraint not working
    831564 Newbie
    Currently Being Moderated
    will do that from next time. Thanks
  • 11. Re: unique constraint not working
    sb92075 Guru
    Currently Being Moderated
    user13755008 wrote:
    will do that from next time. Thanks
    I assist you more next time.
    You are Welcome.
  • 12. Re: unique constraint not working
    831564 Newbie
    Currently Being Moderated
    SQL> select column_name,position,constraint_name from all_cons_columns where table_name='HSRP_TRACK' and OWNER='NC3';
    
    COLUMN_NAME
    --------------------------------------------------------------------------------------------------------------------------------
      POSITION CONSTRAINT_NAME
    ---------- ------------------------------
    ODBID
               SYS_C0048967
    
    CREATETMSTMP
               SYS_C0048968
    
    CREATEUSER
               SYS_C0048969
    
    CREATETRAN
               SYS_C0048970
    
    LASTUPDTMSTMP
               SYS_C0048971
    
    LASTUPDUSER
               SYS_C0048972
    
    LASTUPDTRAN
               SYS_C0048973
    
    ODBID_EQP
               SYS_C0048974
    
    START_DATE
               SYS_C0048975
    
    STOP_DATE
               SYS_C0048976
    
    STATUS
               SYS_C0048977
    
    COMPONENT_TYPE
               SYS_C0048978
    
    OBJECT_NUM
               SYS_C0048979
    
    INTERFACE
               SYS_C0048980
    
    ODBID
             1 HSRP_TRACK_PK
    
    OBJECT_NUM
             2 HSRP_TRACK_UK
    
    INTERFACE
             3 HSRP_TRACK_UK
    
    START_DATE
             4 HSRP_TRACK_UK
    
    ODBID_EQP
             1 HSRP_ODBID_EQP_FK
    
    ODBID_PREV
             1 HSRP_ODBID_PREV_FK
    
    ODBID_STOP_ORDER
             1 HSRP_ODBID_STP_FK
    
    ODBID_START_ORDER
             1 HSRP_ODBID_STR_FK
    
    ODBID_EQP
             1 HSRP_TRACK_UK
    
    
    23 rows selected.
  • 13. Re: unique constraint not working
    831564 Newbie
    Currently Being Moderated
    I guess, I should drop the unique constraint and add it again.
  • 14. Re: unique constraint not working
    831564 Newbie
    Currently Being Moderated
    Even dropping and creating the constraint again didn't work. Any ideas out there?. Thanks
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points