unique constraint violation

[mgl70]

Hi,
I got the error "unique constraint violation" when I am updating
a table. The table has a unique key with muti columns.
I am trying to the update a unique key column. If I change this the uniqueness
will be gone. None of the columns are primary key.

How Can I update?
I searched in the web. I found the the following info.

1). Remove the unique restriction.

2).Change the restriction to allow duplicate keys.
An index could be changed to be a non-unique index,
but remember that the primary key must always be unique.


The first one I canot do it.

I did not understand the second one.: 'Change the restriction to allow duplicate keys'How Can I do this?

Any idea please.

Thanks.

 

[Dagon]

I don't think there is much difference. Either way, you are removing the unique constraint and allowing duplicate values.

There is no getting round this. Either you want the column to have unique values or you don't. It's nonsense to be looking for some sort of third option. You are going to have to work out why you are creating non-unique values with the update. The update must either be updating multiple rows to the same key or updating a key to something that already exists in the table.

 

[SantaMufasa]

First of all, MGL, is there a business purpose to your currently unique-key column(s) to have no duplicates? Specifically, should the data in the currently unique column(s) have no duplicates or are duplicates satisfactory?

Option 1: If duplicates are, in fact, satisfactory, then there is no reason to have the UNIQUE constraint on the column(s). Therefore, a) identify the name of the constraint either from the error message your received (the error lists the name of the constraint) or query USER_CONSTRAINTS, then b) issue the command:

DROP CONSTRAINT <constraint_name>;

Option 2: if duplicates represent an invalid business condition, (i.e., duplicates are not allowed), then just don't enter duplicate values...determine the erroneous business situation that is generating duplicates and remedy the error situation.

Let us know your findings/outcomes.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.