unique constraint violation for two columns.. very confusing! [message #411828] |
Tue, 07 July 2009 01:21  |
prajjwal
Messages: 28 Registered: January 2008 Location: kolkata
|
Junior Member |

|
|
Hi,
Please look into this scenerio:
SQL> create table tx1(aa int, bb int);
Table created.
SQL> alter table tx1 add unique (aa,bb);
Table altered.
SQL> insert into tx1 values (1,null);
1 row created.
SQL> insert into tx1 values (1,null);
insert into tx1 values (1,null)
*
ERROR at line 1:
ORA-00001: unique constraint (USER2.SYS_C005331) violated
I suppose two nulls are different, which implies tuples (1,null) != (1,null). Why this error then?
Regards,
Prajjwal
|
|
|
Re: unique constraint violation for two columns.. very confusing! [message #411845 is a reply to message #411828] |
Tue, 07 July 2009 02:36   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Null is undefined.
So if two nulls are the same or different is also undefined.
So basically since MIGHT be possible that they are identical, the unique constraint could be violated.
- 1 compared to 1 is true.
- null compared to null is null
- true AND null results in null again.
See here for another example.
|
|
|
Re: unique constraint violation for two columns.. very confusing! [message #411847 is a reply to message #411828] |
Tue, 07 July 2009 02:38  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
SQL Reference
Chapter 8 Common SQL DDL Clauses
Section constraint
Paragraph Unique Constraints
Quote: | To satisfy a unique constraint, no two rows in the table can have the same value for the unique key. However, the unique key made up of a single column can contain nulls. To satisfy a composite unique key, no two rows in the table or view can have the same combination of values in the key columns. Any row that contains nulls in all key columns automatically satisfies the constraint. However, two rows that contain nulls for one or more key columns and the same combination of values for the other key columns violate the constraint.
|
Regards
Michel
[Updated on: Tue, 07 July 2009 02:39] Report message to a moderator
|
|
|