Null values in indexes


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Null values in indexes

  1. #1
    Pep Gómez Guest

    Null values in indexes

    I have a problem I don't know if it can be resolved...
    I have a table where one of the fields, of datetime type, may accept Null
    values. The problem is that I would like to create the index UNIQUE, but
    SQL Server treats Null values as if they were "real" values, so I can't have
    2 rows with the date field to Null.
    Is this normal? I mean, a Null value should have no effect on the index (or
    at least that's what I would like :-)
    If there is no solution, how can I do the same but in a different way? What
    I don't like is having to "invent" a Null date, like #1/1/1800# means
    Null... but I think that's the only solution, right?

    Thank you in advance for any solution and/or comment

    Pep
    pep@impulso.net




  2. #2
    David Satz Guest

    Re: Null values in indexes

    the answer unfortunately is to get rid of the UNIQUE constraint and write
    insert and update triggers to perform the check for you.
    --
    HTH,
    David Satz
    Principal Web Engineer
    Hyperion Solutions
    { SQL Server 2000 SP2/6.5 SP5a } { Cold Fusion 5 SP1 } { VSS }
    (Please reply to group only - emails answered rarely)
    -----------------------------------------------------------------
    "Pep Gómez" <pep@impulso.net> wrote in message news:3d1c4650@10.1.10.29...
    > I have a problem I don't know if it can be resolved...
    > I have a table where one of the fields, of datetime type, may accept Null
    > values. The problem is that I would like to create the index UNIQUE, but
    > SQL Server treats Null values as if they were "real" values, so I can't

    have
    > 2 rows with the date field to Null.
    > Is this normal? I mean, a Null value should have no effect on the index

    (or
    > at least that's what I would like :-)
    > If there is no solution, how can I do the same but in a different way?

    What
    > I don't like is having to "invent" a Null date, like #1/1/1800# means
    > Null... but I think that's the only solution, right?
    >
    > Thank you in advance for any solution and/or comment
    >
    > Pep
    > pep@impulso.net
    >
    >
    >




Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center