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.

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.

Hi,

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

Gustavo.

- 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

Thanks guys for the informative posts. Best regards.

http://publib.boulder.ibm.com/infoce...doc/sqls02.htm

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

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