Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2006
    Posts
    38

    Unanswered: Disabling unique index

    Is there a way to disable a unique index in a table in Informix 9.3 for a particular set of transactions and then enable it again once the transaction is done?

    Thank you very much for your patience and help. Best regards.

  2. #2
    Join Date
    Dec 2003
    Location
    North America
    Posts
    146
    There's no way that I know of. Even if you dropped the uniquely constrained index & populated the table with data during the transaction, recreating the index would fail due to the duplicate data present in the table column(s).

    If you have no other recourse, I'd drop the index & recreate it without the unique constraint or add another column (like datetime) to the index so it will once again represent a unique set of column values.

  3. #3
    Join Date
    Jan 2006
    Posts
    38
    Quote Originally Posted by mjldba
    There's no way that I know of. Even if you dropped the uniquely constrained index & populated the table with data during the transaction, recreating the index would fail due to the duplicate data present in the table column(s).

    If you have no other recourse, I'd drop the index & recreate it without the unique constraint or add another column (like datetime) to the index so it will once again represent a unique set of column values.
    mjldba, Thanks a lot for your response.

    Seems you are correct. However the thing is that I have two sources that provide data to this table. I am not supposed to allow duplicates, that's why I put a Unique index on the desired set of columns. This is fine as long as data is coming from first one source. Now, when it comes from the second source - it has one of those column's (part of the unique index) value as NULL. Now there can be more than one rows from the second source with the same set of values for rest of the columns and in that case I need to pop-up the version (another column of the table) by 1. But since, the column that I mentioned above keeps getting NULL values (which they will always be) and it in-validates the uniqueness.

    Now, having a timestamp is okay - but again there is a problem - I insert the datetime YEAR TO FRACTION(3) using CURRENT with the insert statement - Now these inserts (could be multiple) get fired through a trigger (or consider inside from a stored procedure) and keeps giving the same value to all timestamps.

    DATETIME doesn't give distinct times for inserts in the same transaction - all get the same value. Is there any way to get around this?

    I know the explanation is lengthy but the details of the problem is simple and I am unable to sort out how to handle this. Removing the index totally is something that would cause a complete re-design of the client's using that table.

    Kindly suggest as to how should I proceed to tackle the situation while I am myself working on it. Thank you very much once again. Best regards.

  4. #4
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780
    Hi,

    Remember that you must have defined in $ONCONFIG file, the USEOSTIME=1 variable, for fraction use.

    Gustavo.

  5. #5
    Join Date
    Apr 2004
    Location
    Brussels
    Posts
    57
    - In a procedure "current" will returns always the same time.
    Use something like this : SELECT DBINFO('utc_to_datetime', sh_curtime) AS CURRENT into vc FROM sysmaster:sysshmvals;

    - you can deffer the check for a unique key constraint in a transaction with :
    "set constraint <constr_unique_key_name> deferred"
    On the "commit" the constraint will be checked and you will have the appropriate error message (and a rollback)

    - you can also capture rows with duplicated keys with :
    stat violations table for <table> ;
    set indexes <index_name> filtering ;
    insert into
    set indexes .... immediate
    and look into the violation table for rows with duplicated keys

    Hopely you find here
    something that helps
    Yves
    Yves & Willy

  6. #6
    Join Date
    Jan 2006
    Posts
    38
    Thanks guys for the informative posts. Best regards.

  7. #7
    Join Date
    Mar 2006
    Posts
    2
    http://publib.boulder.ibm.com/infoce...doc/sqls02.htm

    >>-SET INDEXES--+---index-+--+---------------------------------->
    '-FOR--table-'

    >--+-+-ENABLED--+-----------------+----------------------------><
    | '-DISABLED-' |
    | .-WITHOUT ERROR-. |
    '-FILTERING--+-WITH ERROR----+-'

Posting Permissions

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