Professional Web Applications Themes

unique constraints with null values - Microsoft SQL / MS SQL Server

I read in SQL Server Books Online: "UNIQUE constraints can be defined on columns that allow null values, whereas PRIMARY KEY constraints can be defined only on columns that do not allow null values." (Creating and Maintaining Databases:UNIQUE Constraints) When I try to create a unique constaint on a column which contains null values, I get - Unable to create index 'IX_studentlist'. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 19. Most significant primary key is '<NULL>'. [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors. [Microsoft][ODBC ...

  1. #1

    Default unique constraints with null values

    I read in SQL Server Books Online:

    "UNIQUE constraints can be defined on columns that allow null values,
    whereas PRIMARY KEY constraints can be defined only on columns that do not
    allow null values."
    (Creating and Maintaining Databases:UNIQUE Constraints)

    When I try to create a unique constaint on a column which contains null
    values, I get
    - Unable to create index 'IX_studentlist'.
    ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE
    INDEX terminated because a duplicate key was found for index ID 19.
    Most significant primary key is '<NULL>'.
    [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
    constraint. See previous errors.
    [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
    terminated.

    I did this from Enterprise Manager, table design, properties, indexes/keys,
    new, create unique constraint
    What am I missing?

    I am using SQL Server 2000

    Thanks!
    Bill



    belgie Guest

  2. #2

    Default Re: unique constraints with null values

    Bill,

    What you read is correct but UNIQUE constraint would fail if there are more
    than one NULL value on the column (thus its no longer unique).

    --
    Dinesh.
    SQL Server FAQ at
    http://www.tkdinesh.com

    "belgie" <com> wrote in message
    news:phx.gbl... 
    UNIQUE 
    19. 
    indexes/keys, 


    Dinesh.T.K Guest

  3. #3

    Default unique constraints with null values

    Bill:

    For the purposes of the unique constraint testing, NULL =
    NULL. In other words, you can have only one row with a
    NULL if you have a UNIQUE CONSTRAINT on it.

    HTH
    Vern
     
    null values, 
    columns that do not 
    contains null 
    Server]CREATE UNIQUE 
    found for index ID 19. 
    not create 
    statement has been 
    properties, indexes/keys, 
    Vern Guest

  4. #4

    Default Re: unique constraints with null values

    A column with a unique constraint may contain only 1 row with a Null value
    for that column.

    HTH

    "belgie" <com> wrote in message
    news:phx.gbl... 
    UNIQUE 
    19. 
    indexes/keys, 


    Amy Guest

Similar Threads

  1. insert null in to unique identifier
    By stewart_smith1 in forum Coldfusion Database Access
    Replies: 3
    Last Post: November 2nd, 03:26 AM
  2. Replies: 3
    Last Post: March 22nd, 12:41 PM
  3. can I show only unique values in a dataGrid?
    By Mark.P. in forum Macromedia Flash Data Integration
    Replies: 0
    Last Post: September 26th, 04:00 PM
  4. Adding UNIQUE constraint on NULL column
    By Dave Smith in forum PostgreSQL / PGSQL
    Replies: 6
    Last Post: January 13th, 09:39 PM
  5. Replies: 6
    Last Post: October 21st, 09:31 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139