Re: relaxing the unique key constraint for a transaction

From: Anurag Varma <avdbi_at_hotmail.com>
Date: 23 Aug 2001 11:22:58 -0700
Message-ID: <7171ca2d.0108231022.47da8a55_at_posting.google.com>


Sybrand is correct!
Though if you really want to do it, this nasty workaround might work in your case,, if you can add a column to that table.

Consider table
test (a NUMBER, b VARCHAR2(10), a_new NUMBER); Where a has a unique constraint
and a_new is the column you added (to the exact specs of column a)

Now update column a_new instead. So instead of writing UPDATE test SET a = 10 WHERE a = 1;
Do a: UPDATE test SET a_new = 10 WHERE a = 1;

At the end of your Updates, write:

UPDATE test SET a = a_new WHERE a_new IS NOT NULL; ... and clean up a_new.

:) Nasty isn't it
Anurag

postbus_at_sybrandb.demon.nl (Sybrand Bakker) wrote in message news:<a20d28ee.0108230310.60b13a2f_at_posting.google.com>...
> gkonduri_at_us.oracle.com (Gangadhar) wrote in message news:<578815ac.0108221716.6bac3299_at_posting.google.com>...
> > Hi,
> >
> > I have a case where i need to do an update to the rows in a database
> > which temporarily violates the unique key constraint in the database.
> > I was wondering if i can temporarily relax the uniqueness check for my
> > transaction without
> > affecting other users.
> >
> > Thanks in advance,
> > Gangadhar
>
> No.
> An unique constraint is imposed on table level and hence affects all users.
> If you disable it, it will be disabled for all users.
> I can hardly believe anyone from Oracle is asking such a question.
> Maybe I should consider switching to DB2.
>
> Yikes!
>
> Regards,
>
> Sybrand Bakker, Senior Oracle DBA
Received on Thu Aug 23 2001 - 20:22:58 CEST

Original text of this message