Monday, 26 August 2013

Bypass or fulfill unique constraint for large update

Bypass or fulfill unique constraint for large update

I have a database of order line items. Two of the columns are for
controlling batch processing. They're called prod_batch_id (number) and
prod_batch_index (varchar2 - 10 byte). They both need to be either null or
filled, and prod_batch_index has a unique index on it. To fetch unbatched
orders we simply pull all records where prod_batch_id is null.
The table (ord_lin) has 171,602 records that pertain to a particular
product line that I'm about to start batching. Rather than limit my
batching queries by the date that we start I'd like to add all of those
records to a dummy batch.
I'm not well versed in Oracle, or even SQL, so I don't know if it's
possible but would there be a way to temporarily disable the unique
constraint for the update, and then enable it again without it complaining
about those records?
If that's not an option, how would I go about creating the unique values
for each record during the update?
Notes: The batch system was originally made for a whole new product line
and was designed this way because those products didn't exist before
batching did. I will likely have several other product lines that will get
switched to the batching system down the line, so I'll be back to this
point again sooner or later.
I'm also aware that I could do things like make another field to act as a
"batched" flag that doesn't have such constraints, but that would involve
updating a lot of programs.

No comments:

Post a Comment