On 13/03/2012 09:30, Knut Anders Hatlen wrote:
John English<john.fore...@gmail.com> writes:
But that won't actually prevent me entering duplicate values with a null
section value, will it?
I think it should.
ij> CREATE TABLE foo (section INTEGER, title VARCHAR(100));
0 rows inserted/updated/deleted
ij> CREATE UNIQUE INDEX FOO_3 ON foo(section, title);
0 rows inserted/updated/deleted
ij> INSERT INTO foo values (null, 'abc'), (null, 'abc');
ERROR 23505: The statement was aborted because it would have caused a
duplicate key value in a unique or primary key constraint or unique
index identified by 'FOO_3' defined on 'FOO'.
This asymmetry between unique constraints and unique indexes is a bit
confusing. The reason for the asymmetry is that CREATE UNIQUE INDEX was
supported a long time before support for the unique constraint on
nullable columns was added, and its semantics were preserved for
backwards compatibility. Also, CREATE UNIQUE INDEX isn't specified by
the SQL standard, so it doesn't need to follow SQL's definition of
uniqueness, whereas the unique constraint is clearly specified in the
standard.
Ah, OK! Many thanks, this is exactly what I was looking for!
------------------------------------------------------------------------
John English | My old University of Brighton home page is still here:
| http://www.cem.brighton.ac.uk/staff/je/
------------------------------------------------------------------------
