1 2 Previous Next 15 Replies Latest reply: Jan 19, 2013 5:56 AM by Solomon Yakobson RSS

    unique constraint not working

    831564
      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
          what command did you use to create the constraints?
          • 2. Re: unique constraint not working
            831564
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            will do that from next time. Thanks
                            • 11. Re: unique constraint not working
                              sb92075
                              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
                                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
                                  I guess, I should drop the unique constraint and add it again.
                                  • 14. Re: unique constraint not working
                                    831564
                                    Even dropping and creating the constraint again didn't work. Any ideas out there?. Thanks
                                    1 2 Previous Next