Re: Unique Keys on Corrupt Serial Fields
> I have a customer who (somehow) got duplicate values in a SERIAL field
> that is keyed uniquely. I need to figure out a way to rebuild his data
> file and make the values in the SERIAL field unique. I have tried
> DROPping the column, ADDing it back as type integer, then MODIFYing it
> to type SERIAL but the data in the column is still not unique. I don't
> want to UNLOAD the table, write a program to change the serial field's
> value in the unloaded ascii file to a unique number (record number), and
> then insert the data back into the data file, unless that is the only way to
> do it. If I could simply do something like this:
stuff seleted
> my problem would be solved but I don't know how to set a unique value
> into un_sys_no after it as been dropped and then added. Any help would
> be greatly appreciated!
If you insert a 0 into a serial field Informix will assign it
a unique value. The following are two suggestions, one to
rebuild all serial numbers and the other to do just the duplicates.
To rebuild ALL serial numbers.
1) unload the current table, HOWEVER replace the serial field
with the constant 0
unload to univ.uld select 0, f2, d3, f4 from univ; ^ This is the zero that goes
in place of your serial field.
2) Drop the current table. Make sure you have a GOOD backup.
3) Re-Create the table; Make sure there is a unique index
on the serial number field.
4) Re-load the table. The 0 in step one will be replaced with
the serial number.
To rebuild just the duplicates. Another approch is just to delete
and reload the just ones with duplicate serial numbers
1) Select the duplicates into a temp table.
select serial_field, count(*) from univ
group by serial_field having count(*) > 1
into temp A;
This find all the serial fields with duplicates.
2) Unload the duplicates, againg replacing the serial field with
an 0.
unload to "dup.uld"
select 0, f2, f3 ,f4 from univ
where serial_field in ( select serial_field from A );
3) Delete the duplicates, again make sure you have a good backup
delete from univ
where serial_field in ( select serial_field from A );
4) Creat a unique index on the serial field.
5) Reload the duplicates, again the 0 from step 1 will be
replaced with a serial number...
Email me if you need more information.
Best of Luck, - Lester
#############################################################################
# Lester Knutsen lester@access.digex.net #
# Advanced DataTools Corporation Voice: 703-256-0267 #
# Grant group privileges for Informix databases with DB Privileges #
#############################################################################