Thread
-
Re: unique constraint - bug?
Oliver Elphick <olly@lfix.co.uk> — 2000-07-20T07:53:30Z
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 -
Re: unique constraint - bug?
Tom Lane <tgl@sss.pgh.pa.us> — 2000-07-20T14:25:07Z
"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
-
Re: unique constraint - bug?
mikeo <mikeo@spectrumtelecorp.com> — 2000-07-20T14:56:49Z
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 > -
Re: unique constraint - bug?
Philip Warner <pjw@rhyme.com.au> — 2000-07-20T15:10:54Z
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 |/ -
Re: unique constraint - bug?
Tom Lane <tgl@sss.pgh.pa.us> — 2000-07-20T15:25:10Z
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 -
Re: unique constraint - bug?
Philip Warner <pjw@rhyme.com.au> — 2000-07-20T15:35:29Z
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 |/ -
Re: unique constraint - bug?
mikeo <mikeo@spectrumtelecorp.com> — 2000-07-20T15:48:36Z
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 >