1

I want an index on a non-primary key column, but this column may have nulls in it. I find on a normal unique index, it (rightly so) only allows one null.

Is that possible?

and what is the syntax for this?

Diskdrive
  • 18,107
  • 27
  • 101
  • 167
  • @Mark: the SQL standard says nothing about indexes - only about constraints. Classically, I believe DB2 first introduced the 'UNIQUE with at most one NULL' interpretation, and most other DBMS followed suit. It would be handy to be able to do '`CREATE UNIQUE UNLESS NULL INDEX idxname ON table(col1);`'. Sadly, I know of no system where you can do that. – Jonathan Leffler May 28 '10 at 05:45
  • @Jonathan: multiple NULLs in a UNIQUE column is allowed in Oracle (10g), no matter if the unique-ness is defined by a constraint or by an index - just tested this to verify. – Bob Jarvis - Слава Україні May 28 '10 at 11:55
  • @Bob - thanks; that is interesting to know (and is why SO is useful). – Jonathan Leffler May 28 '10 at 13:36

1 Answers1

4
CREATE INDEX idxname ON table(col1);

See MSDN

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
  • 1
    yep, i probably should've looked up how the syntax to create an index worked before i posted that question! – Diskdrive May 30 '10 at 23:47