db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John English <john.fore...@gmail.com>
Subject Re: Unique constraint, or something like it
Date Tue, 13 Mar 2012 09:29:36 GMT
On 13/03/2012 09:30, Knut Anders Hatlen wrote:
> John English<john.foreign@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/
------------------------------------------------------------------------

Mime
View raw message