[thelist] mysql: unique constraing and null default
rudy
r937 at interlog.com
Sat Mar 1 07:52:01 CST 2003
> 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
More information about the thelist
mailing list