[thelist] mysql: unique constraing and null default

[rudy]

> What should I do? (this sql biz is no fun)

take up a career with no exposure to sql?

;o)


the answer in this case is easy -- go ahead and use a unique constraint

which, according to the standard, will allow multiple nulls

see http://tinyurl.com/6nu1


as always, when in doubt, test

i'm happy to report that it does work in mysql --


    create table
      testuniquemultnulls
     ( id smallint  auto_increment
     , col1  varchar(3)  not null
     , col2  varchar(3)  null
     , primary key (id)
     , unique (col2)
     );

    insert into testuniquemultnulls
      ( col1, col2 )
    values
      ( 'foo' , 'aaa' )
    , ( 'foo' , 'bbb' )
    , ( 'foo' , 'ccc' )
    , ( 'foo' , null )
    , ( 'foo' , 'ddd' ) ;

    select * from testuniquemultnulls ;

        id  col1 col2
         1  foo  aaa
         2  foo  bbb
         3  foo  ccc
         4  foo  NULL
         5  foo  ddd

    insert into testuniquemultnulls
      ( col1, col2 )
    values
      ( 'bar' ,  null ) ;

    select * from testuniquemultnulls ;

        id  col1 col2
         1  foo  aaa
         2  foo  bbb
         3  foo  ccc
         4  foo  NULL
         5  foo  ddd
         6  bar  NULL

    insert into testuniquemultnulls
      ( col1, col2 )
    values
      ( 'fap' , 'ccc ' ) ;

    MySQL said:
    Duplicate entry 'ccc' for key 2