Home » SQL & PL/SQL » SQL & PL/SQL » unique constraint violated when update record
unique constraint violated when update record [message #159504] Mon, 20 February 2006 01:18 Go to next message
juicyapple
Messages: 92
Registered: October 2005
Member
I have an existing form to import data from text file to 1 master and 1 details table.
It works when insert new records to the table, but when I need to update the records, updating is failed with message 'unique constraint violated' is showed.

Please give some idea on how to solve this..Thanks.
Re: unique constraint violated when update record [message #159506 is a reply to message #159504] Mon, 20 February 2006 01:28 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member

Quote:

'unique constraint violated'


Isn't the error message self-explanatory ?

It means that you are trying to update a column with a value that is already present in that column and the column has a unique/primary key constraint on it.


Re: unique constraint violated when update record [message #159524 is a reply to message #159506] Mon, 20 February 2006 02:51 Go to previous messageGo to next message
juicyapple
Messages: 92
Registered: October 2005
Member
Isn't the error message self-explanatory ? Yes.

It means that you are trying to update a column with a value that is already present in that column and the column has a unique/primary key constraint on it.
Do you have any idea so as I can update the table?
Re: unique constraint violated when update record [message #159557 is a reply to message #159524] Mon, 20 February 2006 04:55 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member
juicyapple wrote on Mon, 20 February 2006 14:21

Do you have any idea so as I can update the table?



How can i have an idea on the data which you are inserting??







Re: unique constraint violated when update record [message #159564 is a reply to message #159557] Mon, 20 February 2006 05:46 Go to previous messageGo to next message
ashok_nr23
Messages: 30
Registered: April 2005
Member
U can disable the constraint to avoid constraint error, But it is not advisable.

Ashok.
Re: unique constraint violated when update record [message #159615 is a reply to message #159564] Mon, 20 February 2006 10:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
ashok_nr23 wrote on Mon, 20 February 2006 12:46

U can disable the constraint to avoid constraint error, But it is not advisable.


Don't even consider going that route if you want to keep your job...
Re: unique constraint violated when update record [message #159661 is a reply to message #159504] Mon, 20 February 2006 18:50 Go to previous messageGo to next message
juicyapple
Messages: 92
Registered: October 2005
Member
Is that mean updating is not allowed for two tables which have unique/primary key constraint on it.
Re: unique constraint violated when update record [message #159706 is a reply to message #159661] Tue, 21 February 2006 00:10 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Of course not; just don't update tables in a way that referential constraint is violated.

[EDIT]

I'm sorry, this answer has nothing to do with the subject; I wasn't reading carefully enough and post #1's "master-detail" relationship mislead me to referential integrity instead of unique constraint violation.

[Updated on: Tue, 21 February 2006 00:52]

Report message to a moderator

Re: unique constraint violated when update record [message #159707 is a reply to message #159706] Tue, 21 February 2006 00:14 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Example:
suppose you have a table containing data about people.
The primary key is an id.
The social security number (ssn) is unique, no two people share the same ssn. This is the unique constraint
You may update the ssn of one person, but if you update it to a value already assigned to another person, you violate the unique constraint.

Get it?
Re: unique constraint violated when update record [message #159708 is a reply to message #159707] Tue, 21 February 2006 00:17 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member

@juicyapple ,

I believe that Frank made is as simple as he could but still if you are unable to understand then please read any good book or check the documentation for the definition of Unique constraint.
Re: unique constraint violated when update record [message #202561 is a reply to message #159504] Fri, 10 November 2006 06:31 Go to previous messageGo to next message
artmast
Messages: 11
Registered: October 2006
Junior Member
According to http://oratip.com/ORA-00001.html this is to do with duplicate key. Excuse me for I am a beginner and just trying this out, but what is a 'key'?
Re: unique constraint violated when update record [message #202564 is a reply to message #202561] Fri, 10 November 2006 06:51 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
'Key' in this context is the column that has the unique constraint on it.

Here is a worked example to show you what's happening:

SQL> create table temp_unq (col_1  varchar2(10));

Table created.

SQL> 
SQL> alter table temp_unq add (constraint col_1_unq unique (col_1));

Table altered.

SQL> 
SQL> insert into temp_unq values ('Value 1');

1 row created.

SQL> insert into temp_unq values ('Value 2');

1 row created.

SQL> insert into temp_unq values ('Value 3');

1 row created.

SQL> 
SQL> select * from temp_unq;

COL_1
----------
Value 1
Value 2
Value 3

We've creates a table, added a unique constraint to the column COL_1 and added 3 valid rows of data.

SQL> update temp_unq 
  2  set    col_1 = 'Value 4' 
  3  where  col_1 = 'Value 3';

1 row updated.

SQL> select * from temp_unq;

COL_1
----------
Value 1
Value 2
Value 4

Here, we've updated the row with a value of 'Value 3' and changed it to 'Value 4'. There was no pre-existing row in the database with a value of 'Value 4', so this is quite allowable.

SQL> 
SQL> update temp_unq
  2  set    col_1 = 'Value 4'
  3  where  col_1 = 'Value 2';
update temp_unq
*
ERROR at line 1:
ORA-00001: unique constraint (JOHN_TEST.COL_1_UNQ) violated
Here, we've tried to update the row with a value of 'Value 2' to 'Value 4'. There was already a row with this value in it (from the previous test), so this failed with a unique constraint violation, just like you're getting.
Previous Topic: SQL Loader
Next Topic: utl_file.put will never flush?
Goto Forum:
  


Current Time: Tue Dec 06 00:30:04 CST 2016

Total time taken to generate the page: 0.11064 seconds