Thread: unique constraint - bug?
All:
Run the following script:
drop table letter;
create table letter (
a char(1),
b char(1),
c char(1),
unique (a, b, c)
);
insert into letter values('1','2','3');
insert into letter values('1','2','3');
insert into letter (a,c) values ('1','3');
insert into letter (a,c) values ('1','3');
Notice that the second insert of ('1','2','3') yields an error message,
which is the correct behavior - (that is the unique constaint is
working),
while the last insert of ('1','3') does not yield an error message -
(that is
the unique constraint doesn't work in this case - I think it should).
It appears as though the null value inserted for column b causes an
abrogation
of the unique constaint.
Is this intended or is it a bug?
Or should I be doing something different?
Thanks
Merrill
> unique (a, b, c)
> );
>
> insert into letter values('1','2','3');
> insert into letter values('1','2','3');
>
> insert into letter (a,c) values ('1','3');
> insert into letter (a,c) values ('1','3');
>
> Notice that the second insert of ('1','2','3') yields an
> error message, which is the correct behavior - (that is the unique
> constaint is working), while the last insert of ('1','3') does not yield
> an error message - (that is the unique constraint doesn't work in this
> case - I think it should).
NULL != NULL - ie you can't say that both keys are equal.
(That's why PK constraint disallow NULLs...)
Vadim
Merrill Oveson <merrill@actarg.com> writes:
> It appears as though the null value inserted for column b causes an
> abrogation of the unique constaint.
Two nulls are never considered equal, therefore the unique constraint
does not trigger.
This is correct behavior according to SQL92 4.10.2:
A unique constraint is satisfied if and only if no two rows in
a table have the same non-null values in the unique columns. In
^^^^^^^^
addition, if the unique constraint was defined with PRIMARY KEY,
then it requires that none of the values in the specified column or
columns be the null value.
(The second sentence just says that PRIMARY KEY implies NOT NULL as well
as UNIQUE.)
Another way to look at it is that the comparison to see whether the two
NULLs are equal would yield NULL, and a NULL result for a constraint
condition is not considered to violate the constraint.
Another way to look at it is that NULL means "I don't know what the
value is", so if you don't know what the values in two rows really are,
you don't know whether they're equal either. I suppose you could make
a case for either accepting or rejecting the UNIQUE constraint in that
situation --- but SQL92 chose the "accept" decision, and I think that
for the majority of practical applications they made the right choice.
If you don't like that behavior, possibly your column should be defined
as NOT NULL.
regards, tom lane
Tom Lane wrote:
>Merrill Oveson <merrill@actarg.com> writes:
>> It appears as though the null value inserted for column b causes an
>> abrogation of the unique constaint.
>
>Two nulls are never considered equal, therefore the unique constraint
>does not trigger.
>
>This is correct behavior according to SQL92 4.10.2:
>
> A unique constraint is satisfied if and only if no two rows in
> a table have the same non-null values in the unique columns. In
> ^^^^^^^^
> addition, if the unique constraint was defined with PRIMARY KEY,
> then it requires that none of the values in the specified column or
> columns be the null value.
I think you are not interpreting this right:
A unique constraint is satisfied if and only if no two rows in
a table have the same non-null values in the unique columns.
I think it means that nulls are to be left out of account in the
comparison. So
a, NULL, c
a, NULL, c
are neither equal nor unequal (as you say) but they do violate a UNIQUE
constraint because the nulls should be ignored altogether. The two
rows do have the "same non-null values".
Surely this interpretation is closer to what users would expect?
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Behold, what manner of love the Father hath bestowed
upon us, that we should be called the sons of God..."
I John 3:1
"Oliver Elphick" <olly@lfix.co.uk> writes:
> Tom Lane wrote:
>> Two nulls are never considered equal, therefore the unique constraint
>> does not trigger.
> I think you are not interpreting this right:
> A unique constraint is satisfied if and only if no two rows in
> a table have the same non-null values in the unique columns.
> I think it means that nulls are to be left out of account in the
> comparison.
Hmm. What then of
a, b, c
a, NULL, c
If I "ignore the null" then these two rows are equal as well.
Still, you're right that it's a little more ambiguously worded than
I thought. Can anyone check how other DBMSs handle this?
regards, tom lane
in oracle 8.1.6:
create table x(col1 char(1), col2 char(1), col3 char(1));
Table created.
SQL> desc x
Name Null? Type
------------------------------- -------- ----
COL1 CHAR(1)
COL2 CHAR(1)
COL3 CHAR(1)
create unique index xidx on x(col1,col2,col3);
Index created.
insert into x values ('1','2','3');
1 row created.
insert into x values ('1','','3');
1 row created.
insert into x values ('1','','3');
insert into x values ('1','','3')
*
ERROR at line 1:
ORA-00001: unique constraint (DBA_USER.XIDX) violated
i'd say that it finds 1,null,3 equal the 2nd time around...
mikeo
At 10:25 AM 7/20/00 -0400, Tom Lane wrote:
>"Oliver Elphick" <olly@lfix.co.uk> writes:
>> Tom Lane wrote:
>>> Two nulls are never considered equal, therefore the unique constraint
>>> does not trigger.
>
>> I think you are not interpreting this right:
>
>> A unique constraint is satisfied if and only if no two rows in
>> a table have the same non-null values in the unique columns.
>
>> I think it means that nulls are to be left out of account in the
>> comparison.
>
>Hmm. What then of
>
> a, b, c
> a, NULL, c
>
>If I "ignore the null" then these two rows are equal as well.
>
>Still, you're right that it's a little more ambiguously worded than
>I thought. Can anyone check how other DBMSs handle this?
>
> regards, tom lane
>
At 10:56 20/07/00 -0400, mikeo wrote:
>in oracle 8.1.6:
>
>i'd say that it finds 1,null,3 equal the 2nd time around...
>
Ditto for Dec/Rdb.
Ditto for SQL/Server 7.
They both also object to null values in the primary key, FWIW: SQL/Server
won't let a field be part of a PK unless it is set 'Not Null', and Dec/Rdb
causes a constraint failure when a NULL value is inserted.
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
mikeo <mikeo@spectrumtelecorp.com> writes:
> insert into x values ('1','','3');
> insert into x values ('1','','3')
> *
> ERROR at line 1:
> ORA-00001: unique constraint (DBA_USER.XIDX) violated
> i'd say that it finds 1,null,3 equal the 2nd time around...
Surely '' does not mean NULL, especially not in a CHAR() column.
regards, tom lane
At 01:10 21/07/00 +1000, Philip Warner wrote:
>At 10:56 20/07/00 -0400, mikeo wrote:
>>in oracle 8.1.6:
>>
>>i'd say that it finds 1,null,3 equal the 2nd time around...
>>
>
> Ditto for Dec/Rdb.
> Ditto for SQL/Server 7.
>
The tests on Dec/Rdb and SQL/Server were done with true NULL values.
----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/
in oracle it does:
SQL> insert into x values ('1',null,'3');
insert into x values ('1',null,'3')
*
ERROR at line 1:
ORA-00001: unique constraint (DBA_USER.XIDX) violated
At 11:25 AM 7/20/00 -0400, Tom Lane wrote:
>mikeo <mikeo@spectrumtelecorp.com> writes:
>> insert into x values ('1','','3');
>
>> insert into x values ('1','','3')
>> *
>> ERROR at line 1:
>> ORA-00001: unique constraint (DBA_USER.XIDX) violated
>
>> i'd say that it finds 1,null,3 equal the 2nd time around...
>
>Surely '' does not mean NULL, especially not in a CHAR() column.
>
> regards, tom lane
>