Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195

    Unanswered: Tracking Down Cause of Unique Key Constraint

    I have this error:

    Code:
    ORA-00001: UNIQUE CONSTRAINT
    (PK1.KSRT)
    VIOLATED
    I know what the error means but I need to see what the constraint is to see how it was violated. Can someone help me achieve this? Thanks, Jeremy
    Nothing better than a good ride.

  2. #2
    Join Date
    May 2003
    Location
    Dublin, Ireland
    Posts
    44

    Re: Tracking Down Cause of Unique Key Constraint

    list of columns for this constraint can be obtained from dba_cons_columns or user_cons_columns.
    Just use constraint_name in the where clause (also owner for the dba_cons_columns).

    Originally posted by JCScoobyRS
    I have this error:

    Code:
    ORA-00001: UNIQUE CONSTRAINT
    (PK1.KSRT)
    VIOLATED
    I know what the error means but I need to see what the constraint is to see how it was violated. Can someone help me achieve this? Thanks, Jeremy

  3. #3
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    You lost me. I'm new to this as I am a developer, not a DBA. Can you explain it in more easily to understand terms. Sorry, Jeremy
    Nothing better than a good ride.

  4. #4
    Join Date
    May 2003
    Location
    Dublin, Ireland
    Posts
    44
    The queries you can use:
    as PK1 user

    select table_name,column_name from user_cons_columns where constraints_name='KSRT' order by position;

    as system (or any other dba user)

    select table_name,column_name from dba_cons_columns where constraints_name='KSRT' and owner='PK1' order by position;

    This will give you ordered list of columns (and tables) that have to be unique. After that it is only a matter of checking of the data that is breaching this uniqueness.


    Originally posted by JCScoobyRS
    You lost me. I'm new to this as I am a developer, not a DBA. Can you explain it in more easily to understand terms. Sorry, Jeremy

  5. #5
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    This is what I get from doing that:

    Code:
    SQL*Plus: Release 8.1.7.0.0 - Production on Thu May 22 09:30:48 2003
    
    (c) Copyright 2000 Oracle Corporation.  All rights reserved.
    
    Connected.
    select table_name,column_name from user_cons_columns where constraints_name='KSRT' order by position
                                                               *
    ERROR at line 1:
    ORA-00904: invalid column name
    Got any idea? Thanks, Jeremy
    Nothing better than a good ride.

  6. #6
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    I changed constraints to constraint and got no rows selected. That doesn't make sense does it? Here is what I just got:

    Code:
    select table_name,column_name from user_cons_columns where constraint_name='KSRT' order by position;
    
    no rows selected
    ????? Thanks for your help, Jeremy
    Nothing better than a good ride.

  7. #7
    Join Date
    May 2003
    Location
    Dublin, Ireland
    Posts
    44
    It's my typing:
    it should be constraint_name= not constraints_name=
    Her is the query:
    select table_name,column_name from user_cons_columns where constraint_name='KSRT' order by position


    Originally posted by JCScoobyRS
    This is what I get from doing that:

    Code:
    SQL*Plus: Release 8.1.7.0.0 - Production on Thu May 22 09:30:48 2003
    
    (c) Copyright 2000 Oracle Corporation.  All rights reserved.
    
    Connected.
    select table_name,column_name from user_cons_columns where constraints_name='KSRT' order by position
                                                               *
    ERROR at line 1:
    ORA-00904: invalid column name
    Got any idea? Thanks, Jeremy

  8. #8
    Join Date
    May 2003
    Location
    Dublin, Ireland
    Posts
    44
    Do you by any chance create table or this constraint dynamically in the code that is breaching it later. In other words is the table already created and it has mentioned constraint?

    Originally posted by JCScoobyRS
    I changed constraints to constraint and got no rows selected. That doesn't make sense does it? Here is what I just got:

    Code:
    select table_name,column_name from user_cons_columns where constraint_name='KSRT' order by position;
    
    no rows selected
    ????? Thanks for your help, Jeremy

  9. #9
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    My code doesn't do anything. I am inserting information into 4 tables from another database. I look into DBA Studio to see the constraints on the tables and there are none. Got anymore ideas? Thanks, Jeremy

    P.S. - I selected * from USER_CONS_COLUMNS and USER_CONSTRAINTS but none of them matched the constraint or table I'm concerned with. Thanks, Jeremy
    Nothing better than a good ride.

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by JCScoobyRS
    I changed constraints to constraint and got no rows selected. That doesn't make sense does it? Here is what I just got:

    Code:
    select table_name,column_name from user_cons_columns where constraint_name='KSRT' order by position;
    
    no rows selected
    ????? Thanks for your help, Jeremy
    The constraint belongs to user PK1 (as shown in original error message). Are you connect as user PK1 when you do this query? If not do this instead:

    select table_name,column_name
    from ALL_cons_columns
    where constraint_name='KSRT'
    and owner='PK1'
    order by position;

  11. #11
    Join Date
    May 2003
    Location
    Dublin, Ireland
    Posts
    44
    What version and product of Oracle you are using and on what platform?

    Originally posted by JCScoobyRS
    My code doesn't do anything. I am inserting information into 4 tables from another database. I look into DBA Studio to see the constraints on the tables and there are none. Got anymore ideas? Thanks, Jeremy

    P.S. - I selected * from USER_CONS_COLUMNS and USER_CONSTRAINTS but none of them matched the constraint or table I'm concerned with. Thanks, Jeremy

  12. #12
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    Oracle 8.1.7 on Windows 2000. I can get you any information you need. I was logged in as user PK1 when I queried. Thanks, Jeremy
    Nothing better than a good ride.

  13. #13
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    KSRT is an index...just figured that out. I see that it is looking at "RT_SEQNUM" as the column. Got any idea how to get more information from it? Thanks, Jeremy
    Nothing better than a good ride.

  14. #14
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by JCScoobyRS
    KSRT is an index...just figured that out. I see that it is looking at "RT_SEQNUM" as the column. Got any idea how to get more information from it? Thanks, Jeremy
    Ah yes, the error message was misleading. What sort of further information are you looking for? If you mean, what value you tried to insert that violated the unique index then no, it can't tell you that.

  15. #15
    Join Date
    May 2003
    Location
    Dublin, Ireland
    Posts
    44
    Since this is unique index value in the RT_SEQNUM cannot be repeated. Check your data.
    If amount of data is big then checking manually is pontless. Just run on the source database:

    select rt_seqnum, count(*) from xxxxx group by rt_seqnum order by 2;

    where xxxxx is a source table and column name (RT_SEQNUM) is the same.

    This will give you all duplicated values.

    BTW judging by the column name this value is probably generated from Oracle sequence.

    Another this, maybe as our etimmed moderator said message is mileading but this index enforcess uniqueness and this is a constraint.


    Originally posted by JCScoobyRS
    KSRT is an index...just figured that out. I see that it is looking at "RT_SEQNUM" as the column. Got any idea how to get more information from it? Thanks, Jeremy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •