Home » SQL & PL/SQL » SQL & PL/SQL » unique constraint violation for two columns.. very confusing! (any)
unique constraint violation for two columns.. very confusing! [message #411828] Tue, 07 July 2009 01:21 Go to next message
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 Go to previous messageGo to next message
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 Go to previous message
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

Previous Topic: Find the Invalid Entry in the table
Next Topic: Date (merged 2) 10g
Goto Forum:
  


Current Time: Mon Feb 17 14:56:55 CST 2025