Using NULL values in unique keys

[Alex]

Hello, all!

I have the following table in Oracle:

SQL> create table mytable( name varchar2(10) not null, ref number(9,0), constraint uk unique( name, ref ) );
Table created.

If I try to add two rows to the table with the same 'name' and ref=NULL, the unique constraint is violated (which is exactly what I want):

SQL> insert into mytable(name) values('Alex');
1 row created.
SQL> insert into mytable(name) values('Alex');
insert into mytable(name) values('Alex')
*
ERROR at line 1:
ORA-00001: unique constraint (MYDB.UK) violated

MySQL and Postgres behave differently in that they don't consider the NULL values equal (I read somewhere that it's what the standard said). I can, however, do a little trick in postgres to get what I want:

mydb=> create unique index mytable_unique_idx on mytable( name, (coalesce(ref, -1)) );
CREATE INDEX
mydb=> insert into mytable(name) values('Alex');
INSERT 409755 1
mydb=> insert into mytable(name) values('Alex');
ERROR: duplicate key violates unique constraint "mytable_unique_idx"

My question is how do I do it in MySQL. I tried using coalesce but got a syntax error.

Thanks,
Alex.