Please help me to figure out my error.

[Suyash Upadhyay]

Hello all,
I have studied that while we apply UNIQUE constraint any column have maximum one field as NULL, but in my code there are two NULL values, Please help me to figure it out.


SQL> create table student(enrollment_no varchar2(10) constraint c UNIQUE,name varchar2(20));

Table created.

SQL> insert into student(name) values('Jhon');

1 row created.

SQL> insert into student(name) values('Robert');

1 row created.

SQL> select * from student;

ENROLLMENT NAME
---------- --------------------
Jhon
Robert

Both rows have enrollment no is as NULL. How it is possible?

Suyash.Upadhyay

[rushdishams]

You did not make any mistake mate. oracle treats 2 different NULL values as 2 different values. So, the NULL value for the first record is treated different value with the NULL value for the second record even if you put UNIQUE constraint on the field.

Cheers!

[Suyash Upadhyay]

thanx rushdishams, I got your point.
Thanx a lot.

[Suyash Upadhyay]

Is it also true with MySQL?

[rushdishams]

that i don't know. you can post that question as MySQL thread